Bind variable in Dynamic SQL string is not binding in Anonymous block

rjsosi · Aug 19, 2021 2:41PM

Hi,

We're using Oracle 12c (PL/SQL Release 12.1.0.2.0 - Production)

I'm testing Dynamic SQL in an Anonymous block and for some reason when I replace the table names with bind variables the code shows and error:

Here's the code:

set serveroutput on
DECLARE
    
    v_sql_stmt VARCHAR2(1000):= NULL;
    v_exists  NUMBER;
    v_table_name_tmp Varchar2(100);
    v_table_name Varchar2(100);
    
BEGIN
    v_table_name_tmp := 'SLD_LOAN_CLTRL_EAD_TMP';
    v_table_name := 'SLD_LOAN_CLTRL_EAD';
    v_exists := 0;
             
        --SELECT 1 into v_exists FROM DBA_TABLES WHERE TABLE_NAME= 'SLD_LOAN_CLTRL_EAD_TMP';
        
    v_sql_stmt :=         
        'SELECT 1 
        FROM DBA_TABLES 
        WHERE TABLE_NAME = :b_table_name_tmp';--sld_loan_cltrl_ead_temp


    EXECUTE IMMEDIATE v_sql_stmt
       INTO v_exists USING v_table_name_tmp;   
                     
      dbms_output.put_line('We made it to here!');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
    
    v_sql_stmt :=NULL;
                                 
    v_sql_stmt :=         
        'CREATE TABLE :b_table_name_tmp 
        AS 
        SELECT * from :b_table_name 
        WHERE 1=2';                         
     
    EXECUTE IMMEDIATE v_sql_stmt
             USING v_table_name_tmp, v_table_name;                              
     
   WHEN OTHERS THEN
     
      dbms_output.put_line('Error in CREATE STATEMENT: '||SQLERRM);


END;

The DBMS OUTPUT from the Exceptions block gives this:

We made it to here!

Error in CREATE STATEMENT: ORA-00904: "SLD_LOAN_CLTRL_EAD_TMP": invalid identifier

The error in the procedure is this:

Error at line 2
ORA-00903: invalid table name
ORA-06512: at line 35
ORA-01403: no data found
Script Terminated on line 2 of \\NAHomeWFS02\Home03$\rs818\My Documents\USB 4G\HFIT\Oracle Utiliy SQL\Working Dynamic SQL with Bind variables.sql.

I've compared this with a very similar block of Dynamic SQL running in a procedure and I found no difference. My only thought is that bind variables may only work when the Dynamic SQL is called form a procedure and not an Anonymous block.

I'm curious if I missed something. Can anyone confirm this? Otherwise I'm not sure why the above block won't run when I introduce the bind variables.

Thanks!

Jan Gorkow · Aug 19, 2021 4:13PM

Hi rjsosi,

sorry, there was an error in the execute immediate call. There you must not provide the bind variables:

DECLARE
 v_sql_stmt   VARCHAR2 (1000) := NULL;
 v_exists   NUMBER;
 v_table_name_tmp VARCHAR2 (100);
 v_table_name  VARCHAR2 (100);
BEGIN
 v_table_name_tmp := 'SLD_LOAN_CLTRL_EAD_TMP';
 v_table_name := 'SLD_LOAN_CLTRL_EAD';
 v_exists := 0;

 --SELECT 1 into v_exists FROM DBA_TABLES WHERE TABLE_NAME= 'SLD_LOAN_CLTRL_EAD_TMP';

 v_sql_stmt := 'SELECT 1 
  FROM DBA_TABLES 
  WHERE TABLE_NAME = :b_table_name_tmp';  --sld_loan_cltrl_ead_temp


 EXECUTE IMMEDIATE v_sql_stmt
  INTO v_exists
  USING v_table_name_tmp;

 dbms_output.put_line ('We made it to here!');
EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
  v_sql_stmt := NULL;

  v_sql_stmt :=
   'CREATE TABLE ' || v_table_name_tmp || ' 
  AS 
  SELECT * from ' || v_table_name || ' 
  WHERE 1=2';

  EXECUTE IMMEDIATE v_sql_stmt;
 WHEN OTHERS
 THEN
  dbms_output.put_line ('Error in CREATE STATEMENT: ' || SQLERRM);
END;
/

Best regards

Jan

User_H3J7U · Aug 19, 2021 2:55PM

Read sql language reference about expressions and pl/sql language reference about dynamic sql.

First dynamic sql can be done statically. And you still do need first query. Simple try to create the table.

Jan Gorkow · Aug 19, 2021 2:59PM

Hi rjsosi,

it is not possible to use binding for identifiers in ddl statements. You have to construct the create tabvle statement like this:

DECLARE
  v_sql_stmt     VARCHAR2 (1000) := NULL;
  v_exists      NUMBER;
  v_table_name_tmp  VARCHAR2 (100);
  v_table_name    VARCHAR2 (100);
BEGIN
  v_table_name_tmp := 'SLD_LOAN_CLTRL_EAD_TMP';
  v_table_name := 'SLD_LOAN_CLTRL_EAD';
  v_exists := 0;

  --SELECT 1 into v_exists FROM DBA_TABLES WHERE TABLE_NAME= 'SLD_LOAN_CLTRL_EAD_TMP';

  v_sql_stmt := 'SELECT 1 
    FROM DBA_TABLES 
    WHERE TABLE_NAME = :b_table_name_tmp';    --sld_loan_cltrl_ead_temp


  EXECUTE IMMEDIATE v_sql_stmt
   INTO v_exists
   USING v_table_name_tmp;

  dbms_output.put_line ('We made it to here!');
EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
   v_sql_stmt := NULL;

   v_sql_stmt :=
     'CREATE TABLE ' || v_table_name_tmp || ' 
    AS 
    SELECT * from ' || v_table_name || ' 
    WHERE 1=2';

   EXECUTE IMMEDIATE v_sql_stmt
     USING v_table_name_tmp, v_table_name;
  WHEN OTHERS
  THEN
   dbms_output.put_line ('Error in CREATE STATEMENT: ' || SQLERRM);
END;
/

Best regards

Jan

BluShadow · Aug 19, 2021 3:26PM

Simply put, bind variables are for 'values' not for 'objects', so you can't specify table names with a bind variable.

Oracle isn't like T-SQL, so it's not good practice, nor usual, to create temporary tables on-the-fly in runtime code. Good design would mean that you create the temporary tables (commonly a global temporary table) up front as part of your database design. Those temporary tables are session specific, so can be used by any session without interfering with the data from other sessions.

rjsosi · Aug 19, 2021 3:50PM

Hi Jan Gorkow,

It's still having an issue with that statement. I replaced the Create statement with yours and When I ran the blaock I got the following error.

Error at line 1

ORA-06546: DDL statement is executed in an illegal context

ORA-06512: at line 34

ORA-01403: no data found

Here's the phrase It's having an issue with.

EXCEPTION
  WHEN NO_DATA_FOUND
  THEN
   v_sql_stmt := NULL;


   v_sql_stmt :=
     'CREATE TABLE ' || v_table_name_tmp || ' 
    AS 
    SELECT * from ' || v_table_name || ' 
    WHERE 1=2';


   EXECUTE IMMEDIATE v_sql_stmt
     USING v_table_name_tmp, v_table_name;

I changed no other part of the code.

Solomon Yakobson · Aug 19, 2021 4:07PM

Meditate over:

SQL> desc test
ERROR:
ORA-04043: object test does not exist

SQL> declare
  2      v_dummy varchar2(10) := 'XYZ';
  3  begin
  4      execute immediate 'create table test as select * from dual' using v_dummy;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06546: DDL statement is executed in an illegal context
ORA-06512: at line 4

