Forum Stats

  • 3,768,303 Users
  • 2,252,772 Discussions
  • 7,874,521 Comments

Discussions

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

rjsosi
rjsosi Member Posts: 221 Bronze Badge

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!

Tagged:

Best Answer

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    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

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 640 Silver Trophy
    edited Aug 19, 2021 2:56PM

    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
    Jan Gorkow Member Posts: 133 Gold Badge

    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

    rjsosi
  • BluShadow
    BluShadow Member, Moderator Posts: 41,474 Red Diamond

    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
  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge

    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
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    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
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    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
  • rjsosi
    rjsosi Member Posts: 221 Bronze Badge

    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
    Frank Kulash Member, Moderator Posts: 41,205 Red Diamond

    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.