Listen Software's How To: Dynamic SQL

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;

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