# SQL and PL/SQL
SQL and PL/SQL

This section provides reference pages to Oracle and PostgreSQL functions, statements, and other commands.

**Topics**
+ [

# Single-row and aggregate functions
](chap-oracle-aurora-pg.sql.aggregate.md)
+ [

# CREATE TABLE AS SELECT statement
](chap-oracle-aurora-pg.sql.ctas.md)
+ [

# Common Table Expressions
](chap-oracle-aurora-pg.sql.cte.md)
+ [

# Oracle identity columns and PostgreSQL SERIAL type
](chap-oracle-aurora-pg.sql.identity.md)
+ [

# INSERT FROM SELECT statement
](chap-oracle-aurora-pg.sql.ifs.md)
+ [

# Multi-Version Concurrency Control
](chap-oracle-aurora-pg.sql.mvcc.md)
+ [

# MERGE statement
](chap-oracle-aurora-pg.sql.merge.md)
+ [

# Oracle OLAP functions and PostgreSQL window functions
](chap-oracle-aurora-pg.sql.olap.md)
+ [

# Oracle and PostgreSQL sequences
](chap-oracle-aurora-pg.sql.sequences.md)
+ [

# Oracle transaction model and PostgreSQL transactions
](chap-oracle-aurora-pg.sql.transactions.md)
+ [

# Oracle anonymous block and PostgreSQL DO
](chap-oracle-aurora-pg.sql.block.md)
+ [

# Oracle and PostgreSQL cursors
](chap-oracle-aurora-pg.sql.cursors.md)
+ [

# Oracle DBMS\$1OUTPUT and PostgreSQL RAISE
](chap-oracle-aurora-pg.sql.raise.md)
+ [

# Oracle DBMS\$1RANDOM and PostgreSQL RANDOM function
](chap-oracle-aurora-pg.sql.random.md)
+ [

# Oracle DBMS\$1SQL package and PostgreSQL dynamic execution
](chap-oracle-aurora-pg.sql.dynamic.md)
+ [

# Oracle EXECUTE IMMEDIATE and PostgreSQL EXECUTE and PREPARE
](chap-oracle-aurora-pg.sql.immediate.md)
+ [

# Oracle procedures and functions and PostgreSQL stored procedures
](chap-oracle-aurora-pg.sql.stored.md)
+ [

# Oracle and PostgreSQL user-defined functions
](chap-oracle-aurora-pg.sql.udfs.md)
+ [

# Oracle UTL\$1FILE package
](chap-oracle-aurora-pg.sql.utl.md)
+ [

# Oracle UTL\$1MAIL or UTL\$1SMTP and PostgreSQL Scheduled Lambda with Amazon SES
](chap-oracle-aurora-pg.sql.mail.md)

# Single-row and aggregate functions


Single-row and aggregate functions are essential SQL constructs that perform operations on individual rows or groups of rows, respectively. The following sections compare Oracle and PostgreSQL single-row and aggregate functions.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |  N/A  |  Not all functions are supported by PostgreSQL and may require to create manually.  | 

## Oracle usage


Oracle provides two main categories of built-in SQL functions based on the number of rows used as input and generated as output.
+ Single-row functions (also known as scalar functions) return a single result for each row of the queried table or view. You can use them with a `SELECT` statement in the `WHERE` clause, the `START WITH` clause, the `CONNECT BY` clause, and the `HAVING` clause. The single-row functions are divided into groups according to data types such as `NUMERIC` functions, `CHAR` functions, and `DATETIME` functions.
+ Aggregative Functions (also known as Group functions) are used to summarize a group of values into a single result. Examples include `AVG`, `MIN`, `MAX`, `SUM`, `COUNT`, `LISTAGG`, `FIRST`, and `LAST`.

See the following section for a comparison of Oracle and PostgreSQL single-row functions.

Oracle 19 adds ability to eliminate duplicate items in `LISTAGG` function results with new `DISTINCT` keyword.

Oracle 19 introduces several new bitmap SQL aggregate functions (`BITMAP_BUCKET_NUMBER`, `BITMAP_BIT_POSITION` and `BITMAP_CONSTRUCT_AGG`) that help to speed up `COUNT DISTINCT` operations.

For more information, see [Single-Row Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Single-Row-Functions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C) and [Aggregate Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Aggregate-Functions.html#GUID-62BE676BAF18-4E63-BD14-25206FEA0848) in *Oracle documentation*.

## PostgreSQL usage


PostgreSQL provides an extensive list of single-row and aggregation functions. Some are similar to their Oracle counterparts (by name and functionality, or under a different name but with similar functionality). Other functions can have identical names to their Oracle counterparts, but exhibit different functionality. In the following tables, the Equivalent column indicates functional equivalency.

 **Numeric functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `ABS`   |  Absolute value of n: `abs (-11.3) → 11.3`.  |   `ABS(n)`   |  Absolute value of n: `abs (-11.3) → 11.3`.  |  Yes  | 
|   `CEIL`   |  Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) → -24`.  |   `CEIL` / `CEILING`   |  Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) → -24`.  |  Yes  | 
|   `FLOOR`   |  Returns the largest integer equal to or less than n: `floor (-43.7) → -44`.  |   `FLOOR`   |  Returns the largest integer equal to or less than n: `floor (-43.7) → -44`.  |  Yes  | 
|   `MOD`   |  Remainder of n2 divided by n1: `mod(10,3) → 1`.  |   `MOD`   |  Remainder of n2 divided by n1: `mod(10,3) → 1`.  |  Yes  | 
|   `ROUND`   |  Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) → 3.5`.  |   `ROUND`   |  Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) → 3.5`.  |  Yes  | 
|   `TRUNC (Number)`   |  Returns n1 truncated to n2 decimal places: `trunc(13.5) → 13`.  |   `TRUNC (Number)`   |  Returns n1 truncated to n2 decimal places: `trunc(13.5) → 13`.  |  Yes  | 

 **Character functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CONCAT`   |  Returns char1 concatenated with char2: `concat('a', 1) → a1`.  |   `CONCAT`   |  Concatenate the text representations of all the arguments: `concat('a', 1) → a1`.  |  Partly  | 
|   `LOWER` / `UPPER`   |  Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |   `LOWER` / `UPPER`   |  Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.  |  Yes  | 
|   `LPAD` / `RPAD`   |  Returns expr1, left or right padded to length n characters with the sequence of characters in expr2: `LPAD('Log-1',10,'-') → -----Log-1`.  |   `LPAD` / `RPAD`   |  Returns expr1, left or right padded to length n characters with the sequence of characters in expr2: `LPAD('Log-1',10,'-') → -----Log-1`.  |  Yes  | 
|   `REGEXP_REPLACE`   |  Search a string for a regular expression pattern: `regexp_replace('John', '[hn].', '1') → Jo1`.  |   `REGEXP_REPLACE`   |  Replace substring(s) matching a POSIX regular expression: `regexp_replace('John', '[hn].', '1') → Jo1`.  |  Yes  | 
|   `REGEXP_SUBSTR`   |  Extends the functionality of the SUBSTR function by searching a string for a regular expression pattern: `REGEXP_SUBSTR('http://www.aws.-com/products','http://(+\.?){3,4}/?') → http://www.aws.com/`.  |   `REGEXP_MATCHES OR SUBSTRING`   |  Return all captured substrings resulting from matching a POSIX regular expression against the string: `REGEXP_MATCHES ('http://www.aws.com/products', '(http://+./)') → {http://www.aws.com/} OR SUBSTRING ('http://www.aws.-com/products', '(http://+./)') → http://www.aws.-com/`.  |  No  | 
|   `REPLACE`   |  Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |   `REPLACE`   |  Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.  |  Yes  | 
|   `LTRIM` / `RTRIM`   |  Removes from the left or right end of char all of the characters that appear in set: `ltrim ('zzzyaws', 'xyz') → aws`.  |   `LTRIM` / `RTRIM`   |  Remove the longest string containing only characters from characters (a space by default) from the start of string: `ltrim('zzzyaws', 'xyz') → aws`.  |  Yes  | 
|   `SUBSTR`   |  Return a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.  |   `SUBSTRING`   |  Extract substring: `substring ( 'John Smith', 6 ,1) → S `.  |  No  | 
|   `TRIM`   |  Trim leading or trailing characters (or both) from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.  |   `TRIM`   |  Remove the longest string containing only characters from characters (a space by default) from the start, end, or both ends: `trim (both from 'yxJohnxx', 'xyz') → John`.  |  Partly  | 
|   `ASCII`   |  Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |   `ASCII`   |  Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.  |  Yes  | 
|   `INSTR`   |  Search string for substring  |  N/A  |  Oracle `INSTR` function can be simulated using PostgreSQL built-in function.  |  No  | 
|   `LENGTH`   |  Return the length of char: `length ('John S.') → 7`.  |   `LENGTH`   |  Return the length of char: `length ('John S.') → 7`.  |  Yes  | 
|   `REGEXP_COUNT`   |  Returns the number of times, a pattern occurs in a source string.  |  N/A  |  You can use the `REGEXP_COUNT` function with Amazon Redshift if necessary.  |  No  | 
|   `REGEXP_INSTR`   |  Search a string position for a regular expression pattern.  |  N/A  |  You can use the `REGEXP_INSTR` function with Amazon Redshift if necessary.  |  No  | 

 **Datetime functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `ADD_MONTHS`   |  Returns the date plus integer months: `add_months( sysdate,1)`   |  N/A  |  PostgreSQL can implement the same functionality using the `<date>+ interval month` statement: `now () + interval '1 month'`.  |  No  | 
|   `CURRENT_DATE`   |  Returns the current date in the session time zone: `select current_date from dual → 2017-01-01 13:01:01`.  |   `CURRENT_DATE`   |  PostgreSQL CURRENT\$1DATE will return date with no time, use the `now()` or the `current_timestamp` function to achieve the same results: `select current_timestamp → 2017-01-01 13:01:01`.  |  Partly  | 
|   `CURRENT_TIMESTAMP`   |  Returns the current date and time in the session time zone: `select current_timestamp from dual; → 2017-01-01 13:01:01`.  |   `CURRENT_TIMESTAMP`   |  Returns the current date and time in the session time zone: `select current_timestamp; → 2017-01-01 13:01:01`.  |  Yes  | 
|   `EXTRACT (date part)`   |  Returns the value of a specified datetime field from a datetime or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |   `EXTRACT (date part)`   |  Returns the value of a specified datetime field from a datetime or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.  |  Yes  | 
|   `LAST_DAY`   |  Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.  |  N/A  |  You can use the `LAST_DAY` function with Amazon Redshift if necessary or you can create a workaround with PostgreSQL built-in functions.  |  No  | 
|   `BETWEEN`   |  Returns the number of months between dates date1 and date2: `MONTHS_BETWEEN ( sysdate, sysdate-100) → 3.25`.  |  N/A  |  As an alternative solution create a function from PostgreSQL built-in functions to achieve the same functionality. Example for a possible solution without decimal values: `DATE_PART ('month', now()) - DATE_PART('month', now()- interval'100 days') → 3`.  |  No  | 
|   `SYSDATE`   |  Returns the current date and time set for the operating system on which the database server resides: `select sysdate from dual; → 2017-01-01 13:01:01`.  |   `now()`   |  Current date and time including fractional seconds and time zone: `select now (); → 2017-01-01 13:01:01.123456+00`.  |  No  | 
|   `SYSTIMESTAMP`   |  Returns the system date, including fractional seconds and time zone: `select systimestamp from dual; → 2017-01-01 13:01:01.123456 PM+00:00`.  |   `NOW()`   |  Current date and time including fractional seconds and time zone: `select now (); → 2017-01-0113:01:01.123456+00`.  |  No  | 
|   `LOCALTIMESTAMP`   |  Returns the current date and time in the session time zone in a value of data type TIMESTAMP: `select localtimestamp from dual; → 01-JAN-17 10.01.10.123456 PM`.  |   `LOCALTIMESTAMP`   |  Returns the current date and time in the session time zone in a value of data type TIMESTAMP: `select localtimestamp; → 01-JAN-17 10.01.10.123456 PM`.  |  Yes  | 
|   `TO_CHAR(datetime)`   |  Converts a datetime or timestamp to data type to a value of VARCHAR2 data type in the format specified by the date format: `to_char(sys-date, 'DD-MON-YYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`.  |   `TO_CHAR(datetime)`   |  Convert time stamp to string: `TO_CHAR(now(), 'DD-MONYYYY HH24:MI:SS'); → 01-JAN-2017 01:01:01`.  |  Yes  | 
|   `TRUNC (date)`   |  Returns a date with the time portion of the day truncated to the unit specified by the format model: `trunc(systimestamp); → 2017-01-01 00:00:00`.  |   `DATE_TRUNC`   |  Truncate to specified precision: `date_trunc('day', now()); → 2017-01-01 00:00:00`.  |  No  | 

 **Encoding and decoding functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `DECODE`   |  Compares expression to each search value one by one using the functionality of an `IF-THEN-ELSE` statement.  |   `DECODE`   |  PostgreSQL Decode function acts differently from Oracle, PostgreSQL decode binary data from textual representation in string and doesn’t have the functionality of an `IF-THEN-ELSE` statement.  |  No  | 
|   `DUMP`   |  Returns a `VARCHAR2` value containing the data type code, length in bytes, and internal representation of expression.  |  N/A  |  N/A  |  No  | 
|   `ORA_HASH`   |  Computes a hash value for a given expression.  |  N/A  |  N/A  |  No  | 

 **Null functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CASE`   |  The `CASE` statement chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |   `CASE`   |  The PostgreSQL `CASE` expression is a generic conditional expression, similar to if/else statements in other programming languages: `CASE WHEN condition THEN result [WHEN …​] [ELSE result] END`.  |  Yes  | 
|   `COALESCE`   |  Returns the first non-null expr in the expression list: `coalesce (null, 'a', 'b') → a`.  |   `COALESCE`   |  Returns the first of its arguments that isn’t null: `coalesce (null, 'a', 'b') → a`.  |  Yes  | 
|   `NULLIF`   |  Compares expr1 and expr2. If they are equal, the function returns null. If they aren’t equal, the function returns expr1: `NULLIF('a', 'b') → a`.  |   `NULLIF`   |  Returns a null value if value1 equals value2 otherwise it returns value1: `NULLIF ('a', 'b') → a`.  |  Yes  | 
|   `NVL`   |  Replace null (returned as a blank) with a string in the results of a query: `NVL (null, 'a') → a`.  |   `COALESCE`   |  Returns the first of its arguments that isn’t null: `coalesce (null, 'a') → a`.  |  No  | 
|   `NVL2`   |  Determine the value returned by a query based on whether a specified expression is null or not null.  |  N/A  |  Can use the `CASE` statement instead.  |  No  | 

 **Environment and identifier functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `SYS_GUID`   |  Generates and returns a globally unique identifier (RAW value) made up of 16 bytes: `select sys_guid() from dual → 5A280ABA8C76201EE0530-100007FF691`.  |  UUID\$1GENERATE\$1V1()  |  Generates a version 1 UUID: `select uuid_generate_v1() → 90791a6-a359-11e7-a61c-12803bf1597a`.  |  No  | 
|   `UID`   |  Returns an integer that uniquely identifies the session user (the user who logged on): `select uid from dual → 84`   |  N/A  |  Consider using the PostgreSQL current\$1user function along with other PostgreSQL built-in function to generate a UID.  |  No  | 
|   `USER`   |  Returns the name of the session user: `select user from dual`.  |   `USER` / `SESSION_USER` / `CURRENT_USER` / `CURRENT_SCHEMA()`   |  User name or schema of current run context: `select user;` or `select current_schema();`   |  No  | 
|   `USERENV`   |  Returns information about the current session using parameters: `SELECT USERENV ('LANGUAGE') "Language" FROM DUAL`   |  N/A  |  For a list of all system functions, see the [PostgreSQL documentation](https://www.postgresql.org/docs/13/functions-info.html).  |  No  | 

 **Conversion functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `CAST`   |  Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as int) + 1 → 11`.  |   `CAST`   |  Converting one data type into another: `cast ( '10' as int) + 1 → 11`.  |  Yes  | 
|   `CONVERT`   |  Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual`   |  N/A  |  N/A  |  No  | 
|   `TO_CHAR (string / numeric)`   |  Converts `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB` data to the database character set: `select to_char ('01234') from dual → 01234`.  |   `TO_CHAR`   |  Converts the first argument to the second argument: `select to_char (01234, '00000') → 01234`.  |  No  | 
|   `TO_DATE`   |  Converts char of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `DATE` data type: `to_date('01Jan2017','DDMonYYYY') → 01-JAN-17`.  |   `TO_DATE`   |  Convert string to date: `to_date('01Jan2017', 'DDMonYYYY') → 2017-01-01`.  |  Partly  | 
|   `TO_NUMBER`   |  Converts expr to a value of `NUMBER` data type: `to_number('01234') → 1234 or to_number('01234', '99999') → 1234`.  |   `TO_NUMBER`   |  Convert string to numeric: `to_number('01234', '99999') → 1234`.  |  Partly  | 

 **Aggregate functions** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `AVG`   |  Returns average value of expression: `select avg(salary) from employees`.  |   `AVG`   |  Average (arithmetic mean) of all input values: `select avg(salary) from employees`.  |  Yes  | 
|   `COUNT`   |  Returns the number of rows returned by the query: `select count(*) from employees`.  |   `COUNT`   |  The number of input rows: `select count(*) from employees`.  |  Yes  | 
|   `LISTAGG`   |  Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select listagg(firstname,' ,') within group (order by customerid) from customer`.  |   `STRING_AGG`   |  Input values concatenated into a string, separated by delimiter: `select string_agg(firstname, ' ,') from customer order by 1;`.  |  No  | 
|   `MAX`   |  Returns the maximum value of expression: `select max(salary) from employees`.  |   `MAX`   |  Returns maximum value of expression: `select max(salary) from employees`.  |  Yes  | 
|   `MIN`   |  Returns the minimum value of expression: `select min(salary) from employees`.  |   `MIN`   |  Returns minimum value of expression: `select min(salary) from employees`.  |  Yes  | 
|   `SUM`   |  Returns the sum of values of expression: `select sum(salary) from employees`.  |   `SUM`   |  Returns the sum of values of expression: `select sum(salary) from employees`.  |  Yes  | 

 **Top-N query Oracle 12c** 


| Oracle function | Function definition | PostgreSQL function | Function definition | Equivalent | 
| --- | --- | --- | --- | --- | 
|   `FETCH`   |  Retrieves rows of data from the result set of a multi-row query: `select * from customer fetch first 10 rows only`.  |   `FETCH` or `LIMIT`   |  Retrieve just a portion of the rows that are generated by the rest of the query: `select * from customer fetch first 10 rows only`.  |  Yes  | 

 `REGEXP_MATCH` is a new pattern matching function that was introduced in PostgreSQL 10.

```
SELECT REGEXP_MATCH('foobarbequebaz','bar.*que');
regexp_match
-------------
{barbeque}
```

