Migrating Oracle Cursors to YugabyteDB

Oracle developers often rely on explicit cursors to loop through result sets or to parameterize queries inside PL/SQL procedures. When migrating to YugabyteDB (or PostgreSQL), the syntax and behavior of cursors changes just enough to cause confusion.

Today’s tip shows side-by-side examples so you can quickly adapt your Oracle cursor code to YugabyteDB’s PL/pgSQL.

Declaring and Opening Cursors

Oracle

				
					CURSOR c1(p_tab VARCHAR2) IS
  SELECT partition_name, high_value
  FROM user_tab_partitions
  WHERE table_name = p_tab;

IF l_cnt > 0 THEN
  OPEN c1(l_tab);
END IF;

YugabyteDB / Postgres

Postgres doesn’t have parameterized cursor declarations. Instead you declare a REFCURSOR variable and pass parameters directly at OPEN time.

				
					c1 REFCURSOR;

IF l_cnt > 0 THEN
  OPEN c1 FOR
    SELECT partition_name, high_value
    FROM user_tab_partitions
    WHERE table_name = l_tab;
END IF;

Fetching Rows

Oracle

				
					LOOP
  FETCH c1 INTO l_part, l_high_value;
  EXIT WHEN c1%NOTFOUND;
  -- process row
END LOOP;

YugabyteDB / Postgres

				
					LOOP
  FETCH c1 INTO l_part, l_high_value;
  EXIT WHEN NOT FOUND;  -- no need for c1%NOTFOUND
  -- process row
END LOOP;

Or, the cleaner Postgres idiom:

				
					FOR l_part, l_high_value IN
  SELECT partition_name, high_value
  FROM user_tab_partitions
  WHERE table_name = l_tab
LOOP
  -- process row
END LOOP;

Closing Cursors

Oracle

				
					CLOSE c1;

YugabyteDB / Postgres

				
					CLOSE c1;

Same syntax. Easy win. 😂

Dynamic SQL with Cursors

Sometimes you want to build the query dynamically.

Oracle

				
					EXECUTE IMMEDIATE
  'SELECT count(*) FROM user_tab_partitions
   WHERE table_name = ''' || l_tab || ''''
INTO l_cnt;

YugabyteDB / Postgres

				
					EXECUTE format(
  'SELECT count(*) FROM user_tab_partitions WHERE table_name = %L',
  l_tab
) INTO l_cnt;

Key points:

● Use format() with %L to safely quote variables.
● Use INTO STRICT if you expect exactly one row (Oracle default).

Example Usage: Partition DDL and Cursors

It’s common in Oracle to loop over partitions and drop them dynamically. Here’s how that looks in Oracle vs. YugabyteDB/Postgres.

Oracle

				
					CURSOR c1(p_tab VARCHAR2) IS
  SELECT partition_name
  FROM user_tab_partitions
  WHERE table_name = p_tab;

BEGIN
  FOR r IN c1(l_tab) LOOP
    l_sql := 'ALTER TABLE ' || l_tab || ' DROP PARTITION ' || r.partition_name;
    EXECUTE IMMEDIATE l_sql;
  END LOOP;
END;

YugabyteDB / Postgres

Remember: in YSQL, partitions are just child tables attached under a parent. Dropping a partition = dropping the child table.

				
					DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT relname AS partition_name
    FROM pg_class c
    JOIN pg_inherits i ON c.oid = i.inhrelid
    JOIN pg_class p ON p.oid = i.inhparent
    WHERE p.relname = l_tab
  LOOP
    -- Construct DROP TABLE for the child table
    l_sql := format('DROP TABLE %I', r.partition_name);
    EXECUTE l_sql;
  END LOOP;
END;

Summary

● Replace c1%NOTFOUND with NOT FOUND in loops.
● Replace parameterized cursors with REFCURSOR + OPEN … FOR.
● Prefer FOR … IN SELECT loops for simplicity.
● Use format() with %L or %I for safe dynamic SQL.

Pro Tip 🎯

If you’re migrating a lot of cursor-heavy code, start by rewriting everything into FOR … IN SELECT loops. They’re the most natural and efficient style in Postgres/YugabyteDB, and they save you from subtle cursor management mistakes.

Have Fun!