Migrating Cursors from Oracle to PostgreSQL

A cursor is a mechanism by which you can assign a name to a SELECT statement and manipulate the information within that SQL statement. A cursor is a pointer to the result set of a query. Using cursors, it is possible to read few rows from the result set. The main purpose of doing this is to avoid memory consumption of database server if the result of the query contains huge number of rows.

This article explains cursors basics and things that must be taken care during Oracle cursors migration to PostgreSQL and discusses on the implications if not handled properly.

OraclePostgreSQL
Syntax:
CURSOR your_cursor_name(arguments) IS your_Select_query;

Example:
CURSOR c_customers IS
SELECT id, name, address FROM customers;

Syntax:
your_cursor_name CURSOR (arguments) FOR your_Select_query;

Example:
c_customers CURSOR FOR
SELECT id, name, address FROM customers;

Using STRICT in PostgreSQL

A SELECT statement in PL/SQL returns NO_DATA_FOUND exception when no rows are returned.
SELECT statement in PLpgsql does not return NO_DATA_FOUND exception by default. You must use STRICT keyword if the SELECT statement must throw NO_DATA_FOUND exception.

OraclePostgreSQL
set serveroutput on;
declare
v_num integer;
begin
select 1 col into v_num from dual where 1 = 0;
dbms_output.put_line(‘v_num: ‘|| v_num);
end;
/
–No exception is raised
set client_min_messages = debug;
declare
v_num integer;
begin
select 1 col into v_num where 1 = 0;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
end $$;

–Throws exception similar to Oracle
set client_min_messages = debug;
declare
v_num integer;
begin
select 1 col into STRICT v_num where 1 = 0;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
end $$;

Do Cursors raise NO_DATA_FOUND exception?

Cursors do not raise NO_DATA_FOUND exception, if the query does not return any data. This is same in both Oracle and PostgreSQL.

OraclePostgreSQL
set serveroutput on;
declare
cursor cur1 is select 1 col from dual where 1 = 0;
v_num integer;
begin
open cur1;
fetch cur1 into v_num;
dbms_output.put_line(‘v_num: ‘|| v_num);
close cur1;
end;
/

v_num:

PL/SQL procedure successfully completed.

set client_min_messages = debug;
do $$
declare
cur1 cursor for select 1 col where 1 = 0;
v_num integer;
begin
open cur1;
fetch cur1 into v_num;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
close cur1;
end $$;

DEBUG: v_num:
DO

What needs to be taken care during migrating Cursors?

Let’s understand working with cursors with an example.

OraclePostgreSQL
set serveroutput on;
declare
cursor cur1 is select 1 col from dual where 1 = 1;
v_num integer;
begin
open cur1;
fetch cur1 into v_num;
dbms_output.put_line(‘v_num: ‘|| v_num);
close cur1;
end;
/

v_num: 1

PL/SQL procedure successfully completed.

set client_min_messages = debug;
do $$
declare
cur1 cursor for select 1 col where 1 = 1;
v_num integer;
begin
open cur1;
fetch cur1 into v_num;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
close cur1;
end $$;

DEBUG: v_num: 1
DO

Cursors in PostgreSQL resembles cursors of Oracle in many ways syntactically or semantically.
Though there are similarities in many scenarios, differences can be observed with the way FETCH is evaluated.

  • No syntax error in Postgres when INTO list of a FETCH does not match cursor declaration
  • Default value is overridden after FETCH in Postgres
  • Cursor FOR loop and EXIT

No syntax error in Postgres when INTO list in a FETCH does not match cursor declaration

OraclePostgreSQL
set serveroutput on;
declare
cursor cur_test is select * FROM test_cur_found where id = 2;
v_name varchar2(100);
begin
open cur_test;
fetch cur_test into v_name;
close cur_test;
dbms_output.put_line(‘v_name: ‘|| v_name);
end;
/

fetch cur_test into v_name;
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00394: wrong number of values in the INTO list of a FETCH statement
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored

set client_min_messages = debug;
do $$
declare
cur_test cursor for select * FROM test_cur_found where id = 2;
v_name varchar(100);
begin
open cur_test;
fetch cur_test into v_name;
close cur_test;
raise notice using message := concat_ws(”, ‘v_name = ‘, v_name);
end $$;

Default value is overridden after FETCH in Postgres

Here is a simple example to demonstrate how Oracle preserves the old value even if there is no data found in fetch cursor. However, in PG, if there is no data through cursor FETCH then variable default value is overridden and becomes NULL. If this is not handled properly, could seriously impact database as it might data within database.

OraclePostgreSQL
set serveroutput on;
declare
cursor cur1 is select 1 col from dual where 1 = 0;
v_num integer := 100;
begin
open cur1;
fetch cur1 into v_num;
dbms_output.put_line(‘v_num: ‘|| v_num);
close cur1;
end;
/

v_num: 100

PL/SQL procedure successfully completed.

set client_min_messages = debug;
do $$
declare
cur1 cursor for select 1 col where 1 = 0;
v_num integer := 100;
begin
open cur1;
fetch cur1 into v_num;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
close cur1;
end $$;

DEBUG: v_num:
DO

–Correct way
set client_min_messages = debug;
do $$
declare
cur1 cursor for select 1 col where 1 = 0;
v_num integer := 100;
v_temp integer;
begin
open cur1;
fetch cur1 into v_temp;
if found then
v_num := v_temp;
end if;
raise debug using message := concat_ws(”, ‘v_num: ‘, v_num);
close cur1;
end $$;

DEBUG: v_num: 100
DO

From the above you must have understood the executional difference.

The following code uses one extra variable to store cursor value temporarily and assigns value to the actual variable only if there is a successful fetch through IF FOUND condition checking. FOUND, which is of type boolean. FOUND starts out false within each PL/pgSQL function call. It is set by each of the following types of statements:

  • A SELECT INTO statement sets FOUND true if a row is assigned, false if no row is returned.
  • A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced.
  • UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.
  • A FETCH statement sets FOUND true if it returns a row, false if no row is returned.
  • A FOR statement sets FOUND true if it iterates one or more times, else false.

Cursor FOR loop and EXIT

OraclePostgreSQL
create table test_cur_found(
id number,
tname varchar2(10),
tdate date );

insert into test_cur_found values(1, ‘test-1’, sysdate);
insert into test_cur_found values(2, ‘test-2’, sysdate);
insert into test_cur_found values(3, ‘test-3’, sysdate);
insert into test_cur_found values(4, ‘test-4’, sysdate);
insert into test_cur_found values(4, ‘test-5’, sysdate);

SQL> select * from test_cur_found;

ID TNAME TDATE
———- ———- ———
1 test-1 19-SEP-21
2 test-2 19-SEP-21
3 test-3 19-SEP-21
4 test-4 19-SEP-21
4 test-5 19-SEP-21

set serveroutput on;
declare
cursor cur_test is select tname FROM test_cur_found where id = 4;
v_name varchar2(10);
begin
open cur_test;
loop
fetch cur_test into v_name;
exit when cur_test%notfound;
end loop;
close cur_test;
dbms_output.put_line(‘v_name: ‘|| v_name);
end;
/

v_name: test-5

PL/SQL procedure successfully completed.

create table test_cur_found(
id integer,
tname varchar(10),
tdate date );

insert into test_cur_found values(1, ‘test-1’, current_date);
insert into test_cur_found values(2, ‘test-2’, current_date);
insert into test_cur_found values(3, ‘test-3’, current_date);
insert into test_cur_found values(4, ‘test-4’, current_date);
insert into test_cur_found values(4, ‘test-5’, current_date);

pg=> select * from test_cur_found;
id | tname | tdate
—-+——–+————
1 | test-1 | 2021-09-19
2 | test-2 | 2021-09-19
3 | test-3 | 2021-09-19
4 | test-4 | 2021-09-19
4 | test-5 | 2021-09-19
(5 rows)

set client_min_messages = debug;
do $$
declare
cur_test cursor for select tname FROM test_cur_found where id = 4;
v_name varchar(10);
begin
open cur_test;
loop
fetch cur_test into v_name;
exit when not found;
end loop;
close cur_test;
raise notice using message := concat_ws(”, ‘v_name = ‘, v_name);
end $$;

NOTICE: v_name =
DO

–Correct way
set client_min_messages = debug;
do $$
declare
cur_test cursor for select tname FROM test_cur_found where id = 4;
v_name varchar(10);
v_rec RECORD;
begin
open cur_test;
loop
fetch cur_test into v_rec;
if found then
v_name := v_rec.tname;
end if;
exit when not found;
end loop;
close cur_test;
raise notice using message := concat_ws(”, ‘v_name = ‘, v_name);
end $$;

NOTICE: v_name = test-5
DO

DML/SELECT after FETCH

OraclePostgreSQL
set serveroutput on;
declare
cursor cur_test is select * from test_cur_found;
rec_test test_cur_found%rowtype;
res integer := 0;
begin
open cur_test;
loop
fetch cur_test into rec_test;
select count(*) into res from test_cur_found;
exit when cur_test%notfound;
end loop;
close cur_test;
dbms_output.put_line(‘res = ‘|| res);
end;
/

res = 5

PL/SQL procedure successfully completed.

–hangs, infinite loop
set client_min_messages = debug;
do $$
declare
cur_test cursor for select * from test_cur_found;
rec_test test_cur_found%rowtype;
res integer := 0;
begin
open cur_test;
loop
fetch cur_test into rec_test;
select count(*) into res from test_cur_found;
exit when not found;
end loop;
close cur_test;
raise debug using message := concat_ws(”, ‘res = ‘, res);
end $$;

Hangs…

–Verify infinite loop
set client_min_messages = debug;
do $$
declare
cur_test cursor for select * from test_cur_found;
rec_test test_cur_found%rowtype;
res integer := 0;
begin
open cur_test;
loop
fetch cur_test into rec_test;
select count(*) into res from test_cur_found;
raise debug using message := concat_ws(”, ‘inside loop – found: % ‘, found);
exit when not found;
end loop;
close cur_test;
raise debug using message := concat_ws(”, ‘res = ‘, res);
end $$;

DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
DEBUG: inside loop – found: % t
.
.
Continued…

–Correct Approach
set client_min_messages = debug;
do $$
declare
cur_test cursor for select * from test_cur_found;
rec_test test_cur_found%rowtype;
res integer := 0;
v_flg boolean := false;
begin
open cur_test;
loop
fetch cur_test into rec_test;
if not found then
v_flg := true;
end if;
–res := res +1;
select count(*) into res from test_cur_found;
exit when v_flg;
end loop;
raise notice using message := concat_ws(”, ‘res = ‘, res);
end $$;

NOTICE: res = 5
DO

Summary

  1. Cursor in Postgres does not throw error when the cursor select statement’s column list does not match the INTO list in FETCH statement.
  2. Cursor FETCH overrides the variable value whether FETCH is found or not found.
  3. Always declare another third variable to hold value temporarily from FETCH and assign to local variable using IF FOUND THEN .. construct.

In this blog, we have tried to explain on different facets you must be aware of during migrating cursors from Oracle to PostgreSQL and be aware on scenarios which might often be neglected.
Enjoy reading and we would love your feedback.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments