Notes
Slide Show
Outline
1
Dynamic SQL in a Dynamic World
2
The Problem
  • It is difficult to build a system that actually meets the stated requirements.
    • Is the industry-wide 75% system failure rate still true?
  • It is very difficult to build a system that doesn’t require massive changes within a short period of time.
    • How many mistakes were made by previous contractors?
  • It is impossible to build a system that will not be obsolete sooner or later.
    • Did you ever meet anyone who is omniscient?
3
The Truth… Let’s be fair…
  • I’ve never seen:
    • 100% perfect analysis
    • 100% complete set of requirements
    • 100% adequate hardware
    • 100% competent users
    • 100% knowledgeable tech support
4
Not My Job
  • Educate users
  • Improve customer support
5
My Job
6
Unknowns
  • What elements are involved?
    • Example: The monthly summary table may not exist.
  • What to do with the elements you have?
    • Example: Do you need quarterly or yearly totals?
  • How you should proceed?
    • Example: Is a hash join hint the best option?
  • Whether or not you can proceed?
    • DDL is still prevented in PL/SQL, isn’t it?
7
The Hero
  • Dynamic SQL:
    • Makes it possible to build and process complete SQL and PL/SQL statements as strings at runtime.
8
Techniques
  • Dynamic SQL
    • EXECUTE IMMEDIATE
    • Dynamic cursors
    • DBMS_SQL
  • Other useful features
    • Bulk operations
    • Functions that return collections
    • Function-based views
9
Basic Example
  •  Main:
    • Generic API that allows you to fetch any column from any table using the primary key.
  • Addition:
    • Implement column-level privileges.
      • Example: Restrict user SCOTT from seeing SALARY
10
Basic Example – The Code
  • function F_GET_COLUMN_TX
  •   (i_table_tx,i_showColumn_tx,i_pkColumn_tx,i_pkValue_nr)
  • return varchar2 is
  •   v_out_tx varchar2(4000);
  •   v_sql_tx varchar2(32000);
  • Begin
  •   if user='SCOTT' and i_showColumn_tx = 'SAL' then
  •       return '[Not enough privileges]';
  •   end if;


  •  v_sql_tx:=' select '||i_showColumn_tx||
  •    ' from '||i_table_tx||
  •    ' where '||i_pkColumn_tx||'='||i_pkValue_nr;


  •  EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx;


  •   return v_out_tx;
  • end;
11
Dynamic SQL Core
  • About 90% of dynamic SQL is covered by a single command (with variations):
  • declare
  •    v_variable_tx varchar2(32000);
  • begin
  •    v_variable_tx:='whatever_you_want';
  •    EXECUTE IMMEDIATE v_variable_tx;
  • end;
  •               OR
  • begin
  •    EXECUTE IMMEDIATE 'whatever_you_want';
  • end;
12
Things to know
  • The code can be passed as a variable/string.
  • The variable/string cannot exceed 32K.
  • PL/SQL - semicolon at the end; don’t return anything
  • execute immediate 'begin p_test; end;';
  • SQL – no semicolon at the end; must return the result into the variable of corresponding type (if anything is returned):
  • execute immediate 'select 1 from dual'
  • into a;
13
Size Matters!
  • If the database is using fonts where 1 byte=1 char
    • You can concatenate a number of strings up to 64K of total length
    • In 8i (because of a bug) you could concatenate without upper bound


    • execute immediate v1_txt||v2_txt||v3_txt||…;


  • In 11i – a promise of no upper limit
14
The Problem
  •  Imagine running the example several times:
  •    f_get_Column_tx('emp','eName','empNo',7896)
  •    f_get_Column_tx('emp','eName','empNo',4564)
  •    f_get_Column_tx('emp','eName','empNo',4546)
  • Result:
    • SGA is occupied with exactly the same statements.
  •      select eName from emp where empNo=7896
  •      select eName from emp where empNo=4564
  •      select eName from emp where empNo=4546
    • CPU time is spent re-parsing exactly the same statement.
15
The Solution
  • 1. Use bind variables
  • 2. Use bind variables
  • 3. Use bind variables
  • 4. Use bind variables
  • 5. Use bind variables
  • 6. Use bind variables
  • 7. Use bind variables
  • © Tom Kyte
16
About Bind Variables
  • Part of the text string that forms the dynamic SQL
  • Start with a colon (:)
  • Placeholders for values that will be supplied at runtime
  • No validation when compiled
    • No check for datatypes
    • No check for passing enough values
17
Corrected Basic Example
  • function F_GET_COLUMN_TX
  •  (i_table_tx,i_showColumn_tx,i_pkColumn_tx,i_pkValue_nr)
  • return varchar2 is
  •   v_out_tx varchar2(4000);
  •   v_sql_tx varchar2(32000);
  • Begin
  •   ...
  •   v_sql_tx:=' select '||i_showColumn_tx||
  •             ' from '||i_table_tx||
  •             ' where '||i_pkColumn_tx||'=:v01';
  •   EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx
  •   USING i_pkValue_nr;


  •    return v_out_tx;
  • end;
18
Bind Variable Example
  • No direct limit on the number of bind variables
  • Like any other variables, they could be IN (default), OUT, IN/OUT


  • declare
  •     a NUMBER;
  •     b NUMBER:=1;
  •     c NUMBER:=2;
  •     v_plsql_tx VARCHAR2(2000);
  • begin
  •     v_plsql_tx:='begin :v01:=:v02+:v03; end;';
  •     execute immediate v_plsql_tx
  •     using out a, b, c;
  •     DBMS_OUTPUT.put_line('a='||a);
  • end;
19
Using Bind Variables
  • Bind variables CAN only be used to supply values to be passed to SQL code.
    • Bind variables are substituted with real values AFTER parsing.
  • Bind variables CANNOT be used to define the structural elements of queries or PL/SQL blocks.
    • Structural elements are needed in order for the  statement to be prepared and parsed.
20
Bind Variables in
Dynamic PL/SQL
  • Bind variables are NOT reusable in dynamic SQL – the number of variables is equal to the number of parameters.
  • Bind variables ARE reusable in dynamic PL/SQL – the number of UNIQUE variables is equal to the number of parameters.
  • declare
  •   a NUMBER:=2;
  •     b NUMBER:=3;
  •     v_plsql_tx VARCHAR2(2000);
  • begin
  •     v_plsql_tx:='begin :v01:=:v01+:v02; end;';
  •     execute immediate v_plsql_tx
  •     using in out a, b;
  •     DBMS_OUTPUT.put_line('a='||a);
  • end;
21
More About Bind Variables
  • You cannot pass NULL as a literal – only as a variable


  • procedure p_clear_Column
  • (i_table_tx,i_clearColumn_tx,i_pkColumn_tx,i_pkValue_nr)is
  •   v_sql_tx  varchar2(32000);
    v_null_tx varchar2(256):=NULL;
  • Begin
  •   v_sql_tx:=' update '||i_table_tx||
  •         ' set '||i_clearColumn_tx||'=:v01'||
  •         ' where  '||i_pkColumn_tx||'=:v02';
  • EXECUTE IMMEDIATE v_sql_tx
  •   USING v_null_tx, i_pkValue_nr; -- valid
  • -- EXECUTE IMMEDIATE v_sql_tx INTO v_out_tx
  •   -- USING NULL, i_pkValue_nr;   -- invalid
  • end;
22
Using Returning Clauses
  •  If you build statements with a RETURNING clause, it should be also used in dynamic SQL.
  • function f_appendColumn_tx (i_table_tx,
  •   i_column_tx,i_newValue_tx,i_pkColumn_tx,i_pkValue_nr)
  • return varchar2 is
  •   v_sql_tx  varchar2(32000);
  •   v_out_tx  varchar2(256);
  • Begin
  •   v_sql_tx:='update '||i_table_tx||
  •       ' set '||i_column_tx||'='||
  •                i_column_tx||'||:v01'||
  •       ' where  '||i_pkColumn_tx||'=:v02'||
  •       ' returning '||i_column_tx||' into :v03';
  •   EXECUTE IMMEDIATE v_sql_tx
  •   USING i_newValue_tx, i_pkValue_nr
  •   RETURNING INTO v_out_tx;
  •   return v_out_tx;
  • end;
