dynamic sql, bind variables and dynamic USING

Question

I have some dynamic forms in a .net application. Depending on on the form, the fields in the insert/update will be different. I'm trying to build a dynamic sql statement but the string may be longer than 4000 characters which doesn't work for a string literal, so I'm trying to use bind variables. Since the fields being saved are dynamic I don't know how to handle the USING block. Below is a dumbed down version of what I'm trying to do. You can check out an older question I asked about getting the data out dynamically if it helps explain the dynamic form. collection of records to out sys_refcursor

p.s. I know I could just insert nulls into every placeholder but that means I'd have to update the procedure when I add a field to the table which isn't the way to go.

procedure bindTest(oCur out sys_refcursor)

    as 
      vFirst varchar2(50) := 'Joe';
      vMiddle varchar2(50) := 'Vs'
      vLast varchar2(50) := 'Volcano';
      vVars varchar2(50) := 'vFirst, vLast';
      vSql varchar2(1000) := '';
    begin
    -- This form does not use the middle name so there are only 2 bind vars. 
    -- The field exists in the table but not in the web form so it would not be passed to the procedure.
    -- I've included it here to show data I want to ignore. 
    -- vVars includes the list of valid fields to save. 
    -- This would be the sql created by my script. 

    vSql := 'insert into tbl_users (firstName, lastName) values (:a, :b)'; 
    -- depending on the form, vSql might look like 
    ---- 'insert into tbl_users (firstName, middle, lastName) values (:a,:b,:c)'
    ---- 'insert into tbl_users (lastName) values (:a)'
    ---- etc

    execute immediate vSql using {what goes here? or how do I handle this?};
    -- I understand normally it would be `USING vFirst, vLast` but what about when it's dynamic?

    open oCur for
        select 
            ID
          , firstName
          , lastName
        from
          tbl_users
        where 
          rownum = 1
        order by 
          id desc;


end bindTest;

Answer 1

A simple but static solution assumes there is a known list of bind variables and their data types and the dynamic queries can use only a subset of those bind variables.

Here an example for five VARCHAR bind variables. You generates this PL/SQL block:

DECLARE
L_VC1 VARCHAR2(4000) := :VC1;
L_VC2 VARCHAR2(4000) := :VC2;
L_VC3 VARCHAR2(4000) := :VC3;
L_VC4 VARCHAR2(4000) := :VC4;
L_VC5 VARCHAR2(4000) := :VC5;
BEGIN
  -- here an statement using L_VC1 up to L_VC5
  -- eg
  INSERT INTO test (vc1,vc2,vc3) values (L_VC1, L_VC2, L_VC3);
END;

and executes it passing the full list of values (some of them left NULL).

EXECUTE IMMEDIATE my_generated_block USING vc1, vc2, vc3, vc4, vc5;

A nice feature is, that the dynamic SQL may use one bind variable several times without the need of extending the USINGparameters.

This must of course be maintained, if a new variable appears.

What is an alternative?

In my opinion to be truly dynamic in the bind variable list, you can't solve this with EXECUTE IMMEDIATE and you must take a step down towards DBMS_SQL.

Here the idea, without details how to implement it in PL/SQL:

DECLARE
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(cursor_name, 'INSERT INTO test (vc1,vc2,vc3) values (:vc1, :vc2, :vc3)',
                   DBMS_SQL.NATIVE);
    -- call in a loop for each BV               
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc1', 'x');
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc2', 'y');
    DBMS_SQL.BIND_VARIABLE(cursor_name, ':vc3', 'z');
    ---
    rows_processed := DBMS_SQL.EXECUTE(cursor_name);
    DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
/

You'll have to call the DBMS_SQL.BIND_VARIABLE in a loop for each bind variable name and the value.

Note that I'm completely ignoring the datatype of the bind variables, which should be considered as well and would probably make the solution a bit more involved but possible to solve.

Which solution is more feasible?

If your relational database design is truly key - value (i.e. you may introduce new bind variables without a DDL) you'll have to follow the second option. Otherwise, i.e. in case that table structure modification is required to get a new bind variable and if the change frequency is low, I'd prefer the first option.