Listen Software’s How To: Dynamic SQL


The DBMS_SQL package can be helpful for executing dynamic SQL.
Dynamic SQL can be either a DML (Dynamic Manipulation Language: select,insert,update,delete) or DDL (Data Definition
Language: create table, create index, or create view).

01> FUNCTION EXEC_DYNAMIC_SQL_DML(statement IN VARCHAR2)
02> RETURN BOOLEAN IS
03> return_val BOOLEAN;
04> sql_ret integer;
05> ddl_cursor integer;
06> errormsg varchar2(255);
07> BEGIN
08> return_val := TRUE;
09> BEGIN
10> ddl_cursor := dbms_sql.open_cursor;
11> dbms_sql.parse(ddl_cursor,statement,dbms_sql.v7);
12> sql_ret := dbms_sql.execute(ddl_cursor);
13> dbms_sql.close_cursor(ddl_cursor);
14> EXCEPTION
15> /* Error Messages. */
16> when others then
17> return_val := FALSE;
18> errormsg := sqlerrm;
19> dbms_output.put_line(sqlerrm);
20> if dbms_sql.is_open(ddl_cursor) then
21> dbms_sql.close_cursor(ddl_cursor);
22> end if;
23> end;
24> return return_val;
25> END EXEC_DYNAMIC_SQL_DML;

Line 10 – Opens the dynamic cursor

Line 11 – Parses the sql statement the data
dictionary for object permission and correctness of syntax

Line 12 – Executes a single row SQL,
such as insert, update, or delete

Line 13 – Closes the dynamic cursor

If an error occurs, Line 19 outputs the error message to the
SQL*PLUS environment.

For the output
of the DBMS_OUTPUT package to be displayed in
SQL*PLUS, the SERVEROUTPUT
system variable must be set to ON.

The DBMS_SQL package can also used to fetch records:

01> PROCEDURE EXEC_DYNAMIC_SELECT(p_a_number in number, p_a_character_field1 in varchar2,
p_a_character_field2 in varchar2) IS
02> fNumber_Buffer number(10);
03> fCharacter_Buffer1 varchar(6);
04> fCharacter_Buffer2 varchar(7);
05> sSQL varchar2(2000);
06> wRC integer;
07> BEGIN
08> sSQL := ‘select NUMBER_FIELD, CHARACTER_FIELD_1, CHARACTER_FIELD_2’ ||
09> ‘ from my_table where ‘ ||
10> ‘ A_NUMBER_FIELD = :TOKEN_A_NUMBER_FIELD’ ||
11> ‘ and A_CHARACTER_FIELD1 = :TOKEN_A_CHARACTER_FIELD1’ ||
12> ‘ and A_CHARACTER_FIELD2 = :TOKEN_A_CHARACTER_FIELD2′;
13> dbms_sql.parse(source_cursor,sSQL,dbms_sql.v7);
14> dbms_sql.define_column(source_cursor,1,fNumber);
15> dbms_sql.define_column(source_cursor,2,fCharacter_Buffer,6);
16> dbms_sql.define_column(source_cursor,3,fCharacter_Buffer2,7);
17> dbms_sql.bind_variable(source_cursor,’:TOKEN_A_NUMBER_FIELD’,p_a_number);
18> dbms_sql.bind_variable(source_cursor,’:TOKEN_A_CHARACTER_FIELD1′,p_a_character_field1);
19> dbms_sql.bind_variable(source_cursor,’:TOKEN_A_CHARACTER_FIELD2′,p_a_character_field2);
20> wRC := dbms_sql.execute(source_cursor);
21> loop
22> if dbms_sql.fetch_rows(source_cursor)>0 then
23> dbms_sql.column_value(source_cursor,1,fNumber_Buffer);
24> dbms_sql.column_value(source_cursor,2,fCharacter_Buffer1);
25> dbms_sql.column_value(source_cursor,3,fCharacter_Buffer2);…
26> else
27> exit;
28> end if;
29> end loop;
30> END EXEC_DYNAMIC_SQL_SELECT;

Lines 8-12 – When you bind a value, the SQL string does not contain the value, only a place holder: TOKEN_A_NUMBER_FIELD,
TOKEN_A_CHARACTER_FIELD1, TOKEN_A_CHARACTER_FIELD2

The place holders allow different values to be bound
to the SQL in the
System Global Area (SGA), reducing parsing time and
execution time.

In the sample procedure above, parameters are bound
to the SQL place holders.

Line 13 – Parse the SQL

Lines 14-16 – Define the columns dimensions for
data being return by the cursor for fields: (NUMBER_FIELD,
CHARACTER_FIELD_1,
CHARACTER_FIELD_2.

Assign an ordinal position number to the data
columns

1->NUMBER_FIELD

2->CHARACTER_FIELD_1

3->CHARACTER_FIELD_2

Lines 17-19 – Bind values being passed into the
Procedure to the place holder values

Line 20 – Execute the SQL

Line 22 – Fetch records from the cursor until no
more records

Lines 23-25 – Extract data into variables by ordinal position for
use in code

Back to the LSS “How To” Series Main Page

Latest Articles