dcsimg

Listen Software's How To: Dynamic SQL

September 23, 2002


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









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers