Oracle Illustrated

Feb 21 2010   7:11AM GMT

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



Posted by: Lakshmi Venkatesh
Tags:

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′)

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: