Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to execute an SQL command within dynamic SQL with bind variables:

-- this procedure is a part of PL/SQL package Test_Pkg
PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION
      SET NLS_CALENDAR = :cal'
      USING IN calendar_;
END Set_Nls_Calendar;
Then on the client side, I am trying to invoke the procedure:
Test_Pkg.Set_Nls_Calendar('Thai Buddha');
But this get's me ORA-02248: invalid option for ALTER SESSION.
And my question is: Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?
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;
Problem
To understand why this happens, we need to look at How Dynamic SQL Statements Are Processed.
  
Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. Then Oracle parses the SQL statement. That is, Oracle examines the SQL statement to make sure it follows syntax rules and refers to valid database objects. Parsing also involves checking database access rights1, reserving needed resources, and finding the optimal access path.
  
1 Emphasis added by answerer
Note that parsing step happens before binding any variables to the dynamic statement. If you examine the above four examples, you will realize that there is no way for the parser to guarantee the syntactical validity of these dynamic SQL statements without knowing the values for bind variables.
Example #1: Parser cannot tell if the bind value will be valid. What if instead of USING 42, programmer wrote USING 'forty-two'?
Example #2: Parser cannot tell if :col_name would be a valid column name. What if the bound column name was 'identifier_that_well_exceeds_thirty_character_identifier_limit'?
Example #3: Values for NLS_CALENDAR are built in constants (for a given Oracle version?). Parser cannot tell if the bound variable will have a valid value.
So the answer is that you cannot bind schema elements such as table names, column names in dynamic SQL. Nor you can bind built in constants.
Solution
The only way to achieve referencing schema elements/constants dynamically is to use string concatenation in dynamic SQL statements.
Example #1:
EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT ' || to_char(42) || ')';
Example #2:
EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table (' || var_col_name || ' NUMBER )';
Example #3:
EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = ''' || var_calendar_option || '''';
                I need to do ALTER USER with user supplied password, but string concatenation will introduce SQL injection attack.
– Ray Cheng
                May 10, 2019 at 16:11
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.