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
BEGIN insert_item (table_name => 'ITEM' ,asin => 'B00005O3VC' ,item_type => 'DVD_FULL_SCREEN' ,item_title => 'Monty Python and the Holy Grail' ,item_subtitle => 'Special Edition' ,rating => 'PG' ,agency => 'MPAA' ,release_date => '23-OCT-2001'); END; / Note: The dbms_assert package lets you validate input parameters against SQL injection attacks.

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;
/
-- Test the procedure BEGIN insert_item (asin => 'B00005O3VC' ,item_type => 'DVD_FULL_SCREEN' ,item_title => 'Monty Python and the Holy Grail' ,item_subtitle => 'Special Edition' ,rating => 'PG' ,agency => 'MPAA' ,release_date => '23-OCT-2001'); END; / -- Test the procedure BEGIN insert_item (asin => 'B000G6BLWE' ,item_type => 'DVD_FULL_SCREEN' ,item_title => 'Young Frankenstein' ,rating => 'PG' ,agency => 'MPAA' ,release_date => '05-SEP-2006'); END; /

The next NDS example demonstrates a dynamic statement with inputs and outputs.

Dynamic statement inputs and outputs
-- Example 1
DECLARE -- Define explicit record structure. TYPE title_record IS RECORD ( item_title VARCHAR2(60) , item_subtitle VARCHAR2(60)); TYPE title_collection IS TABLE OF TITLE_RECORD; -- Define dynamic variables. title_cursor SYS_REFCURSOR; titles TITLE_COLLECTION; stmt VARCHAR2(2000); BEGIN -- Set statement. stmt := 'SELECT item_title, item_subtitle ' || 'FROM item ' || 'WHERE SUBSTR(item_title,1,12) = :input'; -- Open and read dynamic curosr, then close it.
  -- :input gets its values from the using clause OPEN title_cursor FOR stmt USING 'Harry Potter';

  -- Load the cursor returning set into a collection FETCH title_cursor BULK COLLECT INTO titles; FOR i IN 1..titles.COUNT LOOP dbms_output.put_line( '['||titles(i).item_title||']['||titles(i).item_subtitle||']'); END LOOP; CLOSE title_cursor; END; / -- Example 2 CREATE OR REPLACE PROCEDURE get_clob ( item_title_in VARCHAR2, item_desc_out IN OUT CLOB ) IS BEGIN UPDATE item SET item_desc = empty_clob() WHERE item_id = (SELECT item_id FROM item WHERE item_title = item_title_in) RETURNING item_desc INTO item_desc_out; END get_clob; / DECLARE -- Define explicit record structure. target CLOB; source VARCHAR2(2000) := 'A Mel Brooks classic movie!'; movie VARCHAR2(60) := 'Young Frankenstein'; stmt VARCHAR2(2000); BEGIN -- Set statement stmt := 'BEGIN ' || ' get_clob(:input,:output); ' || 'END;'; EXECUTE IMMEDIATE stmt USING movie, IN OUT target; dbms_lob.writeappend(target,LENGTH(source),source); COMMIT; END; /

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)
  );
TYPE title_table IS TABLE OF title_record;
  -- Declare dynamic variables. title_cursor SYS_REFCURSOR; title_rows TITLE_TABLE;
  -- Declare DBMS_SQL variables. c INTEGER := dbms_sql.open_cursor; fdbk INTEGER;
  -- Declare local variables. counter NUMBER := 1; column_names DBMS_SQL.VARCHAR2_TABLE; item_ids DBMS_SQL.NUMBER_TABLE; stmt VARCHAR2(2000); substmt VARCHAR2(2000) := ''; BEGIN -- Find the rows that meet the criteria. FOR i IN (SELECT 'item_ids' AS column_names, item_id FROM item WHERE REGEXP_LIKE(item_title,'^Harry Potter'))
  LOOP column_names(counter) := counter; item_ids(counter) := i.item_id; counter := counter + 1; END LOOP; dbms_sql.close_cursor(c);
  -- Dynamically create substatement. IF item_ids.COUNT = 1 THEN substmt := 'WHERE item_id IN (:item_ids)'; ELSE substmt := 'WHERE item_id IN ('; FOR i IN 1..item_ids.COUNT LOOP IF i = 1 THEN substmt := substmt ||':'||i; ELSE substmt := substmt ||',:'||i; END IF; END LOOP; substmt := substmt || ')'; END IF; -- Set statement. stmt := 'SELECT item_title, item_subtitle ' || 'FROM item ' || substmt; -- Parse the statement with DBMS_SQL dbms_sql.parse(c,stmt,dbms_sql.native); -- Bind the bind variable name and value. FOR i IN 1..item_ids.COUNT
  LOOP dbms_sql.bind_variable(c,column_names(i),item_ids(i)); END LOOP;
-- Execute using DBMS_SQL. fdbk := dbms_sql.execute(c); -- Convert the cursor to NDS. title_cursor := dbms_sql.to_refcursor(c); -- Open and read dynamic curosr, then close it. FETCH title_cursor BULK COLLECT INTO title_rows; FOR i IN 1..title_rows.COUNT
  LOOP dbms_output.put_line( '['||title_rows(i).item_title||']['||title_rows(i).item_subtitle||']'); END LOOP; -- Close the System Reference Cursor. CLOSE title_cursor; END; /

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
bind_variable
column_value
define_column
execute
execute_and_fetch
fetch_rows
open_cursor
parse
variable_value

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') 
  LOOP -- Build dynamic SQL statement. stmt := 'DROP SEQUENCE sample_sequence'; -- Parse and execute the statement. dbms_sql.parse(c,stmt,dbms_sql.native); fdbk := dbms_sql.execute(c); -- Close the open cursor. dbms_sql.close_cursor(c); -- Print output line. dbms_output.put_line('Dropped Sequence [SAMPLE_SEQUENCE]'); END LOOP; END; /

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);
-- Define column mapping, execute statement, and copy results. dbms_sql.define_column(c,1,item_title,60); -- Define OUT mode variable. dbms_sql.define_column(c,2,item_subtitle,60); -- Define OUT mode variable. dbms_sql.bind_variable(c,'item_id',item_id); -- Bind IN mode variable. fdbk := dbms_sql.execute_and_fetch(c); dbms_sql.column_value(c,1,item_title); -- Copy query column to variable. dbms_sql.column_value(c,2,item_subtitle); -- Copy query column to variable. -- Print return value and close cursor. dbms_output.put_line('['||item_title||']['||NVL(item_subtitle,'None')||']'); dbms_sql.close_cursor(c); END; /
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';
dbms_sql.parse(c,statement,dbms_sql.native); -- Define column mapping and execute statement. dbms_sql.define_column(c,1,item_title,60); -- Define OUT mode variable. dbms_sql.define_column(c,2,item_subtitle,60); -- Define OUT mode variable. dbms_sql.bind_variable(c,'item1',item1); -- Bind IN mode variable. dbms_sql.bind_variable(c,'item2',item2); -- Bind IN mode variable. fdbk := dbms_sql.execute(c); -- Read results. LOOP EXIT WHEN dbms_sql.fetch_rows(c) = 0; -- No more results. -- Copy and print. dbms_sql.column_value(c,1,item_title); -- Copy query column to variable. dbms_sql.column_value(c,2,item_subtitle); -- Copy query column to variable. dbms_output.put_line('['||item_title||']['||NVL(item_subtitle,'None')||']'); END LOOP; dbms_sql.close_cursor(c); END; /

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.