Migrating from 9i to 11g – Dynamic SQL for PLSQL – Functional completeness

Lets see the functional completeness of Dynamic SQL for PLSQL in this article.

It allows us to write dynamic SQL statements larger than 32KB. Now, DBMS_SQL.PARSE function is overloaded with CLOB data type. Now a REF cursor can be converted into a DBMS_SQL cursor.

DBMS_SQL – to execute dynamic SQL statements that has unknown in / out variables – similar to Method 4 in pro*c. When we do not know the columns a select statement would return / the data type then DBMS_SQL is the best way to go.

Native dynamic SQL – Available since Oracle 8i and enables to perform dynamic SQL. This enables to retrieve the records – variables should be known at compile time. We can use the cursor attributes like %ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT.

Oracle 11g
CREATE OR REPLACE PROCEDURE native_dyn_sql
(in_source_code IN CLOB) AS
BEGIN
EXECUTE IMMEDIATE in_source_code;
dbms_output.put_line(‘The value of source code is ‘ || in_source_code);
END native_dyn_sql;
/

exec native_dyn_sql (‘begin dbms_output.put_line(”hello how are you !!”); end;’)

SQL> exec native_dyn_sql (‘begin dbms_output.put_line(”hello how are you !!”);
end;’)

hello how are you !!
The value of source code is begin dbms_output.put_line(‘hello how are you !!’);
end;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
SQL>

Amazing !!

Now, it is possible to switch between DBMS_SQL to Native dynamic SQL – rocking !!which was not possible prior to Oracle 11g. This can be achieved by DBMS_SQL.TO_REF_CURSOR and DBMS_SQL.TO_CURSOR_NUMBER.

Oracle 11g
Create or replace procedure convert_native_dbms
AS
TYPE native_cursor IS REF CURSOR;
native_cursor_tab native_cursor;
desc_vars dbms_sql.desc_tab;
cursor_handle NUMBER;
cursor_return NUMBER;

BEGIN
cursor_handle := dbms_sql.open_cursor;
dbms_sql.parse(cursor_handle, ‘select empno from emp where rownum exec convert_dbms_native;
The value of empno_Tab is – 1
The value of empno_Tab is – 2
The value of empno_Tab is – 3
The value of empno_Tab is – 4
The value of empno_Tab is – 5
The value of empno_Tab is – 6
The value of empno_Tab is – 7
The value of empno_Tab is – 8
The value of empno_Tab is – 9

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.42

Also, it is equally possible to transform REF CURSOR into DBMS_SQL cursor – this can be achieved using DBMS_SQL.TO_CURSOR_NUMBER.

Oracle 11g
Create or replace procedure convert_dbms_native( in_cursor IN VARCHAR2)
AS
TYPE native_cursor IS REF CURSOR;
native_cursor_tab native_cursor;
TYPE empno_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
empno_tab empno_list;
cursor_handle NUMBER;
cursor_return NUMBER;
v_emp_no NUMBER;
v_num_columns number;
v_describe dbms_sql.desc_tab;
BEGIN
OPEN native_cursor_tab FOR in_cursor;
cursor_handle := DBMS_SQL.TO_CURSOR_NUMBER(native_cursor_tab);
dbms_sql.describe_columns(cursor_handle, v_num_columns, v_describe);

FOR i in 1 .. v_num_columns LOOP
if v_describe(i).col_type = 1 THEN
dbms_sql.define_column(cursor_handle, i, v_emp_no);
END IF;
END LOOP;

WHILE DBMS_SQL.FETCH_ROWS(cursor_handle) > 0 LOOP
FOR i in 1 .. v_num_columns LOOP
if v_describe(i).col_type = 1 then
dbms_sql.column_value (cursor_handle, i, v_emp_no);
dbms_output.put_line(‘The value of empno is – ‘ || v_emp_no);
end if;
END LOOP;
END LOOP;
dbms_sql.close_cursor(cursor_handle);
END;
/

exec convert_dbms_native(‘select empno from emp where rownum <= 10′)