PL/SQL Advanced Programming
It is possible to create dynamic SQL on the fly, you have two architectures that apply in both cases, you can glue strings together or you can implement placeholders. The gluing of strings is susceptible to SQL injection attacks, implementing placeholders (bind variables) makes your dynamic SQL immune to these attacks. They act as formal parameters to dynamic statements.
The process of running a dynamic statement involves four steps:
There are two methods that can be used to build dynamic statements:
Native Dynamic SQL (NDS) |
NDS is a power and easy to use tool, it can be used to glue strings together or use input bind variables. |
DBMS_SQL Package |
It gives you a way to store object code in the database that would build dynamically build SQL statements. |
Native Dynamic SQL (NDS)
A standalone script generally checks something before acting, it could check a table exists before deleting it. This type of program uses the method 1 approach from the dbms_sql package (see dbms_sql methods of operation table)
standalone | begin for i in ( select null from user_objects where object_name = 'TEST_SEQ') loop -- Very simple NDS command execute immediate 'drop sequence test_seq'; dbms_output.put_line('Dropped [test_seq]'); end loop; end; / |
The next example shows how gluing strings together to make the statement can be achieved, this could cause security issues as it is venerable to SQL injection attacks.
Dynamic DML statement | CREATE OR REPLACE PROCEDURE insert_item ( table_name VARCHAR2 , asin VARCHAR2 , item_type VARCHAR2 , item_title VARCHAR2 , item_subtitle VARCHAR2 := '' , rating VARCHAR2 , agency VARCHAR2 , release_date VARCHAR2 ) IS stmt VARCHAR2(2000); BEGIN stmt := 'INSERT INTO '||dbms_assert.simple_sql_name(table_name)||' VALUES ' || '( item_s1.nextval ' || ','||dbms_assert.enquote_literal('ASIN'||CHR(58)||asin) || ',(SELECT common_lookup_id ' || ' FROM common_lookup ' || ' WHERE common_lookup_type = ' || dbms_assert.enquote_literal(item_type)||')' || ','||dbms_assert.enquote_literal(item_title) || ','||dbms_assert.enquote_literal(item_subtitle) || ', empty_clob() ' || ', NULL ' || ','||dbms_assert.enquote_literal(rating) || ','||dbms_assert.enquote_literal(agency) || ','||dbms_assert.enquote_literal(release_date) || ', 3, SYSDATE, 3, SYSDATE)'; dbms_output.put_line(stmt); EXECUTE IMMEDIATE stmt; END insert_item; / -- To test the above |
SQL injection attacks are attempts to fake entry by using unbalanced quotes in SQL statements, dynamic SQL is a place where some hacker might try to exploit your code.
The next example demostrates dynamic statements with inputs, this lets you write statements with placeholders (bind variables). You actually pass the parameters into the statement by placing them as arguements to the using clause, you return values through the returning into clause. The parameters passed are by default are IN mode (pass-by_value) but can be IN OUT mode (pass-by-reference). There are some recommendations when using placeholder variables
Dynamic statement with inputs | CREATE OR REPLACE PROCEDURE insert_item ( asin VARCHAR2 , item_type VARCHAR2 , item_title VARCHAR2 , item_subtitle VARCHAR2 := '' , rating VARCHAR2 , agency VARCHAR2 , release_date DATE ) IS stmt VARCHAR2(2000); BEGIN stmt := 'INSERT INTO item VALUES ' || '( item_s1.nextval ' || ',''ASIN''||CHR(58)||:asin ' || ',(SELECT common_lookup_id ' || ' FROM common_lookup ' || ' WHERE common_lookup_type = :item_type)' || ', :item_title ' || ', :item_subtitle ' || ', empty_clob() ' || ', NULL ' || ', :rating ' || ', :agency ' || ', :release_date ' || ', 3, SYSDATE, 3, SYSDATE)'; dbms_output.put_line(stmt); EXECUTE IMMEDIATE stmt USING asin, item_type, item_title, item_subtitle, rating, agency, release_date; END insert_item; / |
The next NDS example demonstrates a dynamic statement with inputs and outputs.
Dynamic statement inputs and outputs | -- Example 1 |
The final NDS statement uses a dynamic statement with an unknown number of inputs. This type of program uses the method 4 approach from the dbms_sql package (see dbms_sql methods of operation table)
Dynamic statement unknow number of inputs | DECLARE -- Declare explicit record structure and table of structure. TYPE title_record IS RECORD ( item_title VARCHAR2(60), item_subtitle VARCHAR2(60) |
DBMS_SQL Package
DBMS_SQL gives you a way to store object code in the database that would dynamically build SQL statements, it also can support collections.
Where possible you should now use the NDS solution, DBMS_SQL has one major feature NDS does not, is that it can manage dynamic statements when the number and datatypes of columns returns are unknown before runtime, this is possible because of two procedures describe_columns and describe_coloumns2. DBMS_SQL supports both string concatenation and bind variables but requires explicit grants.
Oracle qualifies four types of dynamic SQL statements:
Method | Description | Functions or Procedures |
1 |
Method 1 supports DML or DDL, statements that are static, they have no inputs or outputs. It does not support DQL | execute open_cursor parse |
2 |
Method 2 supports DML statements that are dynamic which means they have bind variable. This method requires that you know the number and datatype of bind variables at statement definition. Method 2 also does not support DQL statements | bind_array bind_variable execute open_cursor parse |
3 |
Method 3 supports DML statements that are dynamic which means they have bind variable. It also supports the returning into clause. This method requires that you know the number and datatype of bind variables at statement definition. Method 3 also supports DQL statements, provided you know the number and datatype at statement definition. | bind_array |
4 |
Method 4 supports DML statements that are dynamic which means they have bind variable. It also supports the returning into clause. This method does not require advanced knowledge of the number and datatypes of the bind variables. Method 4 also supports DQL statements, withou you know the number and datatype at statement definition. | bind_array bind_variable column_value define_column describe_column describe_column2 describe_column3 execute execute_and_fetch fetch_rows open_cursor parse variable_value |
Dynamic DDL statement is normally performed as a standalone statement
Dynamic DDL statement | DECLARE -- Define local DBMS_SQL variables. c INTEGER := dbms_sql.open_cursor; fdbk INTEGER; stmt VARCHAR2(2000); BEGIN -- Use a loop to check whether to drop a sequence. FOR i IN (SELECT null FROM user_objects WHERE object_name = 'SAMPLE_SEQUENCE') |
Dynamic DML Statement uses the method 1 approach.
Dynamic DML statement | DECLARE -- Define local DBMS_SQL variables. c INTEGER := dbms_sql.open_cursor; fdbk INTEGER; stmt1 VARCHAR2(2000); stmt2 VARCHAR2(20) := '-1,SYSDATE)'; -- Variable to get OUT parameter value. client VARCHAR2(64); BEGIN stmt1 := 'INSERT INTO item VALUES ' || '( item_s1.nextval ' || ',''ASIN'||CHR(58)||' B000VBJEEG''' || ',(SELECT common_lookup_id ' || ' FROM common_lookup ' || ' WHERE common_lookup_type = ''DVD_WIDE_SCREEN'') ' || ',''Ratatouille''' || ',''''' || ', empty_clob() ' || ', NULL ' || ',''G''' || ',''MPAA''' || ',''06-NOV-2007''' || ', 3, SYSDATE,'; dbms_application_info.read_client_info(client); IF client IS NOT NULL THEN stmt1 := stmt1 || client || ',SYSDATE)'; ELSE stmt1 := stmt1 || stmt2; END IF; -- Parse and execute the statement. dbms_sql.parse(c,stmt1,dbms_sql.native); fdbk := dbms_sql.execute(c); dbms_sql.close_cursor(c); dbms_output.put_line('Rows Inserted ['||fdbk||']'); END; / INSERT INTO item VALUES ( item_s1.nextval ,'ASIN: B000VBJEEG' ,(SELECT common_lookup_id FROM common_lookup WHERE common_lookup_type = 'DVD_WIDE_SCREEN') ,'Ratatouille' ,'' , empty_clob() , NULL ,'G' ,'MPAA' ,'06-NOV-2007' , 3, SYSDATE, 3, SYSDATE); |
Dynamic Statements with input variables
Dynamic Statements with input variables | CREATE OR REPLACE PROCEDURE insert_item ( asin VARCHAR2 , title VARCHAR2 , subtitle VARCHAR2 := NULL , itype VARCHAR2 := 'DVD_WIDE_SCREEN' , rating VARCHAR2 , agency VARCHAR2 , release DATE ) IS -- Define local DBMS_SQL variables. c INTEGER := dbms_sql.open_cursor; fdbk INTEGER; stmt VARCHAR2(2000); -- Variable to get OUT parameter value. client VARCHAR2(64); BEGIN stmt := 'INSERT INTO item VALUES ' || '( item_s1.nextval ' || ',''ASIN''||CHR(58)|| :asin' || ',(SELECT common_lookup_id ' || ' FROM common_lookup ' || ' WHERE common_lookup_type = :itype) ' || ',:title' || ',:subtitle' || ', empty_clob() ' || ', NULL ' || ',:rating' || ',:agency' || ',:release' || ', :created_by, SYSDATE, :last_updated_by, SYSDATE)'; dbms_application_info.read_client_info(client); IF client IS NOT NULL THEN client := TO_NUMBER(client); ELSE client := -1; END IF; -- Parse and execute the statement. dbms_sql.parse(c,stmt,dbms_sql.native); dbms_sql.bind_variable(c,'asin',asin); dbms_sql.bind_variable(c,'itype',itype); dbms_sql.bind_variable(c,'title',title); dbms_sql.bind_variable(c,'subtitle',subtitle); dbms_sql.bind_variable(c,'rating',rating); dbms_sql.bind_variable(c,'agency',agency); dbms_sql.bind_variable(c,'release',release); dbms_sql.bind_variable(c,'created_by',client); dbms_sql.bind_variable(c,'last_updated_by',client); fdbk := dbms_sql.execute(c); dbms_sql.close_cursor(c); dbms_output.put_line('Rows Inserted ['||fdbk||']'); END insert_item; / show errors list show user BEGIN insert_item(asin => 'B000VBJEEG' ,title => 'Ratatouille' ,itype => 'DVD_WIDE_SCREEN' ,rating => 'G' ,agency => 'MPAA' ,release => '06-NOV-2007'); END; / |
Dynamic statements with input and output variables
Dynamic statements with input and output variables (single row) | DECLARE c INTEGER := dbms_sql.open_cursor; fdbk INTEGER; statement VARCHAR2(2000); item_id NUMBER := 1081; item_title VARCHAR2(60); item_subtitle VARCHAR2(60); BEGIN -- Build and parse SQL statement. statement := 'SELECT item_title, item_subtitle '|| 'FROM item WHERE item_id = :item_id'; dbms_sql.parse(c,statement,dbms_sql.native); |
Dynamic statements with input and output variables (multiple row) | DECLARE c INTEGER := dbms_sql.open_cursor; fdbk INTEGER; statement VARCHAR2(2000); item1 NUMBER := 1003; item2 NUMBER := 1013; item_title VARCHAR2(60); item_subtitle VARCHAR2(60); BEGIN -- Build and parse SQL statement. statement := 'SELECT item_title, item_subtitle ' || 'FROM item ' || 'WHERE item_id BETWEEN :item1 AND :item2 ' || 'AND item_type = 1014'; |
There are a number of constants and Function that dbms_sql package uses, you might want to consult the Oracle documentation for further information on the below functions.
Constants |
|
NATIVE | You should use NATIVE from Oracle 8 onwards |
V6 | Not used any more |
V7 | You should only use if you are using Oracle 7 |
Functions |
|
BIND_ARRAY | support bulk DML operations, binds a nested table collection into a SQL statement |
BIND_VARIABLE | supports row-by-row DML operations, binds a wide variety of datatypes into a SQL statement |
BIND_VARIABLE_CHAR | supports row-by-row DML operations, binds a char into a SQL statement |
BIND_VARIABLE_RAW | supports row-by-row DML operations, binds a raw into a SQL statement |
BIND_VARIABLE_ROWID | supports row-by-row DML operations, binds a rowid into a SQL statement |
CLOSE_CURSOR | closes an open DBMS_SQL cursor |
COLUMN_VALUE | supports bulk and row-by-row DQL operations, bind the output from a select statement into an out mode variable |
COLUMN_VALUE_CHAR | supports bulk and row-by-row DQL operations, bind the output from a select statement for a char column into an out mode variable |
COLUMN_VALUE_RAW | supports bulk and row-by-row DQL operations, bind the output from a select statement for a raw column into an out mode variable |
COLUMN_VALUE_ROWID | supports bulk and row-by-row DQL operations, bind the output from a select statement for a rowid column into an out mode variable |
DEFINE_ARRAY | supports bulk DQL operations, defines (or maps) a nested table to columns of a select statement |
DEFINE_COLUMN | supports row-by-row DQL operations, defines (or maps) column values to columns of a select statement |
DEFINE_COLUMN_CHAR | supports row-by-row DQL operations, defines (or maps) column values to columns of a select statement |
DEFINE_COLUMN_LONG | supports row-by-row DQL operations, defines (or maps) column values to columns of a select statement |
DEFINE_COLUMN_RAW | supports row-by-row DQL operations, defines (or maps) column values to columns of a select statement |
DEFINE_COLUMN_ROWID | supports row-by-row DQL operations, defines (or maps) column values to columns of a select statement |
DESCRIBE_COLUMN | supports row-by-row DQL and DML operations, describes columns for a cursor opened and parsed by the dbms_sql package, supports upto 30 character column names. |
DESCRIBE_COLUMN2 | supports row-by-row DQL and DML operations, describes columns for a cursor opened and parsed by the dbms_sql package, supports upto 32,760 character column names. |
DESCRIBE_COLUMN3 | supports row-by-row DQL and DML operations, describes columns for a cursor opened and parsed by the dbms_sql package, supports upto 32,760 character column names, it also adds the datatype name and name length to the record structure |
EXECUTE | runs the statement associated with an open dbls_sql cursor |
EXECUTE_AND_FETCH | runs the statement associated with an open dbls_sql cursor and fetches one or more rows from a cursor |
FETCH_ROWS | fetches a row or row set from a given cursor |
IS_OPEN | checks whether a cursor is open |
LAST_ERROR_POSITION | returns the byte offset in a SQL statement text where an error occurred. |
LAST_ROW_COUNT | returns the cumulative number of rows fetched from a query |
LAST_ROW_ID | returns the rowid value of the last row fetched from a query |
LAST_SQL_FUNCTION_CODE | returns SQL function code for the statement |
OPEN_CURSOR | opens a cursor in the database and returns the cursor's number |
PARSE | parses a given statement string |
TO_CURSOR_NUMBER | converts a NDS cursor to a dbms_sql cursor |
TO_REFCURSOR | converts a dbms_sql cursor number to a NDS reference cursor |
VARIABLE_VALUE | supports bulk and row-by-row DQL and DML operations, it is used to transfer a variety of datatypes back through a returning into clause. |
VARIABLE_VALUE_CHAR | supports bulk and row-by-row DQL and DML operations, it is used to transfer a char back through a returning into clause. |
VARIABLE_VALUE_RAW | supports bulk and row-by-row DQL and DML operations, it is used to transfer a raw back through a returning into clause. |
VARIABLE_VALUE_ROWID | supports bulk and row-by-row DQL and DML operations, it is used to transfer a rowid back through a returning into clause. |