Using Bind Variables with Dynamic Select into Clause in Pl/Sql

Using bind variables with dynamic SELECT INTO clause in PL/SQL

No you can't use bind variables that way. In your second example :into_bind in v_query_str is just a placeholder for value of variable v_num_of_employees. Your select into statement will turn into something like:

SELECT COUNT(*) INTO  FROM emp_...

because the value of v_num_of_employees is null at EXECUTE IMMEDIATE.

Your first example presents the correct way to bind the return value to a variable.

Edit

The original poster has edited the second code block that I'm referring in my answer to use OUT parameter mode for v_num_of_employees instead of the default IN mode. This modification makes the both examples functionally equivalent.

Dynamic SQL to select into a variable

You cannot use placeholders for table columns in dynamic query. However you can use concatenate to achieve your requirement. See below:

DECLARE
   loc_stmt       VARCHAR2 (200);
   loc_return     VARCHAR2 (30) := NULL;
   loc_code_tab   VARCHAR2 (30);
   P_TABLE_NAME   VARCHAR2 (100) := 'BILLG_FRQNCY_TYPE';
   P_CODE         NUMBER := 1;
BEGIN
   loc_code_tab := SUBSTR (P_TABLE_NAME, 1, LENGTH (P_TABLE_NAME) - 3);

   loc_stmt :=
         'SELECT  '
      || loc_code_tab
      || '_DESC'
      || ' FROM '
      || loc_code_tab
      || ' WHERE '
      || P_TABLE_NAME
      || ' = '
      || P_CODE;

   EXECUTE IMMEDIATE loc_stmt  INTO loc_return ;

   DBMS_OUTPUT.PUT_LINE (loc_return);
END;
/

sql select statement as bind variable for dynamic plsql block

If I understand well, you need to run an entire dynamic PLSQL block by using as SQL query as a bind variable; if so, you can try this way:

SQL> declare
  2      vPlSqlBlock varchar2(10000);
  3      vSQL        varchar2(1000);
  4  BEGIN
  5    vSQL:='SELECT 1 from dual';
  6    --
  7    vPlSqlBlock:='DECLARE
  8      type emp_type IS TABLE OF NUMBER;
  9      emp_id   emp_type;
 10      vSQLDyn  varchar2(1000) := :1;
 11      BEGIN
 12          EXECUTE IMMEDIATE vSQLDyn BULK COLLECT INTO emp_id;
 13          --
 14          /* whatever you need to do in your block */
 15          for i in emp_id.first .. emp_id.last loop
 16              dbms_output.put_line(emp_id(i));
 17          end loop;
 18      END;';
 19
 20      EXECUTE IMMEDIATE vPlSqlBlock USING vSQL;
 21  end;
 22  /
1

PL/SQL procedure successfully completed.

SQL> declare
  2      vPlSqlBlock varchar2(10000);
  3      vSQL        varchar2(1000);
  4  BEGIN
  5    vSQL:='SELECT 1 from dual';
  6    --
  7    vPlSqlBlock:='DECLARE
  8      type emp_type IS TABLE OF NUMBER;
  9      emp_id   emp_type;
 10      vSQLDyn  varchar2(1000) := :1;
 11      BEGIN
 12          EXECUTE IMMEDIATE vSQLDyn BULK COLLECT INTO emp_id;
 13          /* this does nothing */
 14      END;';
 15
 16      EXECUTE IMMEDIATE vPlSqlBlock USING vSQL;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL>

PL-SQL: why does a dynamic statement using bind-variable input not work?

You can't use a bind variable as a table_reference. As documented in the link, the table_reference must be static part of the statement.

 select * from :table  -- ILLEGAL

Contrary to that the usage in the WHERE clause is allowed

 where :row = :value  -- LEGAL, but questionable

but it doesn't do what you expect. The whereclause returns TRUE, if both bind variables passed are same.
I.e. it is equivalent to

 where 'name' = 'xxxx'

and not to

 where name = 'xxxx'

dynamic sql, bind variables and dynamic USING

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.

Oracle SQL - How to define a date bind variable based on a select statement

So, to sum up my comments from above, you don't need PL/SQL to do this. You could do all of your script with straight SQL:

CREATE TABLE A_TEST_TABLE
(
  user varchar2(100),
  product    varchar2(100),
  datum      date
);

INSERT INTO a_test_table
SELECT usr.user,
       usr.product,
       usr.datum
FROM users_table
WHERE usr.datum = (SELECT c_year_ytd FROM master_date_automation)
AND usr.user= '123456789'

Drop the table if you need to first.

Oracle - using bind variable in LIKE clause of dynamic cursor

This is a subtle one. It's often useful to start with a static statement, get that right, then convert it to dynamic SQL.

In non-dynamic SQL we might do it like this:

 SELECT column1, column2 
 FROM my_table 
 WHERE column1 LIKE '%' || local_var || '%'; 

The dynamic equivalent is

query := 'SELECT column1, column2 
          FROM my_table 
          WHERE column1 LIKE ''%''||:bv1||''%'' ';

---

Related Topics