23
Special cases (1)
  • DDL
    • Implicit COMMITs à autonomous transactions are strongly recommended.
    • There is a chance that it will lock itself.


    • create procedure p_drop_procedure
    • (i_proc_tx VARCHAR2) is
    •    pragma autonomous_transaction;
    • begin
    •   execute immediate
         'drop procedure '||i_proc_tx;
    • end;
24
Special cases (2)
  • Alter session/system/database…
  • create procedure p_dropFBIndex (i_index_tx)is
  • Begin
  •   execute immediate
  •     'ALTER SESSION SET EVENTS ''10624 trace '||
  •        ' name context forever, level 12''';
  •   execute immediate
  •     'drop index '||i_index_tx;
  •   execute immediate
  •     'ALTER SESSION SET EVENTS ''10624 trace '||
  •        ' name context off''';
  • end;
25
Special cases (3)
  • Quotes in strings:
    • Should be doubled (unless in 10g you use q ‘{}’) in the string
    • Should NOT be used AT ALL for bind variables
    • create or replace function f_appendColumn_tx
    • ...
    • Begin
    •   v_sql_tx:=' update '||i_table_tx||
    •    ' set '||i_column_tx||'= '
    •       ||i_column_tx||'||''-''||:v01'||
    •    ' where  '||i_pkColumn_tx||'=:v02'||
    •    ' returning '||i_column_tx||' into :v03';
    •   EXECUTE IMMEDIATE v_sql_tx
    •     USING i_newValue_tx, i_pkValue_nr
    •     RETURNING INTO v_out_tx;
    •   return v_out_tx;
    • end;
26
Taking It One Step Farther
  • More things to do with dynamic SQL
    • Advanced datatypes
    • Dynamic cursors
    • DBMS_SQL
27
Advanced Datatypes (1)
  •  Dynamic SQL does not support any PL/SQL datatypes.
    • Exception: You can use RECORD as an output of a dynamic query.
  • Dynamic SQL does support all user-defined SQL datatypes.
28
Advanced Datatypes
- Example 1
  • Create type lov_oty is object
  • (id_nr NUMBER, display_tx VARCHAR2(256));
  • function f_getDisplay_oty (i_table_tx,
  •   i_id_tx,i_display_tx,i_pkColumn_tx,i_pkValue_nr)
  • return lov_oty  is
  •   v_out_oty lov_oty;
  • begin
  •   execute immediate
  •    'select lov_oty('
                    ||i_id_tx||','||i_display_tx||
  •                  ') '||
     ' from '||i_table_tx||
  •    ' where '||i_pkColumn_tx||'=:1'
  •   into v_out_oty using i_pkValue_nr;
  •   return v_out_oty;
  • end;
29
Advanced Datatypes (2)
  • Dynamic SQL supports object collections and all kinds of operations on object collections:
    • FORALL  (Currently only in USING clause):
      • Right:
  •        forall i in dept.first..dept.last
  •        execute immediate
  •         'delete from emp where deptno=:1‘
  •          using dept(i);
      • Wrong:
  •        forall i in dept.first..dept.last
  •          execute immediate
  •          'drop table t_'||dept(i);
    • BULK COLLECT
30
Advanced Datatype Example 2a
  • type lov_nt as table of lov_oty;


  • function f_getLov_nt
  • (i_table_tx,i_id_tx,i_display_tx,i_order_tx)
  • return lov_nt is
  •   v_out_nt lov_nt := lov_nt();
  • begin
  •   execute immediate
  •     'select lov_oty('
  •           ||i_id_tx||','||i_display_tx||
                      ')'||
  •     ' from '||i_table_tx||
  •     ' order by '||i_order_tx
  •   bulk collect into v_out_nt;
  •   return v_out_nt;
  • end;
31
Advanced Datatype Example 2b
  • Completely generic value list query for any UI
    • Uses bind variables – no significant performance impact
    • Completely dynamic – any new fields/tables/etc.

  • select id_nr, display_tx
  • from table(
  •            cast(f_getLov_nt
  •                   ('emp',
  •                    'empno',
  •                    'ename||''-''||job',
  •                    'ename')
  •            as lov_nt)
  •            )