For more information, see [Functions and Operators](https://www.postgresql.org/docs/13/functions.html), [Mathematical Functions and Operators](https://www.postgresql.org/docs/13/functions-math.html), [String Functions and Operators](https://www.postgresql.org/docs/13/functions-string.html), and [uuid-ossp Functions](https://www.postgresql.org/docs/13/uuid-ossp.html) in the *PostgreSQL documentation*.

# CREATE TABLE AS SELECT statement


With AWS DMS, you can create a new table in a target database by selecting data from one or more tables in a source database using the Oracle and PostgreSQL `CREATE TABLE AS SELECT` statement. This statement defines a new table by querying data from existing tables, providing a way to replicate table structures and data from a source to a target database.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png)   |   ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png)   |  N/A  |  N/A  | 

## Oracle usage


The Create Table As Select (CTAS) statement creates a new table based on an existing table. It copies the table DDL definitions (column names and column datatypes) and data to a new table. The new table is populated from the columns specified in the `SELECT` statement, or all columns if you use `SELECT * FROM`. You can filter specific data using the `WHERE` and `AND` statements. Additionally, you can create a new table having a different structure using joins, `GROUP BY`, and `ORDER BY`.

 **Examples** 

Create a table based on an existing table and include data from all columns.

```
CREATE TABLE EMPS
AS
SELECT * FROM EMPLOYEES;
```

Create a table based on an existing table with select columns.

```
CREATE TABLE EMPS
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES
ORDER BY 3 DESC
```

For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL conforms to the ANSI/SQL standard for CTAS functionality and is compatible with an Oracle CTAS statement. For PostgreSQL, the following CTAS standard elements are optional:
+ The standard requires parentheses around the `SELECT` statement; PostgreSQL doesn’t.
+ The standard requires the `WITH [ NO ] DATA` clause; PostgreSQL doesn’t.

 **PostgreSQL CTAS synopsis** 

```
CREATE
[ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
[ (column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) |
WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
AS query
[ WITH [ NO ] DATA ]
```

 **Examples** 

PostgreSQL CTAS.

```
pg_CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES;
pg_CREATE TABLE EMPS AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES ORDER BY 3 DESC;
```

PostgreSQL CTAS with no data.

```
pg_CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES WITH NO DATA;
```

For more information, see [CREATE TABLES](https://www.postgresql.org/docs/13/sql-createtableas.html) in the *PostgreSQL documentation*.

# Common Table Expressions


The following sections provide details on defining and leveraging Common Table Expressions (CTEs) within AWS DMS to streamline database operations and enhance query performance.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png)   |   ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png)   |  N/A  |  N/A  | 

## Oracle usage


Common Table Expressions (CTE) provide a way to implement the logic of sequential code or to reuse code. You can define a named sub query and then use it multiple times in different parts of a query statement.

A CTE is implemented using a `WITH` clause, which is part of the ANSI SQL-99 standard and has existed in Oracle since version 9.2. CTE usage is similar to an inline view or a temporary table. Its main purpose is to reduce query statement repetition and make complex queries simpler to read and understand.

 **Syntax** 

```
WITH <subquery name> AS (<subquery code>)[...]
SELECT <Select list> FROM <subquery name>;
```

 **Examples** 

Create a sub query of the employee count for each department and then use the result set of the CTE in a query.

```
WITH DEPT_COUNT
(DEPARTMENT_ID, DEPT_COUNT) AS
(SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)
SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME,
D.DEPT_COUNT AS EMP_DEPT_COUNT
FROM EMPLOYEES E JOIN DEPT_COUNT D
USING (DEPARTMENT_ID)
ORDER BY 2;
```

## PostgreSQL usage


PostgreSQL conforms to the ANSI SQL-99 standard. Implementing CTEs in PostgreSQL is done in a similar way to Oracle as long as you aren’t using native Oracle elements (for example, connect by).

 **Examples** 

A PostgreSQL CTE.

```
WITH DEPT_COUNT
(DEPARTMENT_ID, DEPT_COUNT) AS (
SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)
SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME, D.DEPT_COUNT AS EMP_
DEPT_COUNT
FROM EMPLOYEES E JOIN DEPT_COUNT D USING (DEPARTMENT_ID) ORDER BY 2;
```

PostgreSQL provides an additional feature when using CTE as a recursive modifier. The following example uses a recursive `WITH` clause to access its own result set.

```
WITH RECURSIVE t(n) AS (
VALUES (0)
UNION ALL
SELECT n+1 FROM t WHERE n < 5)
SELECT * FROM t;
WITH RECURSIVE t(n) AS (
VALUES (0)
UNION ALL
SELECT n+1 FROM t WHERE n < 5)
SELECT * FROM t;
n
--
0
1
2
3
4
5
```

For more information, see [WITH Queries (Common Table Expressions)](https://www.postgresql.org/docs/13/queries-with.html) in the *PostgreSQL documentation*.

# Oracle identity columns and PostgreSQL SERIAL type


With AWS DMS, you can migrate databases that utilize identity columns or auto-incrementing primary keys across different database engines. Oracle databases use identity columns to automatically generate unique sequential values for primary keys, while PostgreSQL databases employ the `SERIAL` pseudo-type for the same purpose.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)   |   [Sequences](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.sequences)   |  Since PostgreSQL 10, there are no differences besides the data types.  | 

## Oracle usage


Oracle 12c introduced support for automatic generation of values to populate columns in database tables. The `IDENTITY` type generates a sequence and associates it with a table column without the need to manually create a separate sequence object. The `IDENTITY` type relies (internally) on sequences, which can also be manually configured.

 **Examples** 

Create a table with an Oracle 12c identity column.

```
CREATE TABLE IDENTITY_TST (COL1 NUMBER GENERATED BY DEFAULT AS IDENTITY(START WITH 100
INCREMENT BY 10), COL2 VARCHAR2(30));
```

Insert data into the table. The identity column automatically generates values for COL1.

```
INSERT INTO IDENTITY_TST(COL2) VALUES('A');
INSERT INTO IDENTITY_TST(COL1, COL2) VALUES(DEFAULT, 'B');
INSERT INTO IDENTITY_TST(col1, col2) VALUES(NULL, 'C');
SELECT * FROM IDENTITY_TST;

COL1   COL2
---    ---
100    A
110    B
```

For more information, see [CREATE TABLE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL enables you to create a sequence that is similar to the `IDENTITY` property supported by Oracle 12c identity column feature. When creating a new table using the `SERIAL` pseudo-type, a sequence is created.

Additional types from the same family are `SMALLSERIAL` and `BIGSERIAL`.

By assigning a `SERIAL` type to a column as part of table creation, PostgreSQL creates a sequence using default configuration and adds the `NOT NULL` constraint to the column. The new sequence can be altered and configured as a regular sequence.

Since PostgreSQL 10, there is a new option called identity columns which is similar to `SERIAL` data type but more SQL standard compliant. The identity columns are highly compatibility compare to Oracle identity columns.

 **Examples** 

Using the PostgreSQL SERIAL pseudo-type (with a Sequence that is created implicitly).

```
CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

ALTER SEQUENCE SERIAL_SEQ_TST_COL1_SEQ RESTART WITH 100 INCREMENT BY 10;
INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
INSERT INTO SERIAL_SEQ_TST(COL1, COL2) VALUES(DEFAULT, 'B');
SELECT * FROM SERIAL_SEQ_TST;
```

To create a table with identity columns, use the following (this command is Oracle compatible).

```
CREATE TABLE emps (
emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL);

INSERT INTO emps (emp_name) VALUES ('Robert');
INSERT INTO emps (emp_id, emp_name) VALUES (DEFAULT, 'Brian');
```

**Note**  
It is important to know that in PostgreSQL (both SERIAL and IDENTITY), you can insert any value that you want that won’t violate the primary key constraint. If you do that and after that, you will use the identity column sequence value again, the following error might raise. SQL Error [23505]: ERROR: duplicate key value violates unique constraint "emps\$1iden\$1pkey" Detail: Key (emp\$1id)=(2) already exists.

For more information, see [CREATE SEQUENCE](https://www.postgresql.org/docs/13/sql-createsequence.html), [Sequence Manipulation Functions](https://www.postgresql.org/docs/13/functions-sequence.html), [Numeric Types](https://www.postgresql.org/docs/13/datatype-numeric.html), and [CREATE TABLE](https://www.postgresql.org/docs/13/sql-createtable.html) in the *PostgreSQL documentation*.

# INSERT FROM SELECT statement


The following sections provide details on running the `INSERT FROM SELECT` statement, including syntax examples and best practices for efficient data transfer.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |  N/A  |  N/A  | 

## Oracle usage


You can insert multiple records into a table from another table using the `INSERT FROM SELECT` statement, which is a derivative of the basic `INSERT` statement. The column ordering and data types must match between the target and the source tables.

 **Examples** 

Simple `INSERT FROM SELECT` (explicit).

```
INSERT INTO EMPS (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID) SELECT EMPLOYEE_ID,
FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000;
```

Simple `INSERT FROM SELECT` (implicit).

```
INSERT INTO EMPS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000;
```

This example produces the same result as the preceding example but uses a subquery in the `DML_table_expression_clause`.

```
INSERT INTO
(SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMPS)
VALUES (120, 'Kenny', 10000, 90);
```

Log errors with the Oracle error\$1logging\$1clause.

```
ALTER TABLE EMPS ADD CONSTRAINT PK_EMP_ID PRIMARY KEY(employee_id);
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('EMPS', 'ERRLOG');
INSERT INTO EMPS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000
LOG ERRORS INTO errlog ('Cannot Perform Insert') REJECT LIMIT 100;
0 rows inserted
```

When inserting an existing `EMPLOYEE ID` into the `EMPS` table, the insert doesn’t fail because the invalid records are redirected to the `ERRLOG` table.

For more information, see [INSERT](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/INSERT.html#GUID-903F8043-0254-4EE9-ACC1-CB8AC0AF3423) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL `INSERT FROM SELECT` syntax is mostly compatible with the Oracle syntax, except for a few Oracle-only features such as the conditional\$1insert\$1clause (`ALL|FIRST|ELSE`). Also, PostgreSQL doesn’t support the Oracle error\$1logging\$1clause. As an alternative, PostgreSQL provides the ON CONFLICT clause to capture errors, perform corrective measures, or log errors.

 **Syntax** 

```
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
[ OVERRIDING { SYSTEM | USER} VALUE ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ]
[, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
and conflict_action is one of:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ]( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
```

**Note**  
 `OVERRIDING` is a new option since PostgreSQL 10 and relevant for identity columns. `SYSTEM VALUE` is only for identity column where `GENERATE ALWAYS` exists; if it’s not there and it was specified, then PostgreSQL just ignores it.

 **Examples** 

Simple `INSERT FROM SELECT` (explicit).

```
INSERT INTO EMPS (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000;
```

Simple `INSERT FROM SELECT` (implicit).

```
INSERT INTO EMPS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000;
```

The following example isn’t compatible with PostgreSQL.

```
INSERT INTO
(SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMPS)
VALUES (120, 'Kenny', 10000, 90);
```

The following example demonstrates using the `ON DUPLICATE KEY UPDATE` clause to update specific columns when a `UNIQUE` violation occurs.

```
ALTER TABLE EMPS ADD CONSTRAINT PK_EMP_ID PRIMARY KEY(employee_id);
INSERT INTO EMPS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 10000
ON CONFLICT on constraint PK_EMP_ID DO NOTHING;
INSERT 0
```

For more information, see [INSERT](https://www.postgresql.org/docs/13/sql-insert.html) in the *PostgreSQL documentation*.

# Multi-Version Concurrency Control


With AWS DMS, you can implement Multi-Version Concurrency Control (MVCC) to manage concurrent access to data during database migrations. MVCC is a concurrency control method that maintains multiple versions of database objects, allowing readers and writers to access the data simultaneously without blocking or causing conflicts.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png)   |  N/A  |  N/A  |  N/A  | 

## Oracle usage


Two primary lock types exist in Oracle: exclusive locks and share locks, which implement the following high-level locking semantics:
+ Writers never block readers.
+ Readers never block writers.
+ Oracle never escalates locks from row to page and table level, which reduces potential deadlocks.
+ In Oracle, users can issue explicit locks on specific tables using the `LOCK TABLE` statement.

Lock types can be divided into four categories: DML locks, DDL locks, Explicit (Manual) data locking, and System locks. The following sections describe each category.

 **DML locks** 

DML locks preserve the integrity of data accessed concurrently by multiple users. DML statements acquire locks automatically both on row and table levels.
+  **Row Locks (TX)**. Obtained on a single row of a table by one the following statements: `INSERT`, `UPDATE`, `DELETE`, `MERGE`, and `SELECT …​ FOR UPDATE`. If a transaction obtains a row lock, a table lock is also acquired to prevent DDL modifications to the table that might cause conflicts. The lock exists until the transaction ends with a `COMMIT` or `ROLLBACK`.
+  **Table Locks ™**. When performing one of the following DML operations: `INSERT`, `UPDATE`, `DELETE`, `MERGE`, and `SELECT …​ FOR UPDATE`, a transaction automatically acquires a table lock to prevent DDL modifications to the table that might cause conflicts if the transaction did not issue a `COMMIT` or `ROLLBACK`.

The following table provides additional information regarding row and table locks.


| Statement | Row locks | Table lock mode | RS | RX | S | SRX | X | 
| --- | --- | --- | --- | --- | --- | --- | --- | 
|  SELECT …​ FROM table…​  |  —  |  none  |  Y  |  Y  |  Y  |  Y  |  Y  | 
|  INSERT INTO table…​  |  Yes  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  UPDATE table …​  |  Yes  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  MERGE INTO table …​  |  Yes  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  DELETE FROM table…​  |  Yes  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  SELECT …​ FROM table FOR UPDATE OF…​  |  Yes  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  LOCK TABLE table IN…​  |  —  |  |  |  |  |  |  | 
|  ROW SHARE MODE  |  |  SS  |  Y  |  Y  |  Y  |  Y  |  N  | 
|  ROW EXCLUSIVE MODE  |  |  SX  |  Y  |  Y  |  N  |  N  |  N  | 
|  SHARE MODE  |  |  S  |  Y  |  N  |  Y  |  N  |  N  | 
|  SHARE ROW EXCLUSIVE MODE  |  |  SSX  |  Y  |  N  |  N  |  N  |  N  | 
|  EXCLUSIVE MODE  |  |  X  |  N  |  N  |  N  |  N  |  N  | 

 **DDL locks** 

The main purpose of a DDL lock is to protect the definition of a schema object while it is modified by an ongoing DDL operation such as `ALTER TABLE EMPLOYEES ADD <COLUMN>`.

 **Explicit (Manual) data locking** 

Users have the ability to explicitly create locks to achieve transaction-level read consistency for when an application requires transactional exclusive access to a resource without waiting for other transactions to complete. Explicit data locking can be performed at the transaction level or the session level:
+ Transaction level
  +  `SET TRANSACTION ISOLATION LEVEL` 
  +  `LOCK TABLE` 
  +  `SELECT … FOR UPDATE` 
+ Session level
  +  `ALTER SESSION SET ISOLATION LEVEL` 

 **System locks** 

System locks include latches, mutexes, and internal locks.

 **Examples** 

Explicitly lock data using the `LOCK TABLE` command.

```
-- Session 1
LOCK TABLE EMPLOYEES IN EXCLUSIVE MODE;
-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;
-- Session 2 waits for session 1 to COMMIT or ROLLBACK
```

Explicitly lock data using the `SELECT… FOR UPDATE` command. Oracle obtains exclusive row-level locks on all the rows identified by the `SELECT FOR UPDATE` statement.

```
-- Session 1
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;
-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;
-- Session 2 waits for session 1 to COMMIT or ROLLBACK
```

For more information, see [Automatic Locks in DDL Operations](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-Locks-in-DDL-Operations.html#GUID-84D392A3-94EC-444D-950F-7829DBCD43EE), [Automatic Locks in DML Operations](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-Locks-in-DML-Operations.html#GUID-3D57596F-8B73-4C80-8F4D-79A12F781EFD), and [Automatic and Manual Locking Mechanisms During SQL Operations](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Automatic-and-Manual-Locking-Mechanisms-During-SQL-Operations.html#GUID-0304C4AA-BD28-4C2A-B7F5-267532FB9499) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL provides various lock modes to control concurrent access to data in tables. Data consistency is maintained using a Multi-Version Concurrency Control (MVCC) mechanism. Most PostgreSQL commands automatically acquire locks of appropriate modes to ensure that referenced tables aren’t dropped or modified in incompatible ways while the command runs.

The MVCC mechanism prevents viewing inconsistent data produced by concurrent transactions performing updates on the same rows. MVCC in PostgreSQL provides strong transaction isolation for each database session and minimizes lock-contention in multiuser environments.
+ Similar to Oracle, MVCC locks acquired for querying (reading) data don’t conflict with locks acquired for writing data. Reads will never block writes and writes never blocks reads.
+ Similar to Oracle, PostgreSQL doesn’t escalate locks to table-level, such as where an entire table is locked for writes when a certain threshold of row locks is exceeded.

### Implicit and explicit transactions (Auto-commit behavior)


Unlike Oracle, PostgreSQL uses auto-commit for transactions by default. However, there are two options to support explicit transactions, which are similar to the default behavior in Oracle (non-auto-commit).
+ Use the `START TRANSACTION` (or `BEGIN TRANSACTION`) statements and then `COMMIT` or `ROLLBACK`.
+ Set `AUTOCOMMIT` to `OFF` at the session level.

```
\set AUTOCOMMIT off
```

With explicit transactions:
+ Users can explicitly issue a lock similar to the `LOCK TABLE` statement in Oracle.
+  `SELECT… FOR UPDATE` is supported.

Similar to Oracle, PostgreSQL automatically acquires the necessary locks to control concurrent access to data. PostgreSQL implements the following types of locks.

 **Table-level locks** 


| Requested and current lock modes | ACCESSSHARE | ROWSHARE | ROWEXCLUSIVE | SHAREUPDATEEXCLUSIVE | SHARE | SHAREROWEXCLUSIVE | EXCLUSIVE | ACCESSEXCLUSIVE | 
| --- | --- | --- | --- | --- | --- | --- | --- | --- | 
|  ACCESSSHARE  |  |  |  |  |  |  |  |  X  | 
|  ROWSHARE  |  |  |  |  |  |  |  X  |  X  | 
|  ROWEXCLUSIVE  |  |  |  |  |  X  |  X  |  X  |  X  | 
|  SHAREUPDATEEXCLUSIVE  |  |  |  |  X  |  X  |  X  |  X  |  X  | 
|  SHARE  |  |  |  X  |  X  |  X  |  X  |  X  |  X  | 
|  SHAREROWEXCLUSIVE  |  |  |  X  |  X  |  X  |  X  |  X  |  X  | 
|  EXCLUSIVE  |  |  X  |  X  |  X  |  X  |  X  |  X  |  X  | 
|  ACCESSEXCLUSIVE  |  X  |  X  |  X  |  X  |  X  |  X  |  X  |  X  | 

 **Row-level locks** 


| Requested and current lock modes | FOR KEY SHARE | FOR SHARE | FOR NO KEY UPDATE | FOR UPDATE | 
| --- | --- | --- | --- | --- | 
|  FOR KEY SHARE  |  |  |  |  X  | 
|  FOR SHARE  |  |  |  X  |  X  | 
|  FOR NO KEY UPDATE  |  |  X  |  X  |  X  | 
|  FOR UPDATE  |  X  |  X  |  X  |  X  | 

 **Page-level locks** 

Shared or exclusive locks used to control read or write access to table pages in the shared buffer pool. They are released immediately after a row is fetched or updated.

 **Deadlocks** 

Occur when two or more transactions are waiting for one another to release each lock.

 **Transaction-level locking** 

PostgreSQL doesn’t support session isolation levels, although it can be controlled by transactions.
+  `SET TRANSACTION ISOLATION LEVEL` 
+  `LOCK TABLE` 
+  `SELECT … FOR UPDATE` 

 **PostgreSQL LOCK TABLE synopsis** 

```
LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]
where lockmode is one of:
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE
```

If `ONLY` and `*` are specified, the command stops with an error.

There is no `UNLOCK TABLE` command. Locks are always released at the end of a transaction (`COMMIT` / `ROLLBACK`).

You can use the `LOCK TABLE` command inside a transaction and it should appear after the `START TRANSACTION` statement.

 **Examples** 

Obtain an explicit lock on a table using the `LOCK TABLE` command.

```
-- Session 1
START TRANSACTION;
LOCK TABLE EMPLOYEES IN EXCLUSIVE MODE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK
```

Explicit lock by the `SELECT… FOR UPDATE` command. PostgreSQL obtains exclusive row-level locks on rows referenced by the `SELECT FOR UPDATE` statement. Must be ran inside a transaction.

```
-- Session 1
START TRANSACTION;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK
```

### PostgreSQL deadlocks


Deadlocks occur when two or more transactions acquired locks on each other’s process resources (table or row). PostgreSQL can detect Deadlocks automatically and resolve the event by aborting one of the transactions, allowing the other transaction to complete.

Simulating a deadlock:

```
Session 1 - step1:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
Session 2 - step2:
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
Session 2 step3:
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 11111;
Session 1 - step4:
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
```

Session 1 is waiting for Session 2 and Session 2 is waiting for Session 1 = deadlock.

Real-time monitoring of locks using catalog tables:
+  `pg_locks` 
+  `pg_stat_activity` 

Monitor locks using the following SQL query.

```
SELECT
block.pid AS block_pid,
block_stm.usename AS blocker_user,
block.mode AS block_mode,
block.locktype AS block_locktype,
block.relation::regclass AS block_table,
block_stm.query AS block_query,
block.GRANTED AS block_granted,
waiting.locktype AS waiting_locktype,
waiting_stm.usename AS waiting_user,
waiting.relation::regclass AS waiting_table,
waiting_stm.query AS waiting_query,
waiting.mode AS waiting_mode,
waiting.pid AS waiting_pid
from pg_catalog.pg_locks AS waiting JOIN
pg_catalog.pg_stat_activity AS waiting_stm
ON (waiting_stm.pid = waiting.pid)
join pg_catalog.pg_locks AS block
ON ((waiting."database" = block."database"
AND waiting.relation = block.relation)
OR waiting.transactionid = block.transactionid)
join pg_catalog.pg_stat_activity AS block_stm
ON (block_stm.pid = block.pid)
where NOT waiting.GRANTED
and waiting.pid <> block.pid;
```

Generate an explicit lock using the `SELECT… FOR UPDATE` statement.

```
-- Session 1
START TRANSACTION;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;

-- Session 2
UPDATE EMPLOYEES
SET SALARY=SALARY+1000
WHERE EMPLOYEE_ID=114;

-- Session 2 waits for session 1 to COMMIT or ROLLBACK
```

Run the SQL query from step \$11 monitoring locks while distinguishing between the “blocking” and “waiting” session.

```
-[ RECORD 1 ]-
block_pid        | 31743
blocker_user     | aurora_admin
block_mode       | ExclusiveLock
block_locktype   | transactionid
block_table      |
block_query      | SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=114 FOR UPDATE;
block_granted    | t
waiting_locktype | transactionid
waiting_user     | aurora_admin
waiting_table    |
waiting_query    | UPDATE EMPLOYEES
                 | SET SALARY=SALARY+1000
                 | WHERE EMPLOYEE_ID=114;
waiting_mode     | ShareLock
waiting_pid      | 31996
```

## Summary



| Description | Oracle | PostgreSQL | 
| --- | --- | --- | 
|  Dictionary tables to obtain information about locks  |  <pre>v$lock;<br />v$locked_object;<br />v$session_blockers;</pre>  |  <pre>pg_locks<br />pg_stat_activity</pre>  | 
|  Lock a table  |  <pre>BEGIN;<br />LOCK TABLE employees IN SHARE<br />ROW EXCLUSIVE MODE;</pre>  |  <pre>LOCK TABLE employees IN SHARE<br />ROW EXCLUSIVE MODE;</pre>  | 
|  Explicit locking  |  <pre>SELECT * FROM employees<br />WHERE employee_id=102 FOR UPDATE;</pre>  |  <pre>BEGIN;<br />SELECT * FROM employees WHERE<br />employee_id=102 FOR UPDATE;</pre>  | 
|  Explicit locking, options  |  <pre>SELECT…FOR UPDATE</pre>  |  <pre>SELECT … FOR…<br />KEY SHARE<br />SHARE<br />NO KEY UPDATE<br />UPDATE</pre>  | 

For more information, see [LOCK](https://www.postgresql.org/docs/13/sql-lock.html) and [Explicit Locking](https://www.postgresql.org/docs/13/explicit-locking.html) in the *PostgreSQL documentation*.

# MERGE statement


With AWS DMS, you can perform Oracle `MERGE` statements and the PostgreSQL equivalent to conditionally insert, update, or delete rows in a target table based on the results of a join with a source table.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |   [Merge](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.merge)   |  MERGE isn’t supported by PostgreSQL, workaround available.  | 

## Oracle usage


The `MERGE` statement provides a means to specify single SQL statements that conditionally perform `INSERT`, `UPDATE`, or `DELETE` operations on a target table—a task that would otherwise require multiple logical statements.

The `MERGE` statement selects record(s) from the source table and then, by specifying a logical structure, automatically performs multiple DML operations on the target table. Its main advantage is to help avoid the use of multiple inserts, updates or deletes. It is important to note that `MERGE` is a deterministic statement. That is, once a row has been processed by the MERGE statement, it can’t be processed again using the same `MERGE` statement. `MERGE` is also sometimes known as `UPSERT`.

 **Examples** 

Use `MERGE` to insert or update employees who are entitled to a bonus (by year).

```
CREATE TABLE EMP_BONUS(EMPLOYEE_ID NUMERIC,BONUS_YEAR VARCHAR2(4),
SALARY NUMERIC,BONUS NUMERIC, PRIMARY KEY (EMPLOYEE_ID, BONUS_YEAR));

MERGE INTO EMP_BONUS E1
USING (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES) E2 ON (E1.EMPLOYEE_ID = E2.EMPLOYEE_ID) WHEN MATCHED THEN
UPDATE SET E1.BONUS = E2.SALARY * 0.5
DELETE WHERE (E1.SALARY >= 10000)
WHEN NOT MATCHED THEN
INSERT (E1.EMPLOYEE_ID, E1.BONUS_YEAR, E1.SALARY , E1.BONUS)
VALUES (E2.EMPLOYEE_ID, EXTRACT(YEAR FROM SYSDATE), E2.SALARY,
E2.SALARY * 0.5)
WHERE (E2.SALARY < 10000);

SELECT * FROM EMP_BONUS;

EMPLOYEE_ID BONUS_YEAR SALARY BONUS
103         2017       9000   4500
104         2017       6000   3000
105         2017       4800   2400
106         2017       4800   2400
107         2017       4200   2100
109         2017       9000   4500
110         2017       8200   4100
111         2017       7700   3850
112         2017       7800   3900
113         2017       6900   3450
115         2017       3100   1550
```

For more information, see [MERGE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C0E-81A7-A22436DC968F) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL doesn’t support the use of the `MERGE` SQL command. As an alternative, consider using the `INSERT… ON CONFLICT` clause, which can handle cases where insert clauses might cause a conflict, and then redirect the operation as an update.

 **Examples** 

Using the `ON CONFLICT` clause to handle a similar scenario as shown for the Oracle `MERGE` command.

```
CREATE TABLE EMP_BONUS (
EMPLOYEE_ID NUMERIC,
BONUS_YEAR VARCHAR(4),
SALARY NUMERIC,
BONUS NUMERIC,
PRIMARY KEY (EMPLOYEE_ID, BONUS_YEAR));

INSERT INTO EMP_BONUS (EMPLOYEE_ID, BONUS_YEAR, SALARY)
SELECT EMPLOYEE_ID, EXTRACT(YEAR FROM NOW()), SALARY
FROM EMPLOYEES
WHERE SALARY < 10000
ON CONFLICT (EMPLOYEE_ID, BONUS_YEAR)
DO UPDATE SET BONUS = EMP_BONUS.SALARY * 0.5;

SELECT * FROM EMP_BONUS;

employee_id  bonus_year  salary   bonus
103          2017        9000.00  4500.000
104          2017        6000.00  3000.000
105          2017        4800.00  2400.000
106          2017        4800.00  2400.000
107          2017        4200.00  2100.000
109          2017        9000.00  4500.000
110          2017        8200.00  4100.000
111          2017        7700.00  3850.000
112          2017        7800.00  3900.000
113          2017        6900.00  3450.000
115          2017        3100.00  1550.000
116          2017        2900.00  1450.000
117          2017        2800.00  1400.000
118          2017        2600.00  1300.000
```

Running the same operation multiple times using the `ON CONFLICT` clause doesn’t generate an error because the existing records are redirected to the update clause.

For more information, see [INSERT](https://www.postgresql.org/docs/13/sql-insert.html) and [Unsupported Features](https://www.postgresql.org/docs/13/unsupported-features-sql-standard.htm) in the *PostgreSQL documentation*.

# Oracle OLAP functions and PostgreSQL window functions


The following sections outline the steps to configure and utilize Oracle OLAP functions and PostgreSQL window functions with AWS Database Migration Service.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   [OLAP Functions](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.olapfunctions)   |   `GREATEST` and `LEAST` functions might get different results in PostgreSQL. `CONNECT BY` isn’t supported by PostgreSQL, workaround available.  | 

## Oracle usage


Oracle OLAP functions extend the functionality of standard SQL analytic functions by providing capabilities to compute aggregate values based on a group of rows. You can apply the OLAP functions to logically partitioned sets of results within the scope of a single query expression. OLAP functions are usually used in combination with Business Intelligence reports and analytics. They can help boost query performance as an alternative to achieving the same result using more complex non-OLAP SQL code.

### Common Oracle OLAP Functions



| Function type | Related functions | 
| --- | --- | 
|  Aggregate  |   `average_rank`, `avg`, `count`, `dense_rank`, `max`, `min`, `rank`, `sum`   | 
|  Analytic  |   `average_rank`, `avg`, `count`, `dense_rank`, `lag`, `lag_variance`, `lead_variance_percent`, `max`, `min`, `rank`, `row_number`, `sum`, `percent_rank`, `cume_dist`, `ntile`, `first_value`, `last_value`   | 
|  Hierarchical  |   `hier_ancestor`, `hier_child_count`, `hier_depth`, `hier_level`, `hier_order`, `hier_parent`, `hier_top`   | 
|  Lag  |   `lag`, `lag_variance`, `lag_variance_percent`, `lead`, `lead_variance`, `lead_variance_percent`   | 
|  OLAP DML  |   `olap_dml_expression`   | 
|  Rank  |   `average_rank`, `dense_rank`, `rank`, `row_number`   | 

For more information, see [OLAP Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/OLAP-Functions.html#GUID-2AE523A7-630C-4907-B91B-89861C141EBD) and [Functions](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Functions.html#GUID-D079EFD3-C683-441F-977E-2C9503089982) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL refers to ANSI SQL analytical functions as “Window Functions”. They provide the same core functionality as SQL Analytical Functions and Oracle extended OLAP functions. Window functions in PostgreSQL operate on a logical “partition” or "window" of the result set and return a value for rows in that “window”.

From a database migration perspective, you should examine PostgreSQL Window Functions by type and compare them with the equivalent Oracle OLAP functions to verify compatibility of syntax and output.

**Note**  
Even if a PostgreSQL window function provides the same functionality of a specific Oracle OLAP function, the returned data type may be different and require application changes.

PostgreSQL provides support for two main types of window functions:
+ Aggregation functions.
+ Ranking functions.

### PostgreSQL window functions by type



| Function type | Related functions | 
| --- | --- | 
|  Aggregate  |   `avg`, `count`, `max`, `min`, `sum`, `string_agg`   | 
|  Ranking  |   `row_number`, `rank`, `dense_rank`, `percent_rank`, `cume_dist`, `ntile`, `lag`, `lead`, `first_value`, `last_value`, `nth_value`   | 

 **Examples** 

The Oracle `rank()` function and the PostgreSQL `rank()` function provide the same results.

Oracle:

```
SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank
80            Russell   14000  .4             1
80            Partners  13500  .3             2
80            Errazuriz 12000  .3             3
```

PostgreSQL:

```
hr=# SELECT department_id, last_name, salary, commission_pct,
    RANK() OVER (PARTITION BY department_id
    ORDER BY salary DESC, commission_pct) "Rank"
    FROM employees WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY    COMMISSION_PCT Rank
80            Russell   14000.00  0.40           1
80            Partners  13500.00  0.30           2
80            Errazuriz 12000.00  0.30           3
```

**Note**  
The returned formatting for certain numeric data types is different.

### Oracle CONNECT BY equivalent in PostgreSQL


PostgreSQL provides two workarounds as alternatives to Oracle hierarchical statements such as the `CONNECT BY` function:
+ Use PostgreSQL `generate_series` function.
+ Use PostgreSQL recursive views.

 **Example** 

PostgreSQL `generate_series` function.

```
SELECT "DATE"
  FROM generate_series(timestamp '2010-01-01',
                       timestamp '2017-01-01',
                       interval '1 day') s("DATE");

DATE
---------------------
2010-01-01 00:00:00
2010-01-02 00:00:00
2010-01-03 00:00:00
2010-01-04 00:00:00
2010-01-05 00:00:00
…
```

For more information, see [Window Functions](https://www.postgresql.org/docs/13/functions-window.html) and [Aggregate Functions](https://www.postgresql.org/docs/13/functions-aggregate.html) in the *PostgreSQL documentation*.

### Extended support for analytic queries and OLAP


For advanced analytic purposes and use cases, consider using Amazon Redshift as a purpose-built data warehouse cloud solution. You can run complex analytic queries against petabytes of structured data using sophisticated query optimization, columnar storage on high-performance local disks, and massive parallel query run. Most results are returned in seconds.

 Amazon Redshift is specifically designed for online analytic processing (OLAP) and business intelligence (BI) applications, which require complex queries against large datasets. Because it addresses very different requirements, the specialized data storage schema and query run engine that Amazon Redshift uses is completely different from the PostgreSQL implementation. For example, Amazon Redshift stores data in columns, also known as a columnar-store database.


| Function type | Related functions | 
| --- | --- | 
|  Aggregate  |   `AVG`, `COUNT`, `CUME_DIST`, `FIRST_VALUE`, `LAG`, `LAST_VALUE`, `LEAD`, `MAX`, `MEDIAN`, `MIN`, `NTH_VALUE`, `PERCENTILE_CONT`, `PERCENTILE_DISC`, `RATIO_TO_REPORT`, `STDDEV_POP`, `STDDEV_SAMP` (synonym for `STDDEV`), `SUM`, `VAR_POP`, `VAR_SAMP` (synonym for `VARIANCE`)  | 
|  Ranking  |   `DENSE_RANK`, `NTILE`, `PERCENT_RANK`, `RANK`, `ROW_NUMBER`   | 

For more information, see [Window functions](https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html) and [Overview example for window functions](https://docs.aws.amazon.com/redshift/latest/dg/c_Window_functions.html#r_Window_function_example) in the *Amazon documentation*.

## Summary



| Oracle OLAP function | Returned data type | PostgreSQL window function | Returned data type | Compatible syntax | 
| --- | --- | --- | --- | --- | 
|   `Count`   |   `Number`   |   `Count`   |   `bigint`   |  Yes  | 
|   `Max`   |  Number  |   `Max`   |   `numeric`, `string`, `date/time`, `network` or `enum` type  |  Yes  | 
|   `Min`   |   `Number`   |   `Min`   |   `numeric`, `string`, `date/time`, `network` or `enum` type  |  Yes  | 
|   `Avg`   |   `Number`   |   `Avg`   |   `numeric`, `double`, otherwise same datatype as the argument  |  Yes  | 
|   `Sum`   |   `Number`   |   `Sum`   |   `bigint`, otherwise same datatype as the argument  |  Yes  | 
|   `rank()`   |   `Number`   |   `rank()`   |   `bigint`   |  Yes  | 
|   `row_number()`   |   `Number`   |   `row_number()`   |   `bigint`   |  Yes  | 
|   `dense_rank()`   |   `Number`   |   `dense_rank()`   |   `bigint`   |  Yes  | 
|   `percent_rank()`   |   `Number`   |   `percent_rank()`   |   `double`   |  Yes  | 
|   `cume_dist()`   |   `Number`   |   `cume_dist()`   |   `double`   |  Yes  | 
|   `ntile()`   |   `Number`   |   `ntile()`   |   `integer`   |  Yes  | 
|   `lag()`   |  Same type as value  |   `lag()`   |  Same type as value  |  Yes  | 
|   `lead()`   |  Same type as value  |   `lead()`   |  Same type as value  |  Yes  | 
|   `first_value()`   |  Same type as value  |   `first_value()`   |  Same type as value  |  Yes  | 
|   `last_value()`   |  Same type as value  |   `last_value()`   |  Same type as value  |  Yes  | 

# Oracle and PostgreSQL sequences


With AWS DMS, you can manage database sequence objects across heterogeneous database platforms during migration. Sequences are unique identifiers that generate sequential numbers, often used as primary keys in tables.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   [Sequences](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.sequences)   |  Different syntax for a few options in PostgreSQL  | 

## Oracle usage


Sequences are database objects that serve as unique identity value generators, for example, automatically generating primary key values. Oracle treats sequences as independent objects. The same sequence can generate values for multiple tables.

Sequences can be configured with multiple parameters to control their value-generating behavior. For example, the `INCREMENT BY` sequence parameter defines the interval between each generated sequence value. If more than one database user is generating incremented values from the same sequence, each user may encounter gaps in the generated values that are visible to them.

Oracle 18c introduces scalable sequences: a special class of sequences that are optimized for multiple concurrent session usage.

This introduces the following new options when creating a new sequence:
+  `SCALE` — enable the sequence scalability feature.
  +  `EXTEND` — extend in additional 6 digits offset (as default) and the maximum number of digits in the sequence (maxvalue/minvalue).
  +  `NOEXTEND` (default when using the `SCALE` option) — sequence value will be padded to the max value.
+  `NOSCALE` — non-scalable sequence usage.

### Oracle sequence options


By default, the initial and increment values for a sequence are both 1, with no upper limit.
+  `INCREMENT BY`: Controls the sequence interval value of the increment or decrement (if a negative value is specified). If the INCREMENT BY parameter isn’t specified during sequence creation, the value is set to 1. The increment can’t be assigned a value of 0.
+  `START WITH`: Defines the initial value of a sequence. The default value is 1.
+  `MAXVALUE` \$1 `NOMAXVALUE`: Specifies the maximum limit for values generated by a sequence. It must be equal or greater than the START WITH parameter and must be greater in value than the `MINVALUE` parameter. The default for `NOMAXVALUE` is 1027 for an ascending sequence.
+  `MINVALUE` \$1 `NOMINVALUE`: Specifies the minimum limit for values generated by a sequence. Must be less than or equal to the `START WITH` parameter and must be less than the `MAXVALUE` parameter. The default for `NOMINVALUE` is -1026 for a descending sequence.
+  `CYCLE` \$1 `NOCYCLE`: Instructs a sequence to continue generating values despite reaching the maximum value or the minimum value. If the sequence reaches one of the defined ascending limits, it generates a new value according to the minimum value. If it reaches a descending limit, it generates a new value according to the maximum value. `NOCYCLE` is the default.
+  `CACHE` \$1 `NOCACHE`: Specifies the number of sequence values to keep cached in memory for improved performance. `CACHE` has a minimum value of 2. The `NOCACHE` parameter causes a sequence to not cache values in memory. Specifying neither `CACHE` nor `NOCACHE` will cache 20 values to memory. In the event of a database failure, all unused cached sequence values are lost and gaps in sequence values may occur.
+  `SCALE` \$1 `NOSCALE`: Enable the scalable sequences feature (described above).

 **Examples** 

Create a sequence.

```
CREATE SEQUENCE SEQ_EMP
START WITH 100
INCREMENT BY 1
MAXVALUE 99999999999
CACHE 20
NOCYCLE;
```

Drop a sequence.

```
DROP SEQUENCE SEQ_EMP;
```

View sequences created for the current schema or user.

```
SELECT * FROM USER_SEQUENCES;
```

Use a sequence as part of an `INSERT INTO` statement.

```
CREATE TABLE EMP_SEQ_TST (COL1 NUMBER PRIMARY KEY, COL2 VARCHAR2(30));
INSERT INTO EMP_SEQ_TST VALUES(SEQ_EMP.NEXTVAL, 'A');

COL1    COL2
100     A
```

Query the current value of a sequence.

```
SELECT SEQ_EMP.CURRVAL FROM DUAL;
```

Manually increment the value of a sequence according to the `INCREMENT BY` specification.

```
SELECT SEQ_EMP.NEXTVAL FROM DUAL;
```

Alter an existing sequence.

```
ALTER SEQUENCE SEQ_EMP MAXVALUE 1000000;
```

Create a scalable sequence.

```
CREATE SEQUENCE scale_seq
MINVALUE 1
MAXVALUE 9999999999
SCALE;

select scale_seq.nextval as scale_seq from dual;

NEXTVAL
1010320001
```

### Oracle 12c default values using sequences


Starting with Oracle 12c, you can assign a sequence to a table column with the `CREATE TABLE` statement and specify the `NEXTVAL` configuration of the sequence.

Generate `DEFAULT` values using sequences.

```
CREATE TABLE SEQ_TST ( COL1 NUMBER DEFAULT SEQ_1.NEXTVAL PRIMARY KEY, COL2 VARCHAR(30));
INSERT INTO SEQ_TST(COL2) VALUES('A');

SELECT * FROM SEQ_TST;

COL1   COL2
100    A
```

### Oracle 12c session sequences (`SESSION` or `GLOBAL`)


Beginning with Oracle 12c, sequences can be created as session-level or global-level. By adding the `SESSION` parameter to a `CREATE SEQUENCE` statement, the sequence is created as a session-level sequence. Optionally, you can use the `GLOBAL` keyword to create a global sequence to provide consistent results across sessions in the database. Global sequences are the default. Session sequences return a unique range of sequence numbers only within a session.

Create Oracle 12c `SESSION` and `GLOBAL` sequences.

```
CREATE SEQUENCE SESSION_SEQ SESSION;
CREATE SEQUENCE SESSION_SEQ GLOBAL;
```

### Oracle 12c identity columns


You can use sequences as an `IDENTITY` type, which automatically creates a sequence and associates it with the table column. The main difference is that there is no need to create a sequence manually; the `IDENTITY` type does that for you. An `IDENTITY` type is a sequence that can be configured.

Insert records using an Oracle 12c `IDENTITY` column (explicitly or implicitly).

```
INSERT INTO IDENTITY_TST(COL2) VALUES('A');
INSERT INTO IDENTITY_TST(COL1, COL2) VALUES(DEFAULT, 'B');
INSERT INTO IDENTITY_TST(col1, col2) VALUES(NULL, 'C');

SELECT * FROM IDENTITY_TST;

COL1    COL2
120     A
130     B
```

For more information, see [CREATE SEQUENCE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SEQUENCE.html#GUID-E9C78A8C-615A-4757-B2A8-5E6EFB130571) in the *Oracle documentation*.

## PostgreSQL usage


Sequences in PostgreSQL serve the same purpose as in Oracle; they generate numeric identifiers automatically. The PostgreSQL `CREATE SEQUENCE` command is mostly compatible with the Oracle `CREATE SEQUENCE` command. A sequence object is owned by the user that created it.

Oracle 18c introduces scalable sequences, this feature isn’t always needed but if it and the current PostgreSQL isn’t scalable enough, you can use other solutions and services to allow high-concurrency data read (to store only sequences data), this option will require more changes in the application layer.

### PostgreSQL sequence synopsis


```
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name
[ AS data_type ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
```

Most PostgreSQL `CREATE SEQUENCE` parameters are compatible with Oracle. Similar to Oracle 12c, in PostgreSQL you can create a sequence and use it directly as part of a `CREATE TABLE` statement.

### Sequence parameters

+  `TEMPORARY` or `TEMP` — PostgreSQL can create a temporary sequence within a session. Once the session ends, the sequence is automatically dropped.
+  `IF NOT EXISTS` — Creates a sequence even if a sequence with an identical name already exists. Replaces the existing sequence.
+  `AS` — A new option in PostgreSQL 10. It is for specifying the data type of the sequence. The available options are `smallint`, `integer`, and `bigint` (default). This also determines the maximum and minimum values.
+  `INCREMENT BY` — An optional parameter with a default value of 1. Positive values generate sequence values in ascending order. Negative values generate sequence values in descending sequence.
+  `START WITH` — The same as Oracle. This is an optional parameter having a default of 1. It uses the `MINVALUE` for ascending sequences and the MAXVALUE for descending sequences.
+  `MAXVALUE` \$1 `NO MAXVALUE` — Defaults are between 263 for ascending sequences and -1 for descending sequences.
+  `MINVALUE` \$1 `NO MINVALUE` — Defaults are between 1 for ascending sequences and -263 for descending sequences.
+  `CYCLE` \$1 `NO CYCLE` — If the sequence value reaches `MAXVALUE` or `MINVALUE`, the `CYCLE` parameter instructs the sequence to return to the initial value (`MINVALUE` or `MAXVALUE`). The default is `NO CYCLE`.
+ CACHE — Note that in PostgreSQL, the `NOCACHE` isn’t supported. By default, when not specifying the `CACHE` parameter, no sequence values will be pre-cached into memory, which is equivalent to the Oracle `NOCACHE` parameter. The minimum value is 1.
+  `OWNED BY` \$1 `OWNBY NON` — Specifies that the sequence object is to be associated with a specific column in a table, which isn’t supported by Oracle. When dropping this type of sequence, an error will be returned because of the sequence/table association.

 **Examples** 

Create a sequence.

```
CREATE SEQUENCE SEQ_1 START WITH 100
INCREMENT BY 1 MAXVALUE 99999999999 CACHE 20 NO CYCLE;
```

Identical to Oracle syntax, except for the whitespace in the `NO CYCLE` parameter.

Drop a sequence.

```
DROP SEQUENCE SEQ_1;
```

View sequences created in the current schema and sequence specifications.

```
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES;
OR
\ds
```

Use a PostgreSQL sequence as part of a `CREATE TABLE` and an `INSERT` statement.

```
CREATE TABLE SEQ_TST (COL1 NUMERIC DEFAULT NEXTVAL('SEQ_1') PRIMARY KEY, COL2 VARCHAR(30));

INSERT INTO SEQ_TST (COL2) VALUES('A');

SELECT * FROM SEQ_TST;
col1   col2
100    A
```

Use the `OWNED BY` parameter to associate the sequence with a table.

```
CREATE SEQUENCE SEQ_1 START WITH 100 INCREMENT BY 1 OWNED BY SEQ_TST.COL1;
```

Query the current value of a sequence.

```
SELECT CURRVAL('SEQ_1);
```

Manually increment a sequence value according to the `INCREMENT BY` value.

```
SELECT NEXTVAL('SEQ_1');
OR
SELECT SETVAL('SEQ_1', 200);
```

Alter an existing sequence.

```
ALTER SEQUENCE SEQ_1 MAXVALUE 1000000;
```

**Note**  
To use the `NEXTVAL` function, the `USAGE` and `UPDATE` permissions on the sequence are needed. To use `CURRVAL` and `LASTVAL` functions, the `USAGE` and `SELECT` permissions on the sequence are needed.

### Generating Sequence by SERIAL Type


PostgreSQL enables you to create a sequence that is similar to the `AUTO_INCREMENT` property supported by identity columns in Oracle 12c. When creating a new table, the sequence is created through the `SERIAL` data type. Other types from the same family are `SMALLSERIAL` and `BIGSERIAL`.

By assigning a `SERIAL` type to a column on table creation, PostgreSQL creates a sequence using the default configuration and adds a `NOT NULL` constraint to the column. The newly created sequence behaves like a regular sequence.

 **Examples** 

Using a SERIAL Sequence.

```
CREATE TABLE SERIAL_SEQ_TST(COL1 SERIAL PRIMARY KEY, COL2 VARCHAR(10));

INSERT INTO SERIAL_SEQ_TST(COL2) VALUES('A');
SELECT * FROM SERIAL_SEQ_TST;

col1   col2
1      A

\ds

Schema  Name                     Type      Owner
public  serial_seq_tst_col1_seq  sequence  pg_tst_db
```

## Summary



| Parameter or feature | Compatibility with PostgreSQL | Comments | 
| --- | --- | --- | 
|  Create sequence syntax  |  Full, with minor differences  |  See Exceptions  | 
|   `INCREMENT BY`   |  Full  |  | 
|   `START WITH`   |  Full  |  | 
|   `MAXVALUE` and `NOMAXVALUE`   |  Full  |  Use `NO MAXVALUE`   | 
|   `MINVALUE` and `NOMINVALUE`   |  Full  |  Use `NO MINVALUE`   | 
|   `CYCLE` and `NOCYCLE`   |  Full  |  Use `NO CYCLE`   | 
|   `CACHE` and `NOCACHE`   |  PostgreSQL doesn’t support the `NOCACHE` parameter but the default behavior is identical. The `CACHE` parameter is compatible with Oracle.  |  | 
|  Default values using sequences in Oracle 12c  |  Supported by PostgreSQL  |   `CREATE TABLE TBL( COL1 NUMERIC DEFAULT NEXTVAL ('SEQ_1')…`   | 
|  Session sequences (session or global) in Oracle 12c  |  Supported by PostgreSQL by using the `TEMPORARY` sequence parameter to Oracle `SESSION` sequence  |  | 
|  Oracle 12c identity columns  |  Supported by PostgreSQL by using the `SERIAL` data type as sequence  |  | 

For more information, see [CREATE SEQUENCE](https://www.postgresql.org/docs/13/static/sql-createsequence.html), [Sequence Manipulation Functions](https://www.postgresql.org/docs/13/static/functions-sequence.html), and [Numeric Types](https://www.postgresql.org/docs/13/static/datatype-numeric.html) in the *PostgreSQL documentation*.

# Oracle transaction model and PostgreSQL transactions


Transactions are logical units of work that allow multiple database operations to be executed as a single atomic unit. The Oracle transaction model and PostgreSQL transactions define how transactions are handled, including features like atomicity, consistency, isolation, and durability (ACID properties).


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   [Transaction Isolation](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.transaction)   |  PostgreSQL doesn’t support `SAVEPOINT`, `ROLLBACK TO SAVEPOINT` inside of functions  | 

## Oracle usage


Database transactions are a logical, atomic units of processing containing one or more SQL statements that may run concurrently alongside other transactions. The primary purpose of a transaction is to ensure the ACID model is enforced.
+  **Atomicity** — All statements in a transaction are processed as one logical unit, or none are processed. If a single part of a transaction fails, the entire transaction is aborted and no changes are persisted (all or nothing).
+  **Consistency** — All data integrity constraints are checked and all triggers are processed before a transaction is processed. If any of the constraints are violated, the entire transaction fails.
+  **Isolation** — One transaction isn’t affected by the behavior of other concurrentl transactions. The effect of a transaction isn’t visible to other transactions until the transaction is committed.
+  **Durability** — Once a transaction commits, its results will not be lost regardless of subsequent failures. After a transaction completes, changes made by committed transactions are permanent. The database ensures that committed transactions can’t be lost.

### Database transaction isolation levels


The ANSI/ISO SQL standard (SQL92) defines four levels of isolation. Each level provides a different approach for handling concurrent run of database transactions. Transaction isolation levels manage the visibility of changed data as seen by other running transactions. In addition, when accessing the same data with several concurrent transactions, the selected level of transaction isolation affects the way different transactions interact. For example, if a bank account is shared by two individuals, what will happen if both parties attempt to perform a transaction on the shared account at the same time? One checks the account balance while the other withdraws money. Oracle supports the following isolation levels:
+  **Read-uncommitted** — A currently processed transaction can see uncommitted data made by the other transaction. If a rollback is performed, all data is restored to its previous state.
+  **Read-committed** — A transaction only sees data changes that were committed. Uncommitted changes(“dirty reads”) aren’t possible.
+  **Repeatable read** — A transaction can view changes made by the other transaction only after both transactions issue a COMMIT or both are rolled-back.
+  **Serializable** — Any concurrent run of a set of serializable transactions is guaranteed to produce the same effect as running them sequentially in the same order.

Isolation levels affect the following database behavior.
+  **Dirty reads** — A transaction can read data that was written by another transaction, but isn’t yet committed.
+  **Non-repeatable (fuzzy) reads** — When reading the same data several times, a transaction can find that the data has been modified by another transaction that has just committed. The same query executed twice can return different values for the same rows.
+  **Phantom reads** — Similar to a non-repeatable read, but it is related to new data created by another transaction. The same query run twice can return a different numbers of records.


| Isolation level | Dirty reads | Non-repeatable reads | Phantom reads | 
| --- | --- | --- | --- | 
|  Read-uncommitted  |  Permitted  |  Permitted  |  Permitted  | 
|  Read-committed  |  Not permitted  |  Permitted  |  Permitted  | 
|  Repeatable read  |  Not permitted  |  Not permitted  |  Permitted  | 
|  Serializable  |  Not permitted  |  Not permitted  |  Not permitted  | 

### Oracle isolation levels


Oracle supports the read-committed and serializable isolation levels. It also provides a Read-Only isolation level which isn’t a part of the ANSI/ISO SQL standard (SQL92). Read-committed is the default.
+  **Read-committed (default)** — Each query that you run within a transaction only sees data that was committed before the query itself. The Oracle database nevers allow reading “dirty pages” and uncommitted data.
+  **Serializable** — Serializable transactions don’t experience non-repeatable reads or phantom reads because they are only able to “see” changes that were committed at the time the transaction began (in addition to the changes made by the transaction itself performing DML operations).
+  **Read-only** — The read-only isolation level doesn’t allow any DML operations during the transaction and only sees data committed at the time the transaction began.

### Oracle Multiversion Concurrency Controls


Oracle uses the Oracle Multiversion Concurrency Controls (MVCC) mechanism to provide automatic read consistency across the entire database and all sessions. Using MVCC, database sessions see data based on a single point in time ensuring only committed changes are viewable. Oracle relies on the System Change Number (SCN) of the current transaction to obtain a consistent view of the database. Therefore, all database queries only return data committed with respect to the SCN at the time of query run.

### Setting isolation levels


Isolation levels can be changed at the transaction and session levels.

 **Examples** 

Change the isolation level at the transaction-level.

```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION READ ONLY;
```

Change the isolation-level at a session-level.

```
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL = READ COMMITTED;
```

For more information, see [Transactions](https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/transactions.html#GUID-B97790CB-DF82-442D-B9D5-50CCE6BF9FBD) in the *Oracle documentation*.

## PostgreSQL usage


The same ANSI/ISO SQL (SQL92) isolation levels apply to PostgreSQL, with several similarities and some differences:


| Isolation level | Dirty reads | Non-repeatable reads | Phantom reads | 
| --- | --- | --- | --- | 
|  Read-uncommitted  |  Permitted but not implemented in PostgreSQL  |  Permitted  |  Permitted  | 
|  Read-committed  |  Not permitted  |  Permitted  |  Permitted  | 
|  Repeatable read  |  Not permitted  |  Not permitted  |  Permitted but not implemented in PostgreSQL  | 
|  Serializable  |  Not permitted  |  Not permitted  |  Not permitted  | 

PostgreSQL technically supports the use of any of the above four transaction isolation levels, but only three can practically be used. The read-uncommitted isolation level serves as read-committed.

The way the Repeatable-Read isolation-level is implemented doesn’t allow for phantom reads, which is similar to the serializable isolation level. The primary difference between repeatable read and serializable is that serializable guarantees that the result of concurrent transactions will be precisely the same as if they were run serially, which isn’t always true for repeatable reads.

Starting with PostgreSQL 12, you can add the `AND CHAIN` option to `COMMIT` or `ROLLBACK` commands to immediately start another transaction with the same parameters as preceding transaction.

### Isolation levels supported by PostgreSQL


PostgreSQL supports the read-committed, repeatable reads, and serializable isolation levels. Read-committed is the default isolation level (similar to the default isolation level in the Oracle database).
+  **Read-committed** — The default PostgreSQL transaction isolation level. Preventing sessions from “seeing” data from concurrent transactions until it is committed. Dirty reads aren’t permitted.
+  **Repeatable read** — Queries can only see rows committed before the first query or DML statement was run in the transaction.
+  **Serializable** — Provides the strictest transaction isolation level. The Serializable isolation level assures that the result of the concurrent transactions will be the same as if they were executed serially. This isn’t always the case for the Repeatable-Read isolation level.

### Multiversion Concurrency Control


PostgreSQL implements a similar Multiversion Concurrency Control (MVCC) mechanism when compared to Oracle. In PostgreSQL, the MVCC mechanism allows transactions to work with a consistent snapshot of data ignoring changes made by other transactions which have not yet committed or rolled back. Each transaction “sees” a snapshot of accessed data accurate to its run start time, regardless of what other transactions are doing concurrently.

### Setting isolation levels in Aurora PostgreSQL


You can configure isolation levels at several levels:
+ Session level.
+ Transaction level.
+ Instance level using Aurora Parameter Groups.

 **Examples** 

Configure the isolation level for a specific transaction.

```
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
```

Configure the isolation level for a specific session.

```
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
```

View the current isolation level.

```
SELECT CURRENT_SETTING('TRANSACTION_ISOLATION'); -- Session
SHOW DEFAULT_TRANSACTION_ISOLATION;              -- Instance
```

You can modify instance-level parameters for Aurora PostgreSQL by using parameter groups. For example, you can alter the `default_transaction_isolation` parameter using the AWS Console or the AWS CLI.

For more information, see [Modifying parameters in a DB parameter group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html#USER_WorkingWithParamGroups.Modifying) in the *Amazon RDS documentation*.

### PostgreSQL Transaction Synopsis


```
SET TRANSACTION transaction_mode [...]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [...]

where transaction_mode is one of:

ISOLATION LEVEL {
SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED
}
READ WRITE | READ ONLY [ NOT ] DEFERRABLE
```


| Database feature | Oracle | PostgreSQL | 
| --- | --- | --- | 
|  AutoCommit  |  Off  |  Depends. Autocommit is turned off by default, however, some client tools such as psql and more are setting this to ON by default. Check your client tool defaults or run the following command to check current configuration in psql: `\echo :AUTOCOMMIT`.  | 
|  MVCC  |  Yes  |  Yes  | 
|  Default Isolation Level  |  Read-committed  |  Read-committed  | 
|  Supported Isolation Levels  |  Serializable, Read-only  |  Repeatable Reads, Serializable, Read-only  | 
|  Configure Session Isolation Levels  |  Yes  |  Yes  | 
|  Configure Transaction Isolation Levels  |  Yes  |  Yes  | 
|  Nested Transaction Support  |  Yes  |  No. Consider using `SAVEPOINT` instead.  | 
|  Support for transaction `SAVEPOINT`s  |  Yes  |  Yes  | 

 **Read-committed isolation level.** 


| TX1 | TX2 | Comment | 
| --- | --- | --- | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  <pre>select employee_id, salary<br />from EMPLOYEES<br />where employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  Same results returned from both sessions  | 
|  <pre>begin;<br />UPDATE employees<br />SET salary=27000<br />WHERE employee_id=100;</pre>  |  <pre>begin;<br />set transaction isolation<br />level read committed;</pre>  |  TX1 starts a transaction; performs an update. TX2 starts a transaction with read-committed isolation level.  | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          27000.00</pre>  |  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  TX1 will “see” the modified results (27000.00) while TX2 “sees” the original data (24000.00).  | 
|  |  <pre>UPDATE employees<br />SET salary=29000<br />WHERE employee_id=100;</pre>  |  Waits because TX2 is blocked by TX1.  | 
|  <pre>Commit;</pre>  |  |  TX1 issues a commit, and the lock is released.  | 
|  |  <pre>Commit;</pre>  |  TX2 issues a commit.  | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          29000.00</pre>  |  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          29000.00</pre>  |  Both queries return the value - 29000.00.  | 

 **Serializable isolation level.** 


| TX1 | TX2 | Comment | 
| --- | --- | --- | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  <pre>select employee_id, salary<br />from EMPLOYEES<br />where employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  Same results returned from both sessions  | 
|  <pre>begin;<br />UPDATE employees<br />SET salary=27000<br />WHERE employee_id=100;</pre>  |  <pre>begin;<br />set transaction isolation<br />level serializable;</pre>  |  TX1 starts a transaction and performs an update. TX2 starts a transaction with serializable isolation level.  | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          27000.00</pre>  |  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          24000.00</pre>  |  TX1 will “see” the modified results (27000.00) while TX2 “sees” the original data (24000.00).  | 
|  |  <pre>UPDATE employees<br />SET salary=29000<br />WHERE employee_id=100;</pre>  |  Waits because TX2 is blocked by TX1.  | 
|  <pre>Commit;</pre>  |  |  TX1 issues a commit, and the lock is released.  | 
|  |  ERROR: could not serialize access due to concurrent update.  |  TX2 received an error message.  | 
|  |  <pre>Commit;<br />ROLLBACK</pre>  |  TX2 trying to issue a commit but receives a rollback message, the transaction failed due to the serializable isolation level.  | 
|  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          27000.00</pre>  |  <pre>SELECT employee_id, salary<br />FROM EMPLOYEES<br />WHERE employee_id=100;<br /><br />employee_id  salary<br />100          27000.00</pre>  |  Both queries will return the data updated according to TX1.  | 

For more information, see [Transactions](https://www.postgresql.org/docs/13/tutorial-transactions.html), [Transaction Isolation](https://www.postgresql.org/docs/13/transaction-iso.html), and [SET TRANSACTION](https://www.postgresql.org/docs/13/sql-set-transaction.html) in the *PostgreSQL documentation*.

# Oracle anonymous block and PostgreSQL DO


With AWS DMS, you can run PL/SQL anonymous blocks and PostgreSQL `DO` commands to perform custom database code operations during a database migration. An Oracle anonymous block is an unattached, unnamed PL/SQL code block that can contain SQL queries and PL/SQL statements. A PostgreSQL `DO` command runs an anonymous code block containing procedural language statements.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)   |   [Stored Procedures](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.procedures)   |  Different syntax may require code rewrite.  | 

## Oracle usage


Oracle PL/SQL is a procedural extension of SQL. The PL/SQL program structure divides the code into blocks distinguished by the following keywords: `DECLARE`, `BEGIN`, `EXCEPTION`, and `END`.

An unnamed PL/SQL code block (code not stored in the database as a procedure, function, or package) is known as an anonymous block. An anonymous block serves as the basic unit of Oracle PL/SQL and contains the following code sections:
+  **The declarative section** (optional) — Contains variables (names, data types, and initial values).
+  **The executable section** (mandatory) — Contains executable statements (each block structure must contain at least one executable PL/SQL statement).
+  **The exception-handling section** (optional) — Contains elements for handling exceptions or errors in the code.

 **Examples** 

Simple structure of an Oracle anonymous block.

```
SET SERVEROUTPUT ON;
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END;
/

hello world
PL/SQL procedure successfully completed.
```

Oracle PL/SQL Anonymous blocks can contain advanced code elements such as functions, cursors, dynamic SQL, and conditional logic. The following anonymous block uses a cursor, conditional logic, and exception-handling.

```
SET SERVEROUTPUT ON;
DECLARE
v_sal_chk        NUMBER;
v_emp_work_years NUMBER;
v_sql_cmd        VARCHAR2(2000);
BEGIN
FOR v IN (SELECT EMPLOYEE_ID, FIRST_NAME||' '||LAST_NAME AS
EMP_NAME, HIRE_DATE, SALARY FROM EMPLOYEES)
LOOP
v_emp_work_years:=EXTRACT(YEAR FROM SYSDATE) - EXTRACT (YEAR FROM v.hire_date);
IF v_emp_work_years>=10 and v.salary <= 6000 then
DBMS_OUTPUT.PUT_LINE('Consider a Bonus for: '||v.emp_name);
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('CODE ERR: '||sqlerrm);
END;
/
```

The preceding example calculates the number of years each employee has worked based on the `HIRE_DATE` column of the `EMPLOYEES` table. If the employee has worked for ten or more years and has a salary of \$16000 or less, the system prints the message “Consider a Bonus for: <employee name>”.

For more information, see [Overview of PL/SQL](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/overview.html#GUID-2FBCFBBE-6B42-4DB8-83F3-55B63B75B1EB) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL supports capabilities similar to Oracle anonymous blocks. In PostgreSQL, you can run PL/pgSQL code that isn’t stored in the database as an independent code segment using a PL/pgSQL `DO` statement.

PL/pgSQL is a PostgreSQL extension to the ANSI SQL and has many similar elements to Oracle PL/SQL. PostgreSQL `DO` uses a similar code structure to an Oracle anonymous block
+  **The declarative section** (optional).
+  **The executable section** (mandatory).
+  **The exception-handling section** (optional).

 **Examples** 

PostgreSQL DO simple structure.

```
SET CLIENT_MIN_MESSAGES = 'debug';
-- Equivalent To Oracle SET SERVEROUTPUT ON

DO $$
  BEGIN
    RAISE DEBUG USING MESSAGE := 'hello world';
  END $$;

DEBUG: hello world
DO
```

The PostgreSQL PL/pgSQL `DO` statement supports the use of advanced code elements such as functions, cursors, dynamic SQL, and conditional logic.

The following example is a more complex PL/pgSQL DO code structure converted from Oracle “employee bonus” PL/SQL anonymous block example presented in the previous section:

```
DO $$
  DECLARE
    v_sal_chk DOUBLE PRECISION;
    v_emp_work_years DOUBLE PRECISION;
    v_sql_cmd CHARACTER VARYING(2000);
    v RECORD;
  BEGIN
  FOR v IN
  SELECT employee_id, CONCAT_WS('', first_name, ' ', last_name) AS emp_name, hire_date, salary FROM employees
  LOOP
    v_emp_work_years := EXTRACT (YEAR FROM now()) - EXTRACT (YEAR FROM v.hire_date);
  IF v_emp_work_years >= 10 AND v.salary <= 6000 THEN
    RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Consider a Salary Raise for: ',v.emp_name);
  END IF;
END LOOP;
EXCEPTION
  WHEN others THEN
    RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'CODE ERR: ',SQLERRM);
  END $$;
```

For more information, see [DO](https://www.postgresql.org/docs/13/sql-do.html) in the *PostgreSQL documentation*.

# Oracle and PostgreSQL cursors


With AWS DMS, you can migrate data from Oracle and PostgreSQL databases that use cursors. Cursors are database objects that enable traversal over rows from a result set in a database. They facilitate processing individual rows or row segments from a SQL statement’s result set.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)   |   [Cursors](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.cursors)   |   `TYPE …​ IS REF CURSOR` isn’t supported by PostgreSQL. Minor differences in syntax may require some code rewrite. PostgreSQL doesn’t support `%ISOPEN`, `%BULK_EXCEPTIONS`, and `%BULK_ROWCOUNT`.  | 

## Oracle usage


PL/SQL cursors are pointers to data sets on which application logic can iterate. The data sets hold rows returned by SQL statements. You can refer to the active data set in named cursors from within a program.

There are two types of PL/SQL cursors:
+  **Implicit cursors** are session cursors constructed and managed by PL/SQL automatically without being created or defined by a user. PL/SQL opens an implicit cursor each time you run a `SELECT` or DML statement. Implicit cursors are also called SQL cursors.
+  **Explicit cursors** are session cursors created, constructed, and managed by a user. Cursors are declared and defined by naming it and associating it with a query. Unlike an implicit cursor, you can reference an explicit cursor using its name. An explicit cursor is called a named cursor.

 **Examples** 

The following examples demonstrate cursor usage:

1. Define an explicit PL/SQL cursor named `c1`.

1. The cursor runs an SQL statement to return rows from the database.

1. The PL/SQL loop reads data from the cursor, row by row, and stores the values into two variables: `v_lastname` and `v_jobid`.

1. The loop uses the `%NOTFOUND` attribute to terminate when the last row is read from the database.

```
DECLARE
  CURSOR c1 IS
    SELECT last_name, job_id FROM employees
    WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
    ORDER BY last_name;
    v_lastname employees.last_name%TYPE; -- variable to store last_name
    v_jobid employees.job_id%TYPE; -- variable to store job_id
  BEGIN
    OPEN c1;
    LOOP -- Fetches 2 columns into variables
      FETCH c1 INTO v_lastname, v_jobid;
      EXIT WHEN c1%NOTFOUND;
    END LOOP;
  CLOSE c1;
END;
```

1. Define an implicit PL/SQL cursor using a `FOR` Loop.

1. The cursor runs a query and stores values returned into a record.

1. A loop iterates over the cursor data set and prints the result.

```
BEGIN
FOR item IN
  (SELECT last_name, job_id FROM employees WHERE job_id LIKE '%MANAGER%'
    AND manager_id > 400 ORDER BY last_name) LOOP
    DBMS_OUTPUT.PUT_LINE('Name = ' || item.last_name || ', Job = ' || item.job_id);
  END LOOP;
END;
/
```

For more information, see [Explicit Cursor Declaration and Definition](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/explicit-cursor-declaration-and-definition.html#GUID-38C5DBA3-9DEC-4AF2-9B5E-7B721D11A77C) and [Implicit Cursor Attribute](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/implicit-cursor-attribute.html#GUID-5A938EE7-E8D2-468C-B60F-81898F110BE1) in the *Oracle documentation*.

## PostgreSQL usage


Similar to Oracle PL/SQL cursors, PostgreSQL has PL/pgSQL cursors that enable you to iterate business logic on rows read from the database. They can encapsulate the query and read the query results a few rows at a time. All access to cursors in PL/pgSQL is performed through cursor variables, which are always of the refcursor data type.

Create a PL/pgSQL cursor by declaring it as a variable of type refcursor.

 **Examples of DECLARE a cursor** 

Declare a cursor in PL/pgSQL to be used with any query.

```
DECLARE c1 refcursor;
```

The variable c1 is unbound since it isn’t bound to any particular query.

Declare a cursor in PL/pgSQL with a bound query.

```
DECLARE c2 CURSOR FOR SELECT * FROM employees;
```

In the following example, you can replace `FOR` with `IS` for Oracle compatibility. Declare a cursor in PL/pgSQL to be used with any query.

```
DECLARE c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;
```
+ The id variable is replaced by an integer parameter value when the cursor is opened.
+ When declaring a cursor with `SCROLL` specified, the cursor can scroll backwards.
+ If `NO SCROLL` is specified, backward fetches are rejected.

Declare a backward-scrolling compatible cursor using the `SCROLL` option.

```
DECLARE c3 SCROLL CURSOR FOR SELECT id, name FROM employees;
```
+  `SCROLL` specifies that rows can be retrieved backwards. `NO SCROLL` specifies that rows can’t be retrieved backwards.
+ Depending upon the complexity of the run plan for the query, `SCROLL` might create performance issues.
+ Backward fetches aren’t allowed when the query includes `FOR UPDATE` or `FOR SHARE`.

 **Examples of OPEN a cursor** 

Open a cursor variable that was declared as Unbound and specify the query to run.

```
OPEN c1 FOR SELECT * FROM employees WHERE id = emp_id;
```

Open a cursor variable that was declared as Unbound and specify the query to run as a string expression. This approach provides greater flexibility.

```
OPEN c1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;
```

Parameter values can be inserted into the dynamic command using `format()` and `USING`. For example, the table name is inserted into the query using `format()`. The comparison value for col1 is inserted using a `USING` parameter.

Open a cursor that was bound to a query when the cursor was declared and that was declared to take arguments.

```
DO $$
DECLARE
  c3 CURSOR (var1 integer) FOR SELECT * FROM employees where id = var1;
BEGIN
  OPEN c3(var1 := 42);
END$$;
```

For the c3 cursor, supply the argument value expressions. If the cursor was not declared to take arguments, the arguments can be specified outside the cursor.

```
DO $$
DECLARE
  var1 integer;
  c3 CURSOR FOR SELECT * FROM employees where id = var1;
BEGIN
  var1 := 1;
  OPEN c3;
END$$;
```

 **Examples of FETCH a cursor** 

The PL/pgSQL `FETCH` command retrieves the next row from the cursor into a variable. Fetch the values returned from the `c3` cursor into a row variable.

```
DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
  OPEN c3;
  FETCH c3 INTO rowvar;
END$$;
```

Fetch the values returned from the c3 cursor into two scalar datatypes.

```
DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH c3 INTO emp_id, emp_name;
END$$;
```

PL/pgSQL supports a special direction clause when fetching data from a cursor using the `NEXT`, `PRIOR`, `FIRST`, `LAST`, `ABSOLUTE count`, `RELATIVE count`, `FORWARD`, or `BACKWARD` arguments. Omitting direction is equivalent to as specifying `NEXT`. For example, fetch the last row from the cursor into the declared variables.

```
DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
END$$;
```

For more information, see [FETCH](https://www.postgresql.org/docs/13/sql-fetch.html) in the *PostgreSQL documentation*.

 **Example of CLOSE a cursor** 

Close a PL/pgSQL cursor using the `CLOSE` command.

```
DO $$
DECLARE
  c3 CURSOR FOR SELECT id, name FROM employees;
  emp_id integer;
  emp_name varchar;
BEGIN
  OPEN c3;
  FETCH LAST FROM c3 INTO emp_id, emp_name;
  CLOSE c3;
END$$;
```

 **Example of iterating through a cursor** 

PL/pgSQL supports detecting when a cursor has no more data to return and can be combined with loops to iterate over all rows of a cursor reference.

The following PL/pgSQL code uses a loop to fetch all rows from the cursor and then exit after the last record is fetched (using `EXIT WHEN NOT FOUND`).

PL/pgSQL supports detecting when a cursor has no more data to return and can be combined with loops to iterate over all rows of a cursor reference.

The following PL/pgSQL code uses a loop to fetch all rows from the cursor and then exit after the last record is fetched (using `EXIT WHEN NOT FOUND`).

```
DO $$
DECLARE
  c3 CURSOR FOR SELECT * FROM employees;
  rowvar employees%ROWTYPE;
BEGIN
OPEN c3;
  LOOP
    FETCH FROM c3 INTO rowvar;
    EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE c3;
END$$;
```

 **Example of MOVE a cursor without fetching data** 

 `MOVE` repositions a cursor without retrieving any data and works such as the `FETCH` command, except it only repositions the cursor in the dataset and doesn’t return the row to which the cursor is moved. The special variable `FOUND` can be checked to determine if there is a next row.

Move to the last row (null or no data found) for cursor c3.

```
MOVE LAST FROM c3;
```

Move the cursor two records back.

```
MOVE RELATIVE -2 FROM c3;
```

Move the c3 cursor two records forward.

```
MOVE FORWARD 2 FROM c3;
```

 **Example of UPDATE or DELETE current** 

When a cursor is positioned on a table row, that row can be updated or deleted. There are restrictions on what the cursor’s query can select for this type of DML to succeed.

For example, the current row to which the C3 cursor is pointed to is updated.

```
UPDATE employee SET salary = salary*1.2 WHERE CURRENT OF c3;
```

 **Example of Use an Implicit Cursor (FOR Loop Over Queries)** 

```
DO $$
DECLARE
  item RECORD;
BEGIN
  FOR item IN (
    SELECT last_name, job_id
    FROM employees
    WHERE job_id LIKE '%MANAGER%'
    AND manager_id > 400
    ORDER BY last_name
  )
  LOOP
    RAISE NOTICE 'Name = %, Job=%', item.last_name, item.job_id;
  END LOOP;
END $$;
```

## Summary



| Action | Oracle PL/SQL | PostgreSQL PL/pgSQL | 
| --- | --- | --- | 
|  Declare a bound explicit cursor  |  <pre>CURSOR c1 IS<br />SELECT * FROM employees;</pre>  |  <pre>c2 CURSOR FOR<br />SELECT * FROM employees;</pre>  | 
|  Open a cursor  |  <pre>OPEN c1;</pre>  |  <pre>OPEN c2;</pre>  | 
|  Move Cursor to next row and fetch into a record variable (rowvar was declared in the DECLARE section)  |  <pre>FETCH c1 INTO rowvar;</pre>  |  <pre>FETCH c2 INTO rowvar;</pre>  | 
|  Move Cursor to next row and fetch into multiple scalar data types (emp\$1id, emp\$1name, salary was declared in the DECLARE section)  |  <pre>FETCH c1<br />INTO emp_id, emp_name, salary;</pre>  |  <pre>FETCH c2<br />INTO emp_id, emp_name, salary;</pre>  | 
|  Iterate through an implicit cursor using a loop  |  <pre>FOR item IN (<br />  SELECT last_name, job_id FROM employees<br />  WHERE job_id LIKE '%CLERK%'<br />  AND manager_id > 120 ORDER BY last_name )<br />  LOOP<br />    << do something<br />    >><br />  END LOOP;</pre>  |  <pre>FOR item IN (<br />  SELECT last_name, job_id<br />  FROM employees<br />  WHERE job_id LIKE '%CLERK%'<br />  AND manager_id > 120 ORDER BY last_name )<br />  LOOP<br />    << do something<br />    >><br />  END LOOP;</pre>  | 
|  Declare a cursor with variables  |  <pre>CURSOR c1 (key NUMBER)<br />IS SELECT * FROM employees<br />WHERE id = key;</pre>  |  <pre>C2 CURSOR (key integer)<br />FOR SELECT * FROM employees<br />WHERE id = key;</pre>  | 
|  Open a cursor with variables  |  <pre>OPEN c1(2);</pre>  |  <pre>OPEN c2(2);<br />or<br />OPEN c2(key := 2);</pre>  | 
|  Exit a loop after no data found  |  <pre>EXIT WHEN c1%NOTFOUND;</pre>  |  <pre>EXIT WHEN NOT FOUND;</pre>  | 
|  Detect if a cursor has rows remaining in its dataset  |  <pre>%FOUND</pre>  |  <pre>FOUND</pre>  | 
|  Determine how many rows were affected from any DML statement  |  <pre>%BULK_ROWCOUNT</pre>  |  Not Supported but you can run with every DML `GET DIAGNOSTICS integer_var = ROW_COUNT`; and save the results in an array  | 
|  Determine which DML run failed with the relevant error code  |  <pre>%BULK_EXCEPTIONS</pre>  |  N/A  | 
|  Detect if the Cursor is open  |  <pre>%ISOPEN</pre>  |  N/A  | 
|  Detect if a Cursor has no rows remaining in its dataset  |  <pre>%NOTFOUND</pre>  |  <pre>NOT FOUND</pre>  | 
|  Returns the number of rows affected by a cursor  |  <pre>%ROWCOUNT</pre>  |  <pre>GET DIAGNOSTICS integer_var = ROW_COUNT;</pre>  | 

For more information, see [Cursors](https://www.postgresql.org/docs/13/plpgsql-cursors.html) and [Basic Statements](https://www.postgresql.org/docs/13/plpgsql-statements.html) in the *PostgreSQL documentation*.

# Oracle DBMS\$1OUTPUT and PostgreSQL RAISE


Oracle’s `DBMS_OUTPUT` and PostgreSQL’s `RAISE` are utilities that let you display status information and handle errors during the migration process.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  N/A  | 

## Oracle usage


The Oracle `DBMS_OUTPUT` package is typically used for debugging or for displaying output messages from PL/SQL procedures.

 **Examples** 

In the following example, `DBMS_OUTPUT` with `PUT_LINE` is used with a combination of bind variables to dynamically construct a string and print a notification to the screen from within an Oracle PL/SQL procedure. In order to display notifications on to the screen, you must configure the session with `SET SERVEROUTPUT ON`.

```
SET SERVEROUTPUT ON
DECLARE
CURSOR c1 IS
SELECT last_name, job_id FROM employees
WHERE REGEXP_LIKE (job_id, 'S[HT]_CLERK')
ORDER BY last_name;
v_lastname employees.last_name%TYPE; -- variable to store last_name
v_jobid employees.job_id%TYPE; -- variable to store job_id
BEGIN
OPEN c1;
LOOP -- Fetches 2 columns into variables
FETCH c1 INTO v_lastname, v_jobid;
DBMS_OUTPUT.PUT_LINE ('The employee id is:' || v_jobid || ' and his last name is:' ||
v_lastname);
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
```

In addition to the output of information on the screen, the `PUT` and `PUT_LINE` procedures in the `DBMS_OUTPUT` package enable you to place information in a buffer that can be read later by another PL/SQL procedure or package. You can display the previously buffered information using the `GET_LINE` and `GET_LINES` procedures.

For more information, see [DBMS\$1OUTPUT](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_OUTPUT.html#GUID-C1400094-18D5-4F36-A2C9-D28B0E12FD8C) in the *Oracle documentation*.

## PostgreSQL usage


You can use the PostgreSQL `RAISE` statement as an alternative to `DBMS_OUTPUT`. You can combine `RAISE` with several levels of severity including.


| Severity | Usage | 
| --- | --- | 
|   `DEBUG1..DEBUG5`   |  Provides successively-more-detailed information for use by developers.  | 
|   `INFO`   |  Provides information implicitly requested by the user  | 
|   `NOTICE`   |  Provides information that might be helpful to users  | 
|   `WARNING`   |  Provides warnings of likely problems  | 
|   `ERROR`   |  Reports an error that caused the current command to abort.  | 
|   `LOG`   |  Reports information of interest to administrators, e.g., checkpoint activity.  | 
|   `FATAL`   |  Reports an error that caused the current session to abort.  | 
|   `PANIC`   |  Reports an error that caused all database sessions to abort.  | 

 **Examples** 

Use `RAISE DEBUG` (where `DEBUG` is the configurable severity level) for similar functionality as Oracle `DBMS_OUTPUT.PUT_LINE` feature.

```
SET CLIENT_MIN_MESSAGES = 'debug';
-- Equivalent To Oracle SET SERVEROUTPUT ON

DO $$
BEGIN
RAISE DEBUG USING MESSAGE := 'hello world';
END $$;

DEBUG: hello world
DO
```

Use the `client_min_messages` parameter to control the level of message sent to the client. The default is `NOTICE`. Use the log\$1min\$1messages parameter to control which message levels are written to the server log. The default is `WARNING`.

```
SET CLIENT_MIN_MESSAGES = 'debug';
```

For more information, see [Errors and Messages](https://www.postgresql.org/docs/13/plpgsql-errors-and-messages.html) and [When to Log](https://www.postgresql.org/docs/13/runtime-config-logging.html#GUC-LOG-MIN-MESSAGES) in the *PostgreSQL documentation*.

## Summary



| Feature | Oracle | PostgreSQL | 
| --- | --- | --- | 
|  Disables message output.  |  <pre>DISABLE</pre>  |  Configure “client\$1min\$1message” or “log\$1min\$1message” for the desired results.  | 
|  Enables message output.  |  <pre>ENABLE</pre>  |  Configure “client\$1min\$1message” or “log\$1min\$1message” for the desired results.  | 
|  Retrieves one line from buffer.  |  <pre>GET_LINE</pre>  |  Consider storing messages in an array or temporary table so that you can retrieve them from another procedure or package.  | 
|  Retrieves an array of lines from buffer.  |  <pre>GET_LINES</pre>  |  Consider storing messages in an array or temporary table so that you can retrieve them from another procedure or package.  | 
|  Terminates a line created with `PUT` and places a partial line in the buffer.  |  <pre>PUT + NEW_LINE<br />BEGIN<br />DBMS_OUTPUT.PUT ('1,');<br />DBMS_OUTPUT.PUT('2,');<br />DBMS_OUTPUT.PUT('3,');<br />DBMS_OUTPUT.PUT('4');<br />DBMS_OUTPUT.NEW_LINE();<br />END;<br />/</pre>  |  Store and concatenate the message string in a varchar variable before raising <pre>do $$<br />DECLARE<br />message varchar :='';<br />begin<br />message := concat(message,'1,');<br />message := concat(message,'2,');<br />message := concat(message,'3,');<br />message := concat(message,'4,');<br />RAISE NOTICE '%',<br />message;<br />END$$;</pre>  | 
|  Places line in buffer  |  <pre>PUT_LINE</pre>  |  <pre>RAISE</pre>  | 
|  Returns the number code of the most recent exception  |  <pre>SQLCODE + SQLERRM</pre>  |  <pre>SQLSTATE + SQLERRM</pre>  | 
|  Returns the error message associated with its errornumber argument.  |  <pre>DECLARE<br />Name employees.last_name%TYPE;<br />BEGIN<br />SELECT last_name INTO name<br />FROM employees<br />WHERE employee_id = -1;<br />EXCEPTION<br />WHEN OTHERS then<br />DBMS_OUTPUT.PUT_LINE<br />(CONCAT('Error code ',<br />  SQLCODE,': ',sqlerrm);<br />END;<br />/</pre>  |  <pre>do $$<br />declare<br />Name employees%ROWTYPE;<br />BEGIN<br />SELECT last_name INTO name FROM<br />employees WHERE employee_id = -1;<br />EXCEPTION<br />WHEN OTHERS then<br />RAISE NOTICE 'Error code %: %', sqlstate,<br />sqlerrm;<br />end$$;</pre>  | 

For more information, see [PostgreSQL Error Codes](https://www.postgresql.org/docs/13/errcodes-appendix.html) in the *PostgreSQL documentation*.

# Oracle DBMS\$1RANDOM and PostgreSQL RANDOM function


With AWS DMS, you can generate random numbers or randomly select data for various purposes, such as testing, sampling, or introducing randomness in your applications. Oracle `DBMS_RANDOM` and PostgreSQL `RANDOM` function provide methods to generate random numbers or randomly select data from a specified dataset.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  Different syntax may require code rewrite.  | 

## Oracle usage


Oracle `DBMS_RANDOM` package provides functionality for generating random numbers or strings as part of an SQL statement or PL/SQL procedure.

The `DBMS_RANDOM` Package stored procedures include:
+  **NORMAL** — Returns random numbers in a standard normal distribution.
+  **SEED** — Resets the seed that generates random numbers or strings.
+  **STRING** — Returns a random string.
+  **VALUE** — Returns a number greater than or equal to 0 and less than 1 with 38 digits to the right of the decimal. Alternatively, you could generate a random number greater than or equal to a low parameter and less than a high parameter.

 `DBMS_RANDOM.RANDOM` produces integers in the range [-2^^31, 2^^31].

 `DBMS_RANDOM.VALUE` produces numbers in the range [0,1] with 38 digits of precision.

 **Examples** 

Generate a random number.

```
select dbms_random.value() from dual;

DBMS_RANDOM.VALUE()
.859251508

select dbms_random.value() from dual;

DBMS_RANDOM.VALUE()
.364792387
```

Generate a random string. The first character determines the returned string type and the number specifies the length.

```
select dbms_random.string('p',10) from dual;
DBMS_RANDOM.STRING('P',10)

la'?z[Q&/2

select dbms_random.string('p',10) from dual;
DBMS_RANDOM.STRING('P',10)

t?!Gf2M60q
```

For more information, see [DBMS\$1RANDOM](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RANDOM.html#GUID-8DC48B0C-3707-4172-A306-C0308DD2EB0F) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL doesn’t provide a dedicated package equivalent to Oracle `DBMS_RANDOM`, a 1:1 migration isn’t possible. However, you can use other PostgreSQL functions as workarounds under certain conditions. For example, generating random numbers can be performed using the `random()` function. For generating random strings, you can use the value returned from the `random()` function coupled with an `md5()` function.

 **Examples** 

Generate a random number.

```
select random();
random

0.866594325285405
(1 row)

select random();
random

0.524613124784082
(1 row)
```

Generate a random string.

```
select md5(random()::text);
md5

f83e73114eccfed571b43777b99e0795
(1 row)

select md5(random()::text);
md5

d46de3ce24a99d5761bb34bfb6579848
(1 row)
```

To generate a random string of the specified length, you can use the following function.

```
create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;
```

The following code example shows the result of using this function.

```
select random_string(15);
random_string

5emZKMYxB9C2vT6
(1 row)

select random_string(10);
random_string

tMAxfql0iM
(1 row)
```

## Summary



| Description | Oracle | PostgreSQL | 
| --- | --- | --- | 
|  Generate a random number  |   `select dbms_random.value() from dual;`   |   `select random();`   | 
|  Generate a random number between 1 to 100  |   `select dbms_random.value(1,100) from dual;`   |   `select random()*100;`   | 
|  Generate a random string  |   `select dbms_random.string('p',10) from dual;`   |   `select md5(random()::text);`   | 
|  Generate a random string in upper case  |   `select dbms_random.string('U',10) from dual;`   |   `select upper(md5(random()::text));`   | 

For more information, see [Mathematical Functions and Operators](https://www.postgresql.org/docs/13/functions-math.html) and [String Functions and Operators](https://www.postgresql.org/docs/10/functions-string.html) in the *PostgreSQL documentation*.

# Oracle DBMS\$1SQL package and PostgreSQL dynamic execution


With AWS DMS, you can dynamically construct and execute SQL statements at runtime in your source and target databases. The Oracle `DBMS_SQL` package and PostgreSQL dynamic execution feature provide interfaces for building SQL statements, binding values to placeholders, and processing query results dynamically. These capabilities are essential when writing database applications that must customize queries based on user input or runtime conditions.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[One star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-1.png)   |   ![\[One star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-1.png)   |  N/A  |  Different paradigm and syntax will require application and drivers rewrite.  | 

## Oracle usage


The `DBMS_SQL` package provides an interface to parse and run dynamic SQL statements, DML commands, and DDL commands (usually from within a PL/SQL package, function, or procedure). `DBMS_SQL` enables very granular control of SQL cursors and can improve cursor performance in certain cases.

 **Examples** 

The following examples demonstrats how to manually open, parse, bind, run, and fetch data from a cursor using the `DBMS_SQL` PL/SQL interface.

1. Use `DBMS_SQL.OPEN_CURSOR` to open a blank cursor and return the cursor handle.

1. Use `DBMS_SQL.PARSE` to parse the statement into the referenced cursor.

1. Use `DBMS_SQL.BIND_VARIABLES` to attach the value for the bind variable with the cursor.

1. Use `DBMS_SQL.EXECUTE` to run the cursor.

1. Use `DBMS_SQL.GET_NEXT_RESULT` to iterate over the cursor, fetching the next result.

1. Use `DBMS_SQL.CLOSE_CURSOR` to close the cursor.

```
DECLARE
c1           INTEGER;
rc1          SYS_REFCURSOR;
n            NUMBER;
first_name   VARCHAR2(50);
last_name    VARCHAR2(50);
email        VARCHAR2(50);
phone_number VARCHAR2(50);
job_title    VARCHAR2(50);
start_date   DATE;
end_date     DATE;
BEGIN
c1 := DBMS_SQL.OPEN_CURSOR(true);
DBMS_SQL.PARSE
  (c1, 'BEGIN emp_info(:id); END;', DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(c1, ':id', 176);
n := DBMS_SQL.EXECUTE(c1);
-- Get employee info
DBMS_SQL.GET_NEXT_RESULT(c1, rc1);
FETCH rc1 INTO first_name, last_name, email, phone_number;
-- Get employee job history
DBMS_SQL.GET_NEXT_RESULT(c1, rc1);
LOOP
FETCH rc1 INTO job_title, start_date, end_date;
EXIT WHEN rc1%NOTFOUND;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c1);
END;
/
```

The `DBMS_SQL` package includes three other procedures.
+  `RETURN_RESULT` (New in oracle 12c) — Gets a result set and returns it to the client. Because the procedure already returns a result set, the invoker doesn’t have to know the format of the result or the columns it contains (most often used with SQL\$1Plus).
+  `TO_REFCURSOR` — When using `DBMS_SQL.OPEN_CURSOR`, the numeric cursor ID is returned. If you know the structure of the result of the cursor, you can call the `TO_REFCURSOR` procedure, stop working with DBMS\$1SQL, and move to regular commands such as `FETCH`, `WHEN CURSOR%notfound`, and others. Before using `TO_REFCURSOR`, use the procedures `OPEN_CURSOR`, `PARSE` and `EXECUTE`.
+  `TO_CURSOR_NUMBER` — Gets a cursor opened in native dynamic SQL. After the cursor is open, it can be converted to a number (cursor id) and then managed using DBMS\$1SQL procedures.

For more information, see [DBMS\$1SQL](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQL.html#GUID-C96D5BAA-29A9-4AB5-A69E-E31228ECC9E9) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL doesn’t support granular control of programmatic cursors and thus doesn’t have an equivalent for Oracle `DBMS_SQL`.

However, you can dynamically parse and run SQL statements in PostgreSQL. Find the two examples following.

 **Examples** 

Create dynamic cursor by using `FOR` with `SELECT`.

```
CREATE OR REPLACE FUNCTION GetErrors ()
RETURNS VARCHAR
AS
$$
DECLARE
_currow RECORD;
msg VARCHAR(200);
TITLE VARCHAR(10);
CODE_NUM VARCHAR(10);
BEGIN
msg := '';

FOR _currow IN SELECT TITLE, CODE_NUM, count(*) FROM A group by TITLE,CODE_NUM
LOOP
  TITLE := _currow.TITLE;
  CODE_NUM := _currow.CODE_NUM;
  msg := msg||rpad(TITLE,20)||rpad(CODE_NUM,20);
END LOOP;
RETURN msg;

END;
$$ LANGUAGE plpgsql;
```

Create cursor and then open it for run with given SQL.

```
CREATE OR REPLACE FUNCTION GetErrors () RETURNS VARCHAR AS $$
declare
    refcur refcursor;
    c_id integer;
    title varchar (10);
    code_num varchar (10);
    alert_mesg VARCHAR(1000) := '';
BEGIN
    OPEN refcur FOR execute('select * from Errors');
    loop
      fetch refcur into title, code_num;
        if not found then
          exit;
        end if;
      alert_mesg := alert_mesg||rpad(title,20)||rpad(code_num,20);
    end loop;
close refcur;
return alert_mesg;
END;
$$ LANGUAGE plpgsql
```

For more information, see [DEALLOCATE](https://www.postgresql.org/docs/13/sql-deallocate.html), [PREPARE](https://www.postgresql.org/docs/13/sql-prepare.html), and [Executing Dynamic Commands](https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) in the *PostgreSQL documentation*.

# Oracle EXECUTE IMMEDIATE and PostgreSQL EXECUTE and PREPARE


With AWS DMS, you can run dynamic SQL statements and prepared statements on source and target databases during a database migration. Oracle’s `EXECUTE IMMEDIATE` statement evaluates a string literal containing SQL statements at runtime. PostgreSQL’s `EXECUTE` statement executes a previously prepared statement, while `PREPARE` creates a prepared statement from a string literal.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png)   |   ![\[Two star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-2.png)   |  N/A  |  N/A  | 

## Oracle usage


You can use Oracle `EXECUTE IMMEDIATE` statement to parse and run a dynamic SQL statement or an anonymous PL/SQL block. It also supports bind variables.

 **Examples** 

Run a dynamic SQL statement from within a PL/SQL procedure:

1. Create a PL/SQL procedure named `raise_sal`.

1. Define a SQL statement with a dynamic value for the column name included in the `WHERE` statement.

1. Use the `EXECUTE IMMEDIATE` command supplying the two bind variables to be used as part of the `SELECT` statement: `amount` and `col_val`.

   ```
   CREATE OR REPLACE PROCEDURE raise_sal (col_val NUMBER,
   emp_col VARCHAR2, amount NUMBER) IS
     col_name VARCHAR2(30);
     sql_stmt VARCHAR2(350);
   BEGIN
     -- determine if a valid column name has been given as input
     SELECT COLUMN_NAME INTO col_name FROM USER_TAB_COLS
     WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_col;
   
     -- define the SQL statment (with bind variables)
     sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' ||
     col_name || ' = :2';
   
     -- Run the command
     EXECUTE IMMEDIATE sql_stmt USING amount, col_val;
   END raise_sal;
   /
   ```

1. Run the DDL operation from within an `EXECUTE IMMEDIATE` command.

   ```
   EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)';
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
   ```

1. Run an anonymous block with bind variables using `EXECUTE IMMEDIATE`.

   ```
   EXECUTE IMMEDIATE 'BEGIN raise_sal (:col_val, :col_name, :amount); END;'
     USING 134, 'EMPLOYEE_ID', 10;
   ```

For more information, see [EXECUTE IMMEDIATE Statement](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/EXECUTE-IMMEDIATE-statement.html#GUID-C3245A95-B85B-4280-A01F-12307B108DC8) in the *Oracle documentation*.

## PostgreSQL usage


The PostgreSQL `EXECUTE` command prepares and runs commands dynamically. The `EXECUTE` command can also run DDL statements and retrieve data using SQL commands. Similar to Oracle, you can use the PostgreSQL `EXECUTE` command with bind variables.

 **Examples** 

Execute a SQL SELECT query with the table name as a dynamic variable using bind variables. This query returns the number of employees under a manager with a specific ID.

```
DO $$DECLARE
Tabname varchar(30) := 'employees';
num integer := 1;
cnt integer;
BEGIN
EXECUTE format('SELECT count(*) FROM %I WHERE manager = $1', tabname)
INTO cnt USING num;
RAISE NOTICE 'Count is % int table %', cnt, tabname;
END$$;
;
```

Run a DML command with no variables and then with variables.

```
DO $$DECLARE
BEGIN
EXECUTE 'INSERT INTO numbers (a) VALUES (1)';
EXECUTE format('INSERT INTO numbers (a) VALUES (%s)', 42);
END$$;
;
```

**Note**  
 `%s` formats the argument value as a simple string. A null value is treated as an empty string.  
 `%I` treats the argument value as an SQL identifier and double-quoting it if necessary. It is an error for the value to be null.

Run a DDL command.

```
DO $$DECLARE
BEGIN
EXECUTE 'CREATE TABLE numbers (num integer)';
END$$;
;
```

For more information, see [String Functions and Operators](https://www.postgresql.org/docs/13/functions-string.html) in the *PostgreSQL documentation*.

Using a `PREPARE` statement can improve performance for reusable SQL statements.

The `PREPARE` command can receive a `SELECT`, `INSERT`, `UPDATE`, `DELETE`, or `VALUES` statement and parse it with a user-specified qualifying name so you can use the `EXECUTE` command later without the need to re-parse the SQL statement on each run.
+ When using `PREPARE` to create a prepared statement, it will be viable for the scope of the current session.
+ If you run a DDL command on a database object referenced by the prepared SQL statement, the next `EXECUTE` command requires a hard parse of the SQL statement.

 **Example** 

Use `PREPARE` and `EXECUTE` commands together.

1. The SQL command is prepared with a user-specified qualifying name.

1. The SQL command runs several times, without the need for re-parsing.

```
PREPARE numplan (int, text, bool) AS
INSERT INTO numbers VALUES($1, $2, $3);

EXECUTE numplan(100, 'New number 100', 't');
EXECUTE numplan(101, 'New number 101', 't');
EXECUTE numplan(102, 'New number 102', 'f');
EXECUTE numplan(103, 'New number 103', 't');
```

## Summary



| Functionality | Oracle EXECUTE IMMEDIATE | PostgreSQL EXECUTE | 
| --- | --- | --- | 
|  Execute SQL with results and bind variables  |  <pre>EXECUTE IMMEDIATE 'select salary<br />from employees WHERE ' || col_name ||<br />' = :1' INTO amount USING col_val;</pre>  |  <pre>EXECUTE format('select salary from employees<br />WHERE %I = $1', col_name) INTO<br />amount USING col_val;</pre>  | 
|  Execute DML with variables and bind variables  |  <pre>EXECUTE IMMEDIATE 'UPDATE<br />employees SET salary = salary + :1<br />WHERE ' || col_name || ' = :2'<br />USING amount, col_val;</pre>  |  <pre>EXECUTE format('UPDATE employees<br />SET salary = salary + $1 WHERE %I = $2',<br />col_name) USING amount, col_val;</pre>  | 
|  Execute DDL  |  <pre>EXECUTE IMMEDIATE 'CREATE<br />TABLE link_emp (idemp1 NUMBER,<br />idemp2 NUMBER)';</pre>  |  <pre>EXECUTE 'CREATE TABLE link_emp<br />(idemp1 integer, idemp2 integer)';</pre>  | 
|  Execute anonymous block  |  <pre>EXECUTE IMMEDIATE 'BEGIN<br />DBMS_OUTPUT.PUT_LINE<br />("Anonymous Block"); END;';</pre>  |  <pre>DO $$DECLARE<br />BEGIN ... END$$;</pre>  | 

For more information, see [Basic Statements](https://www.postgresql.org/docs/13/plpgsql-statements.html) in the *PostgreSQL documentation*.

# Oracle procedures and functions and PostgreSQL stored procedures


With AWS DMS, you can migrate Oracle procedures and functions, as well as PostgreSQL stored procedures, to various target databases supported by the service. Oracle procedures and functions are reusable code blocks written in PL/SQL that perform specific tasks within an Oracle database. PostgreSQL stored procedures are similar reusable code blocks for PostgreSQL databases.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   [Stored Procedures](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.procedures)   |  Syntax and option differences  | 

## Oracle usage


PL/SQL is Oracle built-in database programming language providing several methods to store and run reusable business logic from within the database. Procedures and functions are reusable snippets of code created using the `CREATE PROCEDURE` and the `CREATE FUNCTION` statements.

Stored procedures and stored functions are PL/SQL units of code consisting of SQL and PL/SQL statements that solve specific problems or perform a set of related tasks.

 **Procedure** is used to perform database actions with PL/SQL.

 **Function** is used to perform a calculation and return a result.

### Privileges for creating procedures and functions


To create procedures and functions in their own schema, Oracle database users need the `CREATE PROCEDURE` system privilege.

To create procedures or functions in other schemas, database users need the `CREATE ANY PROCEDURE` privilege.

To run a procedure or function, database users need the `EXECUTE` privilege.

### Package and package body


In addition to stored procedures and functions, Oracle also provides packages to encapsulate related procedures, functions, and other program objects.

 **Package** declares and describes all the related PL/SQL elements.

 **Package Body** contains the executable code.

To run a stored procedure or function created inside a package, specify the package name and the stored procedure or function name.

```
EXEC PKG_EMP.CALCULTE_SAL('100');
```

 **Examples** 

Create an Oracle stored procedure using the `CREATE OR REPLACE PROCEDURE` statement. The optional `OR REPLACE` clause overwrites an existing stored procedure with the same name if it exists.

```
CREATE OR REPLACE PROCEDURE EMP_SAL_RAISE
(P_EMP_ID IN NUMBER, SAL_RAISE IN NUMBER)
AS
V_EMP_CURRENT_SAL NUMBER;
BEGIN
SELECT SALARY INTO V_EMP_CURRENT_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=P_EMP_ID;
UPDATE EMPLOYEES
SET SALARY=V_EMP_CURRENT_SAL+SAL_RAISE
WHERE EMPLOYEE_ID=P_EMP_ID;
DBMS_OUTPUT.PUT_LINE('New Salary For Employee ID: '||P_EMP_ID||' Is '||(V_EMP_CURRENT_
SAL+SAL_RAISE));
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR-
'||SQLERRM);
ROLLBACK;
COMMIT;
END;
/
-- Run
EXEC EMP_SAL_RAISE(200, 1000);
```

Create a function using the `CREATE OR REPLACE FUNCTION` statement.

```
CREATE OR REPLACE FUNCTION EMP_PERIOD_OF_SERVICE_YEAR
(P_EMP_ID NUMBER)
RETURN NUMBER
AS
V_PERIOD_OF_SERVICE_YEARS NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(HIRE_DATE)) INTO V_PERIOD_OF_SERVICE_YEARS
FROM EMPLOYEES
WHERE EMPLOYEE_ID=P_EMP_ID;
RETURN V_PERIOD_OF_SERVICE_YEARS;
END;
/

SELECT EMPLOYEE_ID,FIRST_NAME, EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS PERIOD_OF_SERVICE_YEAR FROM EMPLOYEES;
EMPLOYEE_ID  FIRST_NAME  PERIOD_OF_SERVICE_YEAR
174          Ellen       13
166          Sundar      9
130          Mozhe       12
105          David       12
204          Hermann     15
116          Shelli      12
167          Amit        9
172          Elizabeth   10
```

Create a package using the `CREATE OR REPLACE PACKAGE` statement.

```
CREATE OR REPLACE PACKAGE PCK_CHINOOK_REPORTS
AS
PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE);
PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE;
END;
```

Create a new package using the `CREATE OR REPLACE PACKAGE BODY` statement.

```
CREATE OR REPLACE PACKAGE BODY PCK_CHINOOK_REPORTS
AS
PROCEDURE GET_ARTIST_BY_ALBUM(P_ARTIST_ID ALBUM.TITLE%TYPE)
IS
V_ARTIST_NAME ARTIST.NAME%TYPE;
BEGIN
SELECT ART.NAME INTO V_ARTIST_NAME
FROM ALBUM ALB JOIN ARTIST ART USING(ARTISTID)
WHERE ALB.TITLE=P_ARTIST_ID;
DBMS_OUTPUT.PUT_LINE('ArtistName: '||V_ARTIST_NAME);
END;

PROCEDURE CUST_INVOICE_BY_YEAR_ANALYZE
AS
V_CUST_GENRES VARCHAR2(200);
BEGIN
FOR V IN(SELECT CUSTOMERID, CUSTNAME, LOW_YEAR, HIGH_YEAR, CUST_AVG FROM TMP_CUST_
INVOICE_ANALYSE)
LOOP
IF SUBSTR(V.LOW_YEAR, -4) > SUBSTR(V.HIGH_YEAR , -4) THEN
SELECT LISTAGG(GENRE, ',') WITHIN GROUP (ORDER BY GENRE) INTO V_CUST_GENRES FROM
(SELECT DISTINCT
FUNC_GENRE_BY_ID(TRC.GENREID) AS GENRE
FROM TMP_CUST_INVOICE_ANALYSE TMPTBL JOIN INVOICE INV USING(CUSTOMERID)
JOIN INVOICELINE INVLIN
ON INV.INVOICEID = INVLIN.INVOICEID
JOIN TRACK TRC
ON TRC.TRACKID = INVLIN.TRACKID
WHERE CUSTOMERID=V.CUSTOMERID);
DBMS_OUTPUT.PUT_LINE('Customer: '||UPPER(V.CUSTNAME)||' - Offer a Discount According
To Preferred Genres: '||UPPER(V_CUST_GENRES));
END IF;
END LOOP;
END;
END;

EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM();
EXEC PCK_CHINOOK_REPORTS.CUST_INVOICE_BY_YEAR_ANALYZE;
```

The preceding examples demonstrate basic Oracle PL/SQL procedure and function capabilities. Oracle PL/SQL provides a large number of features and capabilities that aren’t within the scope of this document.

For more information, see [CREATE FUNCTION](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-FUNCTION.html#GUID-156AEDAC-ADD0-4E46-AA56-6D1F7CA63306) and [CREATE PROCEDURE](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-PROCEDURE.html#GUID-771879D8-BBFD-4D87-8A6C-290102142DA3) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL provides support for both stored procedures and stored functions using the `CREATE FUNCTION` statement. To emphasize, the procedural statements used by PostgreSQL support the `CREATE FUNCTION` statement only. The `CREATE PROCEDURE` statement isn’t compatible with this PostgreSQL version.

PL/pgSQL is the main database programming language used for migrating from Oracle PL/SQL code. PostgreSQL support additional programming languages, also available in Amazon Aurora PostgreSQL:
+ PL/pgSQL
+ PL/Tcl
+ PL/Perl.

Use the `show.rds.extensions` command to view all available extensions for Amazon Aurora.

 **Interchangeability between Oracle PL/SQL and PostgreSQL PL/pgSQL** 

PostgreSQL PL/pgSQL language is often considered the ideal candidate to migrate from Oracle PL/SQL code because many of the Oracle PL/SQL syntax elements are supported by PostgreSQL PL/pgSQL code.

For example, Oracle `CREATE OR REPLACE PROCEDURE` statement is supported by PostgreSQL PL/pgSQL. Many other PL/SQL syntax elements are also supported making PostgreSQL and PL/pgSQL natural alternatives when migrating from Oracle.

 **PostgreSQL create function privileges** 

To create a function, a user must have `USAGE` privilege on the language. When creating a function, a language parameter can be specified as shown in the examples.

 **Examples** 

Converting Oracle Stored Procedures and Functions to PostgreSQL PL/pgSQL.

Use the PostgreSQL `CREATE FUNCTION` command to create a new function named `FUNC_ALG`.

```
CREATE OR REPLACE FUNCTION FUNC_ALG(P_NUM NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
  RETURN P_NUM * 2;
END; $$
LANGUAGE PLPGSQL;
```

Using a `CREATE OR REPLACE` statement creates a new function, or replaces an existing function, with these limitations:
+ You can’t change the function name or argument types.
+ The statement doesn’t allow changing the existing function return type.
+ The user must own the function to replace it.
+  `INPUT` parameter (`P_NUM`) is implemented similarly to Oracle PL/SQL `INPUT` parameter.
+ Two dollar signs are used to prevent the need to use single-quoted string escape elements. With the two dollar signs, there is no need to use escape characters in the code when using single quotation marks ( ' ). The two dollar signs appear after the keyword `AS` and after the function keyword `END`.
+ Use the `LANGUAGE PLPGSQL` parameter to specify the language for the created function.

Convert the Oracle `EMP_SAL_RAISE` PL/SQL function to PostgreSQL PL/pgSQL.

```
CREATE OR REPLACE FUNCTION EMP_SAL_RAISE
(IN P_EMP_ID DOUBLE PRECISION,
IN SAL_RAISE DOUBLE PRECISION)
RETURNS VOID
AS $$
DECLARE
V_EMP_CURRENT_SAL DOUBLE PRECISION;
BEGIN
SELECT SALARY INTO STRICT V_EMP_CURRENT_SAL
FROM EMPLOYEES WHERE EMPLOYEE_ID = P_EMP_ID;

UPDATE EMPLOYEES SET SALARY = V_EMP_CURRENT_SAL +
SAL_RAISE WHERE EMPLOYEE_ID = P_EMP_ID;

RAISE DEBUG USING MESSAGE := CONCAT_WS('',
'NEW SALARY FOR EMPLOYEE ID: ', P_EMP_ID, 'IS ',
(V_EMP_CURRENT_SAL + SAL_RAISE));
EXCEPTION
WHEN OTHERS THEN
RAISE USING ERRCODE := '20001', MESSAGE :=
CONCAT_WS('', 'AN ERROR WAS ENCOUNTERED - ',
SQLSTATE, ' -ERROR-', SQLERRM);
END; $$
LANGUAGE PLPGSQL;
select emp_sal_raise(200, 1000);
```

Convert the Oracle `EMP_PERIOD_OF_SERVICE_YEAR` PL/SQL function to PostgreSQL PL/pgSQL.

```
CREATE OR REPLACE FUNCTION
EMP_PERIOD_OF_SERVICE_YEAR (IN P_EMP_ID DOUBLE PRECISION)
RETURNS DOUBLE PRECISION
AS $$
DECLARE
V_PERIOD_OF_SERVICE_YEARS DOUBLE PRECISION;
BEGIN
SELECT
EXTRACT (YEAR FROM NOW()) - EXTRACT (YEAR FROM (HIRE_DATE))
INTO STRICT V_PERIOD_OF_SERVICE_YEARS
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP_ID;
RETURN V_PERIOD_OF_SERVICE_YEARS;
END; $$
LANGUAGE PLPGSQL;
SELECT EMPLOYEE_ID,FIRST_NAME,
EMP_PERIOD_OF_SERVICE_YEAR(EMPLOYEE_ID) AS
PERIOD_OF_SERVICE_YEAR
FROM EMPLOYEES;
```

### Oracle Packages and Package Bodies


PostgreSQL doesn’t support Oracle packages and package bodies. All PL/SQL objects must be converted to PostgreSQL functions. The following examples describe how the Amazon Schema Conversion Tool (SCT) handles Oracle packages and package body names.

Oracle package name: `PCK_CHINOOK_REPORTS`. Oracle package body: `GET_ARTIST_BY_ALBUM`.

```
EXEC PCK_CHINOOK_REPORTS.GET_ARTIST_BY_ALBUM('');
```

The PostgreSQL code converted with AWS SCT uses the `$` sign to separate the package and the package name.

```
SELECT PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM('');
```

 **Examples** 

Convert an Oracle package and package body to PostgreSQL PL/pgSQL.

In the following example, the Oracle package name is `PCK_CHINOOK_REPORTS`, and the Oracle package body is `GET_ARTIST_BY_ALBUM`.

```
CREATE OR REPLACE FUNCTION
  chinook."PCK_CHINOOK_REPORTS$GET_ARTIST_BY_ALBUM"
  (p_artist_id text)
  RETURNS void
  LANGUAGE plpgsql
  AS $function$
  DECLARE
    V_ARTIST_NAME CHINOOK.ARTIST.NAME%TYPE;
  BEGIN
    SELECT art.name INTO STRICT V_ARTIST_NAME
    FROM chinook.album AS alb
    JOIN chinook.artist AS art
    USING (artistid)
    WHERE alb.title = p_artist_id;
  RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'ArtistName: ', V_ARTIST_NAME);
END;
$function$;

-- Procedures (Packages) Verification
set client_min_messages = 'debug';
-- Equivalent to Oracle SET SERVEROUTPUT ON
select chinook.pck_chinook_reports$get_artist_by_album(' Fireball');
```

In the following example, the Oracle package name is `PCK_CHINOOK_REPORTS`, and the Oracle package body is `CUST_INVOICE_BY_YEAR_ANALYZE`.

```
CREATE OR REPLACE FUNCTION chinook."pck_chinook_reports$cust_invoice_by_year_analyze" ()
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
  v_cust_genres CHARACTER VARYING(200);
  v RECORD;
BEGIN
  FOR v IN
  SELECT customerid, custname, low_year, high_year, cust_avg
  FROM chinook.tmp_cust_invoice_analyse
  LOOP
    IF SUBSTR(v.low_year, - 4) > SUBSTR(v.high_year, - 4) THEN
-- Altering Oracle LISTAGG Function With PostgreSQL STRING_AGG Function
      select string_agg(genre, ',') into v_cust_genres
      from (select distinct chinook.func_genre_by_id(trc.genreid) as genre
      from chinook.tmp_cust_invoice_analyse tmptbl
      join chinook.INVOICE inv using(customerid)
      join chinook.INVOICELINE invlin on inv.invoiceid = invlin.invoiceid
      join chinook.TRACK trc on trc.trackid = invlin.trackid
      where customerid=v.CUSTOMERID) a;

-- PostgreSQL Equivalent To Oracle DBMS_OUTPUT.PUT_LINE()\
    RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Customer: ',
    UPPER(v.custname), ' - Offer a Discount According To Preferred Genres: ', UPPER(v_
    cust_genres));
    END IF;
  END LOOP;
END;
$function$;

-- Running
SELECT chinook.pck_chinook_reports$cust_invoice_by_year_analyze();
```

New behavior in PostgreSQL version 10 for a set-returning function, used by the `LATERAL FROM` clause.

 **Previous** 

```
CREATE TABLE emps (id int, manager int);
INSERT INTO tab VALUES (23, 24), (52, 23), (21, 65);
SELECT x, generate_series(1,5) AS g FROM tab;
id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5
```

 **New** 

```
SELECT id, g FROM emps, LATERAL generate_series(1,5) AS g;
id |g
---|--
23 |1
23 |2
23 |3
23 |4
23 |5
52 |1
52 |2
52 |3
52 |4
52 |5
21 |1
21 |2
21 |3
21 |4
21 |5
```

Here the planner could choose to put the set-return function on the outside of the nestloop join, since it has no actual lateral dependency on emps table.

For more information, see [CREATE FUNCTION](https://www.postgresql.org/docs/13/sql-createfunction.html), [PL/pgSQL — SQL Procedural Language](https://www.postgresql.org/docs/13/plpgsql.html), [https://www.postgresql.org/docs/13/xplang.html](https://www.postgresql.org/docs/13/xplang.html), and [Query Language (SQL) Functions](https://www.postgresql.org/docs/13/xfunc-sql.html) in the *PostgreSQL documentation*.

# Oracle and PostgreSQL user-defined functions


With AWS DMS, you can migrate user-defined functions (UDFs) from Oracle and PostgreSQL databases to compatible target databases. UDFs are custom functions written in programming languages like PL/SQL or SQL that extend the functionality of the database management system.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   [Stored Procedures](chap-oracle-aurora-pg.tools.actioncode.md#chap-oracle-aurora-pg.tools.actioncode.procedures)   |  Syntax and option differences.  | 

## Oracle usage


You can create an Oracle user-defined function (UDF) using PL/SQL, Java, or C. UDFs are useful for providing functionality not available in SQL or SQL built-in functions. They can appear in SQL statements wherever built-in SQL functions can appear.

You can use UDFs in the following cases:
+ To return a single value from a `SELECT` statement (scalar function).
+ While performing DML operations.
+ In `WHERE`, `GROUP BY`, `ORDER BY`, `HAVING`, `CONNECT BY`, and `START WITH` clauses.

 **Examples** 

Create a simple Oracle UDF with arguments for employee `HIRE_DATE` and `SALARY` as `INPUT` parameters and calculate the overall salary over the employee’s years of service for the company.

```
CREATE OR REPLACE FUNCTION TOTAL_EMP_SAL_BY_YEARS
(p_hire_date DATE, p_current_sal NUMBER)
RETURN NUMBER
AS
v_years_of_service NUMBER;
v_total_sal_by_years NUMBER;
BEGIN
SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM to_date(p_hire_date))
INTO v_years_of_service FROM dual;
v_total_sal_by_years:=p_current_sal*v_years_of_service;
RETURN v_total_sal_by_years;
END;
/
-- Verifying
SELECT EMPLOYEE_ID, FIRST_NAME, TOTAL_EMP_SAL_BY_YEARS(HIRE_DATE, SALARY)AS TOTAL_
SALARY
FROM EMPLOYEES;

EMPLOYEE_ID FIRST_NAME TOTAL_SALARY
100         Steven     364000
101         Neena      204000
102         Lex        272000
103         Alexander  99000
104         Bruce      60000
105         David      57600
…
```

For more information, see [CREATE FUNCTION](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-FUNCTION.html#GUID-156AEDAC-ADD0-4E46-AA56-6D1F7CA63306) in the *Oracle documentation*.

## PostgreSQL usage


PostgreSQL supports the creation of user-defined functions using the `CREATE FUNCTION` statement. The PostgreSQL extended SQL language, PL/pgSQL, is the primary language to use while migrating from Oracle PL/SQL user-defined functions.

To create a function, a user needs the `USAGE` privilege on the language.

 **Examples** 

Convert the Oracle user-defined function from the previous Oracle section to a PostgreSQL PL/pgSQL function.

```
CREATE OR REPLACE FUNCTION total_emp_sal_by_years
(P_HIRE_DATE DATE, P_CURRENT_SAL NUMERIC)
RETURNS NUMERIC
AS
$BODY$
DECLARE
V_YEARS_OF_SERVICE NUMERIC;
V_TOTAL_SAL_BY_YEARS NUMERIC;
BEGIN
SELECT EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM (P_HIRE_DATE)) INTO V_YEARS_OF_SERVICE;
V_TOTAL_SAL_BY_YEARS:=P_CURRENT_SAL*V_YEARS_OF_SERVICE;
RETURN V_TOTAL_SAL_BY_YEARS;
END;
$BODY$
LANGUAGE PLPGSQL;

SELECT EMPLOYEE_ID, FIRST_NAME, TOTAL_EMP_SAL_BY_YEARS(HIRE_DATE, SALARY)AS TOTAL_SALARY
FROM EMPLOYEES;

employee_id  first_name  total_salary
100          Steven      364000.00
101          Neena       204000.00
102          Lex         272000.00
103          Alexander   99000.00
104          Bruce       60000.00
105          David       57600.00
106          Valli       52800.00
107          Diana       42000.00
…
```

For more information, see [User-Defined Functions](https://www.postgresql.org/docs/13/xfunc.html) and [CREATE FUNCTION](https://www.postgresql.org/docs/13/sql-createfunction.html) in the *PostgreSQL documentation*, and [What is the AWS Schema Conversion Tool?](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) in the *user guide*.

# Oracle UTL\$1FILE package


With AWS DMS, you can access data and read/write files on the server’s file system using the Oracle `UTL_FILE` package. The `UTL_FILE` package provides APIs to operate on server files, allowing applications to read and write operating system files.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[No compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-0.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  PostgreSQL doesn’t have the `UTL_FILE` equivalent.  | 

## Oracle usage


Oracle `UTL_FILE` PL/SQL package enables you to access files stored outside of the database such as files stored on the operating system, the database server, or a connected storage volume. `UTL_FILE.FOPEN`, `UTL_FILE.GET_LINE`, and `UTL_FILE.PUT_LINE` are procedures within the `UTL_FILE` package used to open, read, and write files.

 **Examples** 

Run an anonymous PL/SQL block that reads a single line from file1 and writes it to file2.
+ Use `UTL_FILE.FILE_TYPE` to create a handle for the file.
+ Use `UTL_FILE.FOPEN` to open streamable access to the file and specify:
  + The logical Oracle directory object pointing to the O/S folder where the file resides.
  + The file name.
  + The file access mode: 'A'=append mode, 'W'=write mode
+ Use `UTL_FILE.GET_LINE` to read a line from the input file into a variable.
+ Use `UTL_FILE.PUT_LINE` to write a single line to the output file.

```
DECLARE
strString1 VARCHAR2(32767);
fileFile1 UTL_FILE.FILE_TYPE;
BEGIN
fileFile1 := UTL_FILE.FOPEN('FILES_DIR','File1.tmp','R');
UTL_FILE.GET_LINE(fileFile1,strString1);
UTL_FILE.FCLOSE(fileFile1);
fileFile1 := UTL_FILE.FOPEN('FILES_DIR','File2.tmp','A');
utl_file.PUT_LINE(fileFile1,strString1);
utl_file.fclose(fileFile1);
END;
/
```

For more information, see [UTL\$1FILE](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/UTL_FILE.html#GUID-EBC42A36-EB72-4AA1-B75F-8CF4BC6E29B4) in the *Oracle documentation*.

## PostgreSQL usage


 Amazon Aurora PostgreSQL doesn’t currently provides a directly comparable alternative for Oracle `UTL_FILE` package.

# Oracle UTL\$1MAIL or UTL\$1SMTP and PostgreSQL Scheduled Lambda with Amazon SES


With AWS DMS, you can configure email notifications for migration tasks using Oracle `UTL_MAIL` or `UTL_SMTP` and PostgreSQL scheduled Lambda with Amazon Simple Email Service (Amazon SES). `UTL_MAIL` and `UTL_SMTP` are Oracle database packages that provide an interface to send emails, while scheduled Lambda with Amazon SES allows sending emails from a PostgreSQL database using AWS Lambda and Amazon SES.


| Feature compatibility |  AWS SCT / AWS DMS automation level |  AWS SCT action code index | Key differences | 
| --- | --- | --- | --- | 
|   ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png)   |   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |  N/A  |  Use Lambda integration.  | 

## Oracle UTL\$1MAIL usage


The Oracle `UTL_MAIL` package provides functionality for sending email messages. Unlike `UTL_SMTP`, which is more complex and provided in earlier versions of Oracle, `UTL_MAIL` supports attachments. For most cases, `UTL_MAIL` is a better choice.

 **Examples** 

Install the required mail packages.

```
@{ORACLE_HOME}/rdbms/admin/utlmail.sql
@{ORACLE_HOME}/rdbms/admin/prvtmail.plb
```

Set the smtp\$1out\$1server parameter.

```
ALTER SYSTEM SET smtp_out_server = 'smtp.domain.com' SCOPE=BOTH;
```

Send an email message.

```
exec utl_mail.send('Sender@mailserver.com', 'recipient@mailserver.com', NULL, NULL, 'This is the subject', 'This is the message body', NULL, 3, NULL);
```

For more information, see [UTL\$1MAIL](https://docs.oracle.com/database/121/ARPLS/u_mail.htm#ARPLS384) in the *Oracle documentation*.

## Oracle UTL\$1SMTP usage


The Oracle `UTL_SMTP` package provides functionality for sending email messages and is useful for sending alerts about database events. Unlike `UTL_MAIL`, UTL `SMTP` is more complex and doesn’t support attachments. For most cases, `UTL_MAIL` is a better choice.

 **Examples** 

The following example demonstrates using `UTL_SMTP` procedures to send email messages.

Install the required scripts.

```
In oracle 12c:
@{ORACLE_HOME}/rdbms/admin/utlsmtp.sql

In oracle 11g:
@{ORACLE_HOME}/javavm/install/initjvm.sql
@{ORACLE_HOME}/rdbms/admin/initplsj.sql
```

Create and send an email message.
+  `UTL_SMTP.OPEN_CONNECTION` opens a connection to the smtp server.
+  `UTL_SMTP.HELO` initiates a handshake with the smtp server.
+  `UTL_SMTP.MAIL` Initiates a mail transaction that obtains the senders details.
+  `UTL_SMTP.RCPT` adds a recipient to the mail transaction.
+  `UTL_SMTP.DATA` adds the message content.
+  `UTL_SMTP.QUIT` terminates the SMTP transaction.

```
DECLARE
smtpconn utl_smtp.connection;
BEGIN
smtpconn := UTL_SMTP.OPEN_CONNECTION('smtp.mailserver.com', 25);
UTL_SMTP.HELO(smtpconn, 'smtp.mailserver.com');
UTL_SMTP.MAIL(smtpconn, 'sender@mailserver.com');
UTL_SMTP.RCPT(smtpconn, 'recipient@mailserver.com');
UTL_SMTP.DATA(smtpconn,'Message body');
UTL_SMTP.QUIT(smtpconn);
END;
/
```

For more information, see [Managing Resources with Oracle Database Resource Manager](https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-resources-with-oracle-database-resource-manager.html#GUID-2BEF5482-CF97-4A85-BD90-9195E41E74EF) in the *Oracle documentation*.

## PostgreSQL usage


 Amazon Aurora PostgreSQL doesn’t provide native support for sending email message from the database. For alerting purposes, use the Event Notification Subscription feature to send email notifications to operators.

The only way to send Email from the database is to use the AWS Lambda integration. For more information, see [AWS Lambda](https://aws.amazon.com/lambda).

 **Examples** 

Sending an Email from Aurora PostgreSQL using Lambda integration.

First, configure [Amazon Simple Email Service (Amazon SES)](https://docs.aws.amazon.com/ses/latest/dg/Welcome.html).

In the AWS console, choose **SES**, **SMTP Settings**, and choose **Create My SMTP Credentials**. Note the SMTP server name; you will use it in the Lambda function.

Enter a name for IAM User Name (SMTP user) and choose **Create**.

Note the credentials; you will use them to authenticate with the SMTP server.

**Note**  
After you leave this page, you can’t retrieve the credentials.

On the SES page, choose **Email addresses** on the left, and choose **Verify a new email address**. Before sending email, they must be verified.

The next page indicates that the email is pending verification.

After you verified the email, create a table to store messages to be sent by the Lambda fuction.

```
CREATE TABLE emails (title varchar(600), body varchar(600), recipients varchar(600));
```

To create the Lambda function, navigate to the [Lambda page](https://console.aws.amazon.com/lambda/home) in the AWS Console, and choose **Create function**.

Choose **Author from scratch**, enter a name for your project, and select Python 2.7 as the runtime. Make sure that you use a role with the correct permissions. Choose **Create function**.

Download this [GitHub project](https://github.com/alexcasalboni/awslambda-psycopg2).

In your local environment, create two files: main.py and db\$1util.py. Cut and paste the following content into `main.py` and `db_util.py` respectively. Replace the placeholders in the code with values for your environment.

main.py:

```
#!/usr/bin/python
import sys
import logging
import psycopg2

from db_util import make_conn, fetch_data
def lambda_handler(event, context):
  query_cmd = "select * from mails"
  print query_cmd

  # get a connection, if a connect can't be made an exception will be raised here
  conn = make_conn()

  result = fetch_data(conn, query_cmd)
  conn.close()

  return result
```

db\$1util.py:

```
#!/usr/bin/python
import psycopg2
import smtplib
import email.utils
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

db_host = 'YOUR_RDS_HOST'
db_port = 'YOUR_RDS_PORT'
db_name = 'YOUR_RDS_DBNAME'
db_user = 'YOUR_RDS_USER'
db_pass = 'YOUR_RDS_PASSWORD'

def sendEmail(recp, sub, message):
  # Replace sender@example.com with your "From" address.
  # This address must be verified.
  SENDER = 'PUT HERE THE VERIFIED EMAIL'
  SENDERNAME = 'Lambda

  # Replace recipient@example.com with a "To" address. If your account
  # is still in the sandbox, this address must be verified.
  RECIPIENT = recp

  # Replace smtp_username with your Amazon SES SMTP user name.
  USERNAME_SMTP = "YOUR_SMTP_USERNAME"

  # Replace smtp_password with your Amazon SES SMTP password.
  PASSWORD_SMTP = "YOUR_SMTP PASSWORD"

  # (Optional) the name of a configuration set to use for this message.
  # If you comment out this line, you also need to remove or comment out
  # the "X-SES-CONFIGURATION-SET:" header below.
  CONFIGURATION_SET = "ConfigSet"

  # If you're using Amazon SES in a region other than US West (Oregon),
  # replace email-smtp.us-west-2.amazonaws.com with the Amazon SES SMTP
  # endpoint in the appropriate region.
  HOST = "YOUR_SMTP_SERVERNAME"
  PORT = 587

  # The subject line of the email.
  SUBJECT = sub

  # The email body for recipients with non-HTML email clients.
  BODY_TEXT = ("Amazon SES Test\r\n"
    "This email was sent through the Amazon SES SMTP "
    "Interface using the Python smtplib package."
    )

  # The HTML body of the email.
  BODY_HTML = """<html>
  <head></head>
  <body>
  <h1>Amazon SES SMTP Email Test</h1>""" + message + """</body>
  </html>
    """

  # Create message container - the correct MIME type is multipart/alternative.
  msg = MIMEMultipart('alternative')
  msg['Subject'] = SUBJECT
  msg['From'] = email.utils.formataddr((SENDERNAME, SENDER))
  msg['To'] = RECIPIENT
  # Comment or delete the next line if you aren't using a configuration set
  #msg.add_header('X-SES-CONFIGURATION-SET',CONFIGURATION_SET)

  # Record the MIME types of both parts - text/plain and text/html.
  part1 = MIMEText(BODY_TEXT, 'plain')
  part2 = MIMEText(BODY_HTML, 'html')

  # Attach parts into message container.
  # According to RFC 2046, the last part of a multipart message, in this case
  # the HTML message, is best and preferred.
  msg.attach(part1)
  msg.attach(part2)

  # Try to send the message.
  try:
    server = smtplib.SMTP(HOST, PORT)
    server.ehlo()
    server.starttls()
    #stmplib docs recommend calling ehlo() before & after starttls()
    server.ehlo()
    server.login(USERNAME_SMTP, PASSWORD_SMTP)
    server.sendmail(SENDER, RECIPIENT, msg.as_string())
    server.close()
  # Display an error message if something goes wrong.
  except Exception as e:
    print ("Error: ", e)
  else:
    print ("Email sent!")

  def make_conn():
    conn = None
    try:
      conn = psycopg2.connect("dbname='%s' user='%s' host='%s' password='%s'" %
      (db_name, db_user, db_host, db_pass))
    except:
      print "I am unable to connect to the database"
    return conn

  def fetch_data(conn, query):
    result = []
    print "Now running: %s" % (query)
    cursor = conn.cursor()
    cursor.execute(query)

    print("Number of new mails to be sent: ", cursor.rowcount)

    raw = cursor.fetchall()

    for line in raw:
      print(line[0])
      sendEmail(line[2],line[0],line[1])
      result.append(line)

    cursor.execute('delete from mails')
    cursor.execute('commit')

    return result
```

**Note**  
In the body of db\$1util.py, Lambda deletes the content of the mails table.

Place the `main.py` and `db_util.py` files inside the Github extracted folder and create a new zipfile that includes your two new files.

Return to your Lambda project and change the **Code entry type** to **Upload a .ZIP file**, change the **Handler** to **mail.lambda\$1handler**, and upload the file. Then choose **Save**.

To test the Lambda function, choose **Test** and enter the **Event name**.

**Note**  
The Lambda function can be triggered by multiple options. This walkthrough demonstrates how to schedule it to run every minute. Remember, you are paying for each Lambda execution.

To create a scheduled trigger, use Amazon CloudWatch, enter all details, and choose **Add**.

**Note**  
This example runs every minute, but you can use a different interval. For more information, see [Schedule expressions using rate or cron](https://docs.aws.amazon.com/lambda/latest/dg/tutorial-scheduled-events-schedule-expressions.html).

Choose **Save**.