Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

Answer

The statement that you cannot use bind variables in DDL/SCL commands is not the whole truth. Actually there are cases you cannot use bind variables in DML statements as well.
Examples

Sure, bind variables are not allowed in DDL statements. So following statements will cause errors:

    Example #1: DDL statement. Will cause ORA-01027: bind variables not allowed for data definition operations

    EXECUTE IMMEDIATE
      'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )'
      USING 42;

    Example #2: DDL statement. Will cause ORA-00904: : invalid identifier

    EXECUTE IMMEDIATE
      'CREATE TABLE dummy_table ( :col_name NUMBER )'
      USING var_col_name;

    Example #3: SCL statement. Will cause ORA-02248: invalid option for ALTER SESSION

    EXECUTE IMMEDIATE
      'ALTER SESSION SET NLS_CALENDAR = :cal'
      USING var_calendar_option;

    Example #4: Even this DML statement will cause an error. ORA-00903: invalid table name. (If you thought it's a nice way to count total records from a given table, sorry, bad luck).

    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :tab_name'
       INTO var_count USING IN tab_name;

All plsql Questions

Ask your interview questions on plsql

Write Your comment or Questions if you want the answers on plsql from plsql Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---