32
Advanced Datatype Example 2c
  • VIEWS on top of dynamic functions:
    • Completely hide the underlying logic from the UI
    • INSTEAD-OF triggers make logic bi-directional
    • Minor problem – there is still no way of passing parameters into the view other than some kind of globals

  • Create or replace view v_generic_lov as
  • select id_nr, display_tx
  • from table( cast(f_getLov_nt
  • (GV_pkg.f_getCurTable,
  • GV_pkg.f_getPK(GV_pkg.f_getCurTable),
  • GV_pkg.f_getDSP(GV_pkg.f_getCurTable),
  • GV_pkg.f_getSORT(GV_pkg.f_getCurTable))
  •             as lov_nt)
  •            )
33
Dynamic Cursors (1)
  • Syntax
  • declare
  •     v_cur SYS_REFCURSOR;
  •     v_sql_tx varchar2(32000):=...
  •     v_rec ...%rowtype; -- or record type
  • begin
  •    open v_cur for v_sql_tx;
  •     fetch v_cur into v_rec;
  •     close v_cur;
  • end;
  • Most common use:
    • Processing large datasets with unknown structure
34
Dynamic Cursors -  Example 1
  • Get REF CURSOR that points to any table you specify


  • function f_getRefCursor_REF
  •  (i_table_tx,i_where_tx,i_order_tx)
  • return SYS_REFCURSOR
  • is
  •   v_out_ref SYS_REFCURSOR;
  •   v_sql_tx varchar2(32000);
  • begin
  •   v_sql_tx:='select * from '||i_table_tx||
  •             ' where '||i_where_tx||
  •             ' order by'||i_order_tx;
  •   open v_out_ref for v_sql_tx;
  •   return v_out_ref;
  • end;
35
Dynamic Cursors (2)
  • The problem:
    • No way to dynamically identify resulting record structure
    • Have to hard code a data type of the output variable
  • Recommendation:
    • Try to use single-table return
      • Since you know the table, you can define the variable with %rowtype)
    • Create an object type to be returned from the query.
36
Dynamic Cursors -  Example 2
  • Safely limit returned number of rows


  • function f_getLov_nt
  • (i_table_tx,i_id_tx,i_display_tx,i_order_tx,
  •    i_limit_nr)
  • return lov_nt is
  •   v_out_nt lov_nt := lov_nt();
  •   v_cur SYS_REFCURSOR;
  • begin
  •   open v_cur for
  •   'select lov_oty('||i_id_tx||','||i_display_tx||')'||
  •     ' from '||i_table_tx||
  •     ' order by '||i_order_tx;
  •   fetch v_cur bulk collect into v_out_nt
  • limit i_limit_nr;
  •   close v_cur;
  •   return v_out_nt;
  • end;
37
DBMS_SQL package
  • Predecessor of native dynamic SQL
  • Pros:
    • Goes above 32K
    • Separates PARSE and EXECUTE
      • The same query can be reused with different bind variables.
    • Works with unknown number/type of INPUT/OUTPUT values
  • Cons:
    • Significantly slower (up to 5 times)
    • No user-defined datatypes or output to the record
    • More difficult to use
  • Conclusion: Should be avoided unless really needed
38
Real-Life Examples
  • Dynamic SQL used in real systems:
    • Generic query processor
    • Universal wrapper
    • Universal “cloner”
39
Task #1
  • The problem:
    • Existing (ancient) UI generates query on-the-fly
      • No way to intercept generation
      • No way to have a limited number of possible cases
      • Front-end is restricted to see only 500 rows
    • Need to generate complete result
  • The solution:
    • Generic query processor with a CLOB output (tab-delimited format)
