Home > Documentation > Oracle to PostgreSQL

Cursors

Both Oracle and PostgreSQL use cursors to encapsulate a query and process every individual row. However, there are minor syntax differences that must be handled properly during migration from Oracle to PostgreSQL:

• %FOUND and %NOTFOUND are used to find whether an SQL statement affected one or more rows in Oracle PL/SQL. It must be converted into FOUND and NOT FOUND in PostgreSQL code.
• %ROWCOUNT element represents number of rows processed by an SQL statement in Oracle PL/SQL. PostgreSQL provides equivalent ROW_COUNT for the same purpose.
• REF CURSOR is Oracle data type that represents the cursor, every cursor variable must be defined with a type derived from REF CURSOR. PostgreSQL provides equivalent REFCURSOR for the same purpose.

Oracle allows to iterate rows in cursor using the following cycle:

FOR record_variable IN cursor_variable 
LOOP
    ...
END LOOP;

In PostgreSQL the following construction may be used for the same purpose:

LOOP
    FETCH FROM cursor_variable INTO record_variable;
    EXIT WHEN NOT FOUND;
    ...
END LOOP;

Have questions? Contact us