oracle - query - pl sql ventajas

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

Question

Estoy tratando de ejecutar un comando SQL dentro de SQL dinámico 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, intento 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 dinámico?

Answer 1

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: declaración 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 programa de aplicación solicita al usuario el texto de una declaración de SQL y los valores de las variables de host utilizadas en la instrucción. Entonces Oracle analiza la declaración SQL. Es decir, Oracle examina la declaración de SQL para asegurarse de que sigue las reglas de sintaxis 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 ¹ , reservar los recursos necesarios y encontrar la ruta de acceso óptima.

¹ _{Énfasis añadido por el que responde}

Tenga en cuenta que el paso de análisis ocurre antes de vincular las variables a la declaración 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 dinámico 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 programador 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 # 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 dinámico. 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 || '''''''';