40
Process Query (1)
  • Parse query and get column list:
  • FUNCTION  f_getQuery_cl (in_sql_tx, i_limit_nr number:=64000)
  • RETURN CLOB IS
  •  v_cur        integer := dbms_sql.open_cursor;
  •  v_columns_nr number := 0;
  •  v_columns_tt dbms_sql.desc_tab;
  •  ...
  •  pragma autonomous_transaction;
  • BEGIN
  •  DBMS_SQL.parse
    (v_cur, in_sql_tx, DBMS_SQL.native);
  •  DBMS_SQL.describe_columns
       (v_cur, v_columns_nr, v_columns_tt);
  •  FOR i IN 1 .. v_columns_nr  LOOP
  • v_columnList_tx :=
  • v_columnList_tx || '||chr(9)||''"''||v_query_tt (i).'||
  • v_columns_tt (i).col_name||'||''"'' ';
  •   v_header := v_header||chr(9)||'"'||
    v_columns_tt (i).col_name||'"';
  •  END LOOP;
41
Process Query (2)
  • Define a cursor in the dynamic block
  • ...
  • v_process_tx :=
  • 'DECLARE
  •   v_out_cl       CLOB;
  •   v_row_tx   VARCHAR2(32000);
  •   cursor c1 is '||in_sql_tx||';
  •   TYPE DynamicTable_tt IS TABLE OF c1%rowtype
    INDEX BY BINARY_INTEGER;
  •   v_query_tt   DynamicTable_tt;
  •   PROCEDURE p_addline (pi_tx VARCHAR2) IS
  •   BEGIN DBMS_LOB.writeappend(v_out_cl,length(pi_tx),pi_tx); END;
  • BEGIN
  •   DBMS_LOB.createtemporary
     (v_out_cl, TRUE, DBMS_LOB.CALL);
  •   p_addline(:1);
  • ...
42
Process Query (3)
  • Execute the query
  • ...
  •   open c1;
  •   fetch c1 bulk collect
  • into v_query_tt limit :2;
  •   close c1;
  •   FOR i IN 1 .. v_query_tt.COUNT LOOP
  •    v_row_tx:='||v_columnList_tx ||';
  •     p_addline(v_row_tx);
  •   END LOOP;
  •   :3 := v_out_cl;
  • END;';
  •   EXECUTE IMMEDIATE v_process_tx
  •   USING IN  v_header, IN  i_limit_nr,
  •     OUT  v_out_cl;
  •   return v_out_cl;
  • END;
43
Task #2
  • The problem:
    • Large number of requests from the user interface
      • Take some number of parameters
      • Return something (search, extra info, status etc)
    • The whole set of requests is not clear and may change each time.
  • The solution:
    • Universal wrapper
      • Process all requests dynamically
44
Storage (1)
  • Published function
    • ID_NR – unique id of the function
    • DisplayName_tx – header of the screen
      • ID and display are shown to users as LOV
    • Function_tx – real function to be called
  • Parameters (never needed more than 10)
    • Vx_Label_tx – label for the parameter
      • if null – parameter is disabled
    • Vx_Type_tx – helps UI to build the screen. Could be:
      • LOV – value list
      • TEXT – free text
      • DATE – attached calendar is needed
    • Vx_Required_yn – helps UI enforce needed parameters
    • Vx_LOV_tx –name of the corresponding value list
    • Vx_Convert_Tx – any expression with one input
      • Example - 'to_date(:1, ''YYYYMMDD'')' – transformation to the real date
      • Should always use bind variable with correct ID
45
Storage (2)
  • Example:
  • insert into t_extra_ui
  • (id_nr,
  •    displayName_tx, function_tx,
  •    v1_label_tx,v1_type_tx,  v1_req_yn,
  •    v1_lov_tx, v1_convert_tx,
  •    v2_label_tx, v1_type_tx, v1_req_yn,
  •    v2_lov_tx, v2_convert_tx)
  • values
  • (ui_seq.nextval,
  •   'Filter Employees','f_getEmp_cl',
  •   'Job','TEXT','N',
  •    null,null,
  •   'Hire Date','DATE','N',
  •    null,'to_date(:1,''YYYYMMDD'')');
46
The Wrapper (1)
  • function f_wrapper_cl (i_id_nr,
    v1_tx varchar2:=null,...,v5_tx varchar2:=null)
  • return CLOB is
  •   v_out_cl CLOB;
  •   v_sql_tx varchar2(2000);
  •   v_rec t_extra_ui%rowtype;
  • begin
  •   select * into v_rec from t_extra_ui where id_nr=i_id_nr;

  • if v_rec.v1_label_tx is not null then
  •     v_sql_tx:=nvl(v_rec.v1_convert_tx,':1');
  •   end if;
  •   ...
  •   if v_rec.v5_label_tx is not null then
  •     v_sql_tx:=v_sql_tx||','||
  •        nvl(v_rec.v5_convert_tx,':5');
  •   end if;
