(15) Dynamic SQL

[ Oracle PL/SQL ]

"From all old seamy throats of elders, musty books, I've salvaged not a word." (Cormac McCarthy)

Dynamic SQL statements are not embedded in your source program and checked at compile time.
Instead, they are stored in character strings that are input to, or built by, the program at runtime.
(i.e. dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.)

Programming methodology for generating and running SQL statements at run time. Good for:

• Writing general-purpose and flexible programs like ad hoc query systems
• Writing programs that must run DDL statements
• When number and datatypes of IN/OUT variables are unknown at compilation time
• When the full text of a SQL stmt needs to be obtained at run-time only.
• i.e. a SELECT stmt that includes an identifier (a table name) that is unknown at compile time.
• i.e. a WHERE clause in which the number of subclauses is unknown at compile time.

How to write dynamic SQL ? (two ways)

• (1) Native dynamic SQL: PL/SQL language feature for building and running dynamic SQL statements
• (2) DBMS_SQL package: , an API for building, running, and describing dynamic SQL statements

Native dynamic SQL x DBMS_SQL package:

• native dynamic SQL: Faster, and more readable (even more when optimized by compiler)
• Native dynamic SQL: requires complete specification of IN/OUT vars (number and datatype) at compile time.
• Switch between native dynamic SQL and DBMS_SQL package using
  DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER.

Using EXECUTE IMMEDIATE (Advantages over DBMS_SQL)

• DBMS_SQL: Oracle 8 and earlier
• EXECUTE IMMEDIATE: Oracle 8i and later
• Native dynamic SQL:
• Integral part of the SQL language that is built into Pl/SQL, thus making it simpler to use and faster than the DBMS_SQL package.
• Native dynamic SQL also supports the usage of object types and collections not supported by the DBMS_SQL package
• Faster than DBMS_SQL because it parses, binds, and executes the statements immediately.
• DBMS_SQL requires calls to procedures and functions.

Question: Can I use TCL or DDL within a PL/SQL Block? A: No. You HAVE to use DYNAMIC SQL for that. (Native Dyn SQL or DBMS_SQL). Static SQL is limited to DML (SELECT, INSERT, UPDATE, DELETE, MERGE), TCL (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION) and LOCK TABLE.

Native Dynamic SQL: EXECUTE IMMEDIATE

• EXECUTE IMMEDIATE: Builds and runs a dynamic SQL statement in a single operation.
• If dynamic_sql_stmt has a RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.
• When using dynamic SQL, beware of SQL injection, a security risk.
• The value of bind_argument cannot be TRUE, FALSE, or NULL

Type of dynamic SQL statement	USE of EXECUTE IMMEDIATE
SELECT returns at most one row	OUT-bind arguments (defines): in the INTO clause. IN-bind arguments: in the USING clause. EXECUTE IMMEDIATE SELECT ... INTO <OUT-vars> USING <bind_arg>;
SELECT returns multiple rows	OUT-bind arguments: in the BULK COLLECT INTO clause. IN-bind arguments: in the USING clause. EXECUTE IMMEDIATE SELECT ... BULK COLLECT INTO <OUT-vars> USING <bind_arg>;
DML with RETURNING INTO clause	IN-bind arguments: in the USING clause. OUT-bind arguments: in the RETURNING INTO clause.
DMLw/o RETURNING INTO clause	all bind arguments in the USING clause

Using dynamic SQL with EXECUTE IMMEDIATE:

-- create procedure to insert new record on departments table
create or replace procedure new_dept
 (deptid in out number,
  dname  in varchar2,
  mgrid  in varchar2,
  locid  in varchar2) as 
begin 
 deptid:= departments_seq.nextval;
 insert into departments (
    department_id,
    department_name,
    manager_id,
    location_id)
  values (deptid, dname, mgrid, locid);
end;

-- Use dynamic SQL to call the procedure new_dept
set serveroutput on
declare
 plsql_block varchar2(500);
 new_deptid  number(4);
 new_dname   varchar2(30)    := 'Advertising';
 new_mgrid   number(6)       := 200;
 new_locid   number(4)       := 1700;
 numdep      number;
begin
 select count(*) into numdep  from departments;
 dbms_output.put_line('# depts: ' || numdep);

 -- enter the dynamic SQL statement
 -- note that the statement is complete. Only dynamic binding of the 
 -- procedure parameters occur.
 -- note that the SQL stmt has placeholders for bind arguments.
 plsql_block := 'begin new_dept(:a, :b, :c, :d); end;';

 -- use EXECUTE IMMEDIATE to run the SQL stmt and specify the 
 -- binding variables.
 EXECUTE IMMEDIATE plsql_block
   USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
 select count(*) into numdep
 from departments;
 dbms_output.put_line('# depts: ' || numdep);
end;

anonymous block completed
# depts: 27
# depts: 28

Using SELECT, DDL and DML with Execute Immediate Using Conditional Compilation

