Oracle DBMS_SQL package and PostgreSQL dynamic execution - Oracle to Aurora PostgreSQL Migration Playbook
Oracle usagePostgreSQL usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle DBMS_SQL package and PostgreSQL dynamic execution

With Amazon 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 Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

One star feature compatibility

One star automation level

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.

  2. Use DBMS_SQL.PARSE to parse the statement into the referenced cursor.

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

  4. Use DBMS_SQL.EXECUTE to run the cursor.

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

  6. 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*Plus).

  • 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_SQL, 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_SQL procedures.

For more information, see DBMS_SQL 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, PREPARE, and Executing Dynamic Commands in the PostgreSQL documentation.