47
The Wrapper (2)
  •   v_sql_tx:=
  •    'begin :out:='||v_rec.function_tx||
  •                 '('||v_sql_tx||'); end;';


  •   if v5_tx is not null then
  •     execute immediate v_sql_tx
  •       using out v_out_cl, v1_tx,…,v5_tx;
  •   ...
  •   elsif v1_tx is not null then
  •     execute immediate v_sql_tx
  •       using out v_out_cl, v1_tx;
  •   else
  •     execute immediate v_sql_tx using out v_out_cl;
  •   end if;


  •   return v_out_cl;
  • end;
48
Task #3
  • The Problem:
    • Large number of tables in a hierarchical structure that represents an application
      • Customer A has address B, confirmed by person C, with phone D …
  • The Task:
    • Create a full copy of the application in the same database with new IDs preserving all parent/child relationships.
  • Environment:
    • Single-column primary key
    • FK for all relationships
    • Universal sequence for all objects
  • Solution:
    • Universal clone mechanism
49
Define Single Level
  • Specify in NORMAL code what you need:
  • declare
  •     type rows_t is table of REFERENCE%rowtype;
  •     v_rows rows_t;
  • begin
  •     select * bulk collect into v_rows
  •     from REFERENCE t where ADDRESS_ID in
  •           (select id from table (cast (:1 as id_tt)));
  •     for i in v_rows.first..v_rows.last loop
  •       select object_Seq.nextval into v_new_id from dual;
  •       …
  •       v_rows(i).REF_ID:=v_new_id;
  •       v_rows(i).ADDRESS_ID:=
  •         clone_pkg.v_Pair_t(v_rows(i).ADDRESS_ID);
  •     end loop;
  •     forall i in v_rows.first..v_rows.last
  •       insert into REFERENCE values v_rows(i);
  •     …
  • end;
50
Make it Abstract
  • procedure p_process (in_list_rec list_rec, in_parent_list id_tt) is
  •     v_execute_tx varchar2(32000);
  • begin
  •    v_execute_tx:=
  •     'declare '||
  •     ' type rows_t is table of '
  •                   ||in_list_rec.table_tx||'%rowtype;'||
  •     ...
  •     'begin '||
  •     '  select * bulk collect into v_rows '||
  •     '  from '||in_list_rec.table_tx||' t where '||in_list_rec.fk_tx||
  •     '    in (select * from table (cast (:1 as id_tt)));'||
  •     '  if v_rows.count()=0 then return; end if;'||
  •     '  for i in v_rows.first..v_rows.last loop '||
  •     '      select object_Seq.nextval into v_new_id from dual;'||
  •     '      v_rows(i).'||in_list_rec.pk_tx||':=v_new_id;'||
  •     '      v_rows(i).'||in_list_rec.fk_tx||
  •     '       :=clone_pkg.v_Pair_t(v_rows(i).'||in_list_rec.fk_tx||');'||
  •     ...
  •     '  end loop;'||
  •     ...
  •     'end;';
  •     execute immediate v_execute_tx using in_parent_list;
  • end;
51
Fighting Code Injections
  • DBA protection
    • End users should not see administration tools.
  • UI protection
    • User input should always be passed via bind variables (no concatenation!).
      • Bind variables cannot affect the structure of the query.
    • All structural selections should be done from a limited list of options (repository)
      • Power users/developers populate the repository.
      • End users only access whatever is already in the repository.
52
Summary
  • Dynamic SQL does:
    • Significantly extend the list of available options for resolving many problems
    • Provide extra maneuvering room in production environments
  • Dynamic SQL should NOT:
    • be considered a substitute for good analysis
    • be used where “regular” solutions are valid
53
Contact Information
  • Michael Rosenblum – mrosenblum@dulcian.com
  • Dulcian website - www.dulcian.com