What the example below does (3 procedures):
(a) Check whether a table exists in the database (SELECT)
(b) DROP and Recreate or CREATE table test and GRANT access on the table to another user (DDL)
(c) Insert data on the created table (DML)


(1) Procedure check_object:

• (SELECT returns one row only): Check whether a table named TESTE already exists in the database
• procedure call: check_object('TESTE', 'TABLE');

CREATE OR REPLACE Procedure check_object (p_objname in varchar2, p_objtype in varchar2, 
                        p_created OUT boolean) IS
 query varchar2(200);
 vcount varchar2(40);
BEGIN
  -- using SELECT with EXECUTE IMMEDIATE
  query := 'select count(*) from user_objects '||
          'where object_type = :b '|| 
          'and object_name = :c ';
  -- If the dynamic SQL statement is a SELECT statement that can return 
  -- at most one row, put out-bind arguments (defines) in the INTO clause 
  -- and in-bind arguments in the USING clause.
  EXECUTE IMMEDIATE query into vcount  USING IN p_objtype, p_objname;
  IF vcount = 0 THEN
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' does not yet exist on the database.');
    $END
    p_created := FALSE;
  ELSE
    $IF $$my_debug $THEN
       dbms_output.put_line(p_objtype || ' ' || p_objname || ' already exist on the database.');
    $END
    p_created := TRUE;
  END IF; 
EXCEPTION
 When others then 
   dbms_output.put_line('Error Procedure check_object');
   dbms_output.put_line(sqlerrm);
   dbms_output.put_line(dbms_utility.format_error_backtrace());
END;

alter session set plsql_ccflags = 'my_debug:TRUE';
set serveroutput on
declare
 v_created boolean;
begin
check_object('TESTE', 'TABLE',v_created);
end;
/

Anonymous block completed
TABLE TESTE already exist on the database.

(2) Procedure create_table:

• (DDL): If table exists, drop table and recreate it.
• (DDL): Grant SELECT on table TESTE to userA.
• procedure call: cr_tbl('TRUE', 'create table TESTE (name varchar2(20), salary number)';');
• For the procedure to succeed, the procedure owner MUST have been granted the CREATE TABLE privilege directly, not via a role.
• Role privileges are not applied in procedures

CREATE OR REPLACE procedure create_table (p_created in boolean, p_stmt in varchar2) IS
 e_leave_drop exception;
 e_leave_create exception;
 stmt varchar2(200);
 v_created boolean;
BEGIN
-- USING DDL with EXECUTE IMMEDIATE  
  IF p_created then
    -- table exist. Drop before creating.
    stmt := 'drop table teste';
    begin
      execute immediate stmt;
    exception 
      when others then 
        dbms_output.put_line('Error dropping table');
        dbms_output.put_line(sqlerrm);
        raise e_leave_drop;
    end;
    dbms_output.put_line('Table dropped.');
  END IF;
  stmt := p_stmt; 
  begin
    execute immediate stmt;
  exception 
    when others then 
      dbms_output.put_line('Error creating table');
      dbms_output.put_line(sqlerrm);
      raise e_leave_create;
  end;
  dbms_output.put_line('Table teste created.'); 
  v_created := TRUE;
  dbms_output.put_line('Calling check_object..'); 
  check_object('TESTE', 'TABLE', v_created);

  stmt :=  'grant select on teste to userb';
  execute immediate stmt;
  dbms_output.put_line('grant select completed');
EXCEPTION
 when e_leave_drop then 
  dbms_output.put_line('leaving procedure...');
 when e_leave_create then 
  dbms_output.put_line('leaving procedure...');
END;

alter procedure check_object compile;
alter session set PLSQL_CCFLAGS = 'my_debug:TRUE';
set serveroutput on
declare
stmt varchar2(200);
vcreated boolean default TRUE;
begin
 stmt := 'create table UserA.teste (name varchar2(20), salary number)';
 create_table(vcreated, stmt);
end;
/

Anonymous block completed
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed

(3) Procedure insert_tbl:

• (DML): Insert values on table TESTE
• If the dynamic SQL statement is a DML statement WITHOUT a RETURNING INTO clause, other than SELECT, put all bind arguments in the USING clause.

CREATE OR REPLACE procedure insert_tbl (p_name in varchar2, n in integer, 
                                        p_salinit number) as
 vsal number;
 stmt varchar2(200);
 vname varchar2(20);
BEGIN
  stmt := 'INSERT into TESTE values (:b, :c)';
  vsal := p_salinit;
  vname := p_name;
  for i in 1..n loop
    vname := vname || to_char(i);
    vsal := 10000 + i;
    execute immediate stmt USING vname, vsal;
    dbms_output.put_line('Inserted row '|| i);
  end loop;
EXCEPTION
 when others then 
   dbms_output.put_line('Insert error...');
   dbms_output.put_line(sqlerrm);
END;

set serveroutput on
begin
insert_tbl('Mary', 4, 5000);
end;
/

anonymous block completed
Inserted row 1
Inserted row 2
Inserted row 3
Inserted row 4

SQL> Select * from teste;

NAME                 SALARY                 
-------------------- ---------------------- 
Mary1                10001                  
Mary12               10002                  
Mary123              10003                  
Mary1234             10004                  

What the example below does:
(a) Check the columns of a given table in the data dictionary (Mulitple-row SELECT: BULK COLLECT INTO)
(b) Delete a row from the table. Returns data from the deleted row using RETURNIG clause (DML)

(1) Procedure list_tbl

• (SELECT): Get column name list with BULK COLLECT INTO.
• If the dynamic SQL statement is a SELECT statement that can return MULTIPLE rows,
  put out-bind arguments (defines) in the BULK COLLECT INTO clause and in-bind arguments in the USING clause.
• You CANNOT use bind values to pass in the names of schema objects, such as table names or column names.
• SO, names of schema objects have to be added to the dynamic sql statement, instead of passed as bind vars.

CREATE OR REPLACE procedure list_tbl (p_tblname in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 type t_aadata  is TABLE of t_recdata index by pls_integer;
 vdata t_aadata;
 type t_colnm is table of varchar2(20) index by pls_integer;
 vcolnm t_colnm;
 vb varchar2(30);
 stmt varchar2(200);
BEGIN
  stmt := 'SELECT column_name FROM user_tab_columns '||
          'WHERE table_name = :b';
  vb := p_tblname;
  EXECUTE IMMEDIATE stmt BULK COLLECT INTO vcolnm USING p_tblname;
   for i IN vcolnm.first..vcolnm.last loop
     if i = 1 then 
        vb := vcolnm(i);
     else
       vb := vb || ', '|| vcolnm(i); 
     end if;       
   end loop;
   dbms_output.put_line('columns are: '|| vb);  
    -- You CANNOT use bind values to pass in the names of schema objects, 
    -- such as table names or column names.
    -- SO, names of schema objects have to be added to the dynamic sql 
    -- statement, instead of passed as bind vars.
   stmt := 'select '|| vb || ' from teste'; 
   BEGIN
     EXECUTE IMMEDIATE stmt BULK COLLECT INTO vdata;
   EXCEPTION
     when others then
       dbms_output.put_line('Execute immediate: error SELECT');          
       dbms_output.put_line(sqlerrm);
  END;
  
  for i IN vcolnm.first..vcolnm.last loop
     dbms_output.put(vcolnm(i) || ' -- ');
  end loop;
  dbms_output.new_line();
  for i in vdata.first..vdata.last loop
     dbms_output.put_line(vdata(i).nm || ' '|| vdata(i).salary);  
  end loop;        
END list_tbl;

set serveroutput on
begin
list_tbl('TESTE');
end;
/

columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003

(2) Procedure delete_table:

• procedure call: delete_table('Mary');
• If the dynamic SQL statement is a DML statement with a RETURNING INTO clause,
  put in-bind arguments in the USING clause and out-bind arguments in the RETURNING INTO clause.

set serveroutput on
CREATE OR REPLACE procedure del_tbl(p_name in varchar2) as 
 type t_recdata is RECORD (nm varchar2(20), salary number);
 vrec t_recdata;
 stmt varchar2(200); 
BEGIN
  stmt := 'DELETE FROM teste '||
          'WHERE name = :b ' || 
          'RETURNING name, salary INTO :c, :d';
  execute immediate stmt USING IN p_name returning into vrec;
  dbms_output.put_line('Deleted rec: ' || vrec.nm || ' ' || vrec.salary);
  dbms_output.new_line;
  
  dbms_output.put_line('Listing table...');
  list_tbl('TESTE');
EXCEPTION
    when others then
       dbms_output.put_line(sqlerrm);
        dbms_output.put_line(dbms_utility.format_error_backtrace());
END;

set serveroutput on
begin
  del_tbl('Mary123');
end;
/

anonymous block completed
Deleted rec: Mary123 10003
Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Mary1 10001
Mary12 10002
Mary1234 10004

Running all together:

set serveroutput on
declare
  vcreated boolean default TRUE;
begin
  check_object('TESTE', 'TABLE', vcreated);
  create_table(vcreated, 'create table teste (name varchar2(20), salary number)');
  insert_table('Ximenes', 3, 9700);
  list_tbl('TESTE');
  delete_table('Ximenes1');
end;

nonymous block completed
TABLE TESTE already exist on the database.
Table dropped.
Table teste created.
Calling check_object..
TABLE TESTE already exist on the database.
grant select completed
Inserted row 1
Inserted row 2
Inserted row 3
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes1 10001
Ximenes12 10002
Ximenes123 10003
Deleted rec: Ximenes1 10001

Listing table...
columns are: NAME, SALARY
NAME -- SALARY -- 
Ximenes12 10002
Ximenes123 10003

Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

DBMS_SQL Package

Other examples using DBMS_SQL

SQL Injection