¿Por qué no puedo usar variables de vinculación en sentencias DDL / SCL en SQL dynamic?

Estoy tratando de ejecutar un comando SQL dentro de SQL dynamic con variables de vinculación:

-- this procedure is a part of PL/SQL package Test_Pkg PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR = :cal' USING IN calendar_; END Set_Nls_Calendar; 

Luego, en el lado del cliente, bash invocar el procedimiento:

 Test_Pkg.Set_Nls_Calendar('Thai Buddha'); 

Pero esto me da ORA-02248: invalid option for ALTER SESSION .

Y mi pregunta es: ¿por qué no puedo usar variables de vinculación en las sentencias DDL / SCL en SQL dynamic?

Las variables de vinculación no están permitidas en las sentencias DDL. Entonces las siguientes declaraciones causarán errores:

  • Ejemplo # 1: instrucción DDL . Causa ORA-01027: variables de vinculación no permitidas para las operaciones de definición de datos

     EXECUTE IMMEDIATE 'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )' USING 42; 
  • Ejemplo # 2: instrucción DDL . Causará ORA-00904: identificador inválido

     EXECUTE IMMEDIATE 'CREATE TABLE dummy_table ( :col_name NUMBER )' USING var_col_name; 
  • Ejemplo # 3: statement SCL . Causa ORA-02248: opción no válida para ALTER SESSION

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

Problema

Para entender por qué sucede esto, necesitamos ver cómo se procesan las sentencias SQL dinámicas .

Normalmente, un progtwig de aplicación solicita al usuario el texto de una statement de SQL y los valores de las variables de host utilizadas en la instrucción. Entonces Oracle analiza la statement SQL. Es decir, Oracle examina la statement de SQL para asegurarse de que sigue las reglas de syntax y se refiere a los objetos de base de datos válidos. El análisis también implica verificar los derechos de acceso a la base de datos 1 , reservar los recursos necesarios y encontrar la ruta de acceso óptima.

1 Énfasis añadido por el que responde

Tenga en cuenta que el paso de análisis ocurre antes de vincular las variables a la statement dinámica. Si examina los cuatro ejemplos anteriores, se dará cuenta de que no hay forma de que el analizador garantice la validez sintáctica de estas sentencias de SQL dynamic sin conocer los valores de las variables de vinculación.

  • Ejemplo n. ° 1 : El analizador no puede decir si el valor de vinculación será válido. ¿Qué ocurre si en lugar de USING 42 , el progtwigdor escribe USING 'forty-two' ?
  • Ejemplo # 2 : El analizador no puede decir si :col_name sería un nombre de columna válido. ¿Qué ocurre si el nombre de la columna dependiente es 'identifier_that_well_exceeds_thirty_character_identifier_limit' ?
  • Ejemplo n. ° 3 : los valores para NLS_CALENDAR están integrados en constantes (para una versión de Oracle dada?). El analizador no puede decir si la variable enlazada tendrá un valor válido.

Entonces, la respuesta es que no puede vincular elementos de esquema como nombres de tabla, nombres de columna en SQL dynamic. Tampoco puedes unir las constantes integradas .


Solución

La única forma de lograr referencias de elementos / constantes de esquema dinámicamente es utilizar concatenación de cadenas en sentencias SQL dinámicas.

  • Ejemplo 1:

     EXECUTE IMMEDIATE 'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT ' || to_char(42) || ')'; 
  • Ejemplo # 2:

     EXECUTE IMMEDIATE 'CREATE TABLE dummy_table (' || var_col_name || ' NUMBER )'; 
  • Ejemplo n. ° 3:

     EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_CALENDAR = ''' || var_calendar_option || '''';