SQL> declare
  2  --    v_dummy varchar2(10) := 'XYZ';
  3  begin
  4      execute immediate 'create table test as select * from dual'; -- using v_dummy;
  5  end;
  6  /


PL/SQL procedure successfully completed.


SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)


SQL>

SY.

Frank Kulash · Aug 19, 2021 4:09PM

Hi, @rjsosi

Lose the USING clause

 v_sql_stmt :=
     'CREATE TABLE ' || v_table_name_tmp || ' 
    AS 
    SELECT * from ' || v_table_name || ' 
    WHERE 1=2';


   EXECUTE IMMEDIATE v_sql_stmt;

After first statment above, v_sql_stmt is exactly what you want to execute; you don't need to replace anything in it, so you don't need a USING clause.

Blushadow made a good point. Creating tables in PL/SQL is usually a really bad idea.

Jan Gorkow · Aug 19, 2021 4:13PM

Hi rjsosi,

sorry, there was an error in the execute immediate call. There you must not provide the bind variables:

DECLARE
 v_sql_stmt   VARCHAR2 (1000) := NULL;
 v_exists   NUMBER;
 v_table_name_tmp VARCHAR2 (100);
 v_table_name  VARCHAR2 (100);
BEGIN
 v_table_name_tmp := 'SLD_LOAN_CLTRL_EAD_TMP';
 v_table_name := 'SLD_LOAN_CLTRL_EAD';
 v_exists := 0;

 --SELECT 1 into v_exists FROM DBA_TABLES WHERE TABLE_NAME= 'SLD_LOAN_CLTRL_EAD_TMP';

 v_sql_stmt := 'SELECT 1 
  FROM DBA_TABLES 
  WHERE TABLE_NAME = :b_table_name_tmp';  --sld_loan_cltrl_ead_temp


 EXECUTE IMMEDIATE v_sql_stmt
  INTO v_exists
  USING v_table_name_tmp;

 dbms_output.put_line ('We made it to here!');
EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
  v_sql_stmt := NULL;

  v_sql_stmt :=
   'CREATE TABLE ' || v_table_name_tmp || ' 
  AS 
  SELECT * from ' || v_table_name || ' 
  WHERE 1=2';

  EXECUTE IMMEDIATE v_sql_stmt;
 WHEN OTHERS
 THEN
  dbms_output.put_line ('Error in CREATE STATEMENT: ' || SQLERRM);
END;
/

Best regards

Jan

rjsosi · Aug 19, 2021 7:23PM

Finally when I run this in an Anonymous block it runs fine now.

However when I try to run the exact same code in a procedure it give me an error:

[Warning] ORA-24344: success with compilation error

13/37 PL/SQL: ORA-00942: table or view does not exist

13/9 PL/SQL: SQL Statement ignored

(1: 0): Warning: compiled but with compilation errors

It was referring to the call to DBA_TABLES. I changed the call from DBA_TABLES to ALL_TABLES and it ran find. must have been a permissions thing. Everything else works now. The last time I seriously worked with Dynamic SQL was too long ago so it escaped me that you can only use bi9nd variables for variable values and not objects. Thanks Again!

Frank Kulash · Aug 19, 2021 8:40PM

Hi,

I changed the call from DBA_TABLES to ALL_TABLES and it ran find.

I think you mean "it ran fine" (with an E at the end).

must have been a permissions thing.

Exactly! In stored procedures with AUTHID DEFINER (which is the default), roles don't count. All necessary privileges must be granted directly to the procedure owner, or to PUBLIC, and not merely to some role that the owner has. It sounds like you (the definer) have privileges on DBA_TABLES only through a role. You can use DBA_TABLES in an anonymous block, because anonymous blocks are not stored procedures. You can use ALL_TABLES in a strored procedure (almost certainly) because PUBLIC has been granted the necessary privileges.

Forum Stats

Quick Links

Categories

Bind variable in Dynamic SQL string is not binding in Anonymous block

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Bind variable in Dynamic SQL string is not binding in Anonymous block

Best Answer

Answers