Erro em DDL/SCL com bind ? Como funciona o Dynamic SQL

Erro em DDL/SCL com bind ? Como funciona o Dynamic SQL

Olá pessoal !

Me chamaram para dar uma olhada em uma rotina que deveria criar uma tabela baseada em um SQL. A rotina executava um CREATE TABLE…AS SELECT… e passava alguns parâmetros por bind via Dynamic SQL.

Ao avaliar essa situação, não poderia dar uma resposta menos óbvia a maioria dos desenvolvedores PL/SQL. “Abordagem errada, não é possível fazer isso usando bind variable !”.

Depois de receber alguns olhares incrédulos, fui direto aos argumentos práticos !

CREATE TABLE teste
(
 codigo     NUMBER,
 descricao  VARCHAR2(2000)
)
/

INSERT INTO teste VALUES(1,'LINHA 1')
/

INSERT INTO teste VALUES(2,'LINHA 2')
/

Agora criemos um bloco anônimo para o primeiro teste:

DECLARE
   vCodigo NUMBER := 1;

BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE teste_2 AS SELECT * FROM teste WHERE codigo = :cod'
   USING IN vCodigo;

EXCEPTION
   WHEN OTHERS THEN   
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);

END;

Executando…

SQL > ORA-01027: bind variables not allowed for data definition operations

O erro já era esperado ! Vamos utilizar uma abordagem com DBMS_SQL, que facilitará a explicação do porquê disso acontecer.

DECLARE
   cursorID INTEGER;
   execStat INTEGER;
   vCodigo  NUMBER := 1;
   vSQL     VARCHAR2(2000) := 'CREATE TABLE teste_2 AS SELECT * FROM teste WHERE codigo = :cod'; 

BEGIN
   cursorID := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE
      (
       cursorID
      ,vSQL
      ,DBMS_SQL.NATIVE
      );

   DBMS_SQL.BIND_VARIABLE(cursorID,':cod',vCodigo);

   execStat := DBMS_SQL.EXECUTE(cursorID);  

   DBMS_SQL.CLOSE_CURSOR(cursorID);

EXCEPTION
   WHEN OTHERS THEN   
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
      -- Não se esqueça de fechar sempre o seu cursor 🙂
      DBMS_SQL.CLOSE_CURSOR(cursorID); 

END;

Executando…

SQL > ORA-01027: bind variables not allowed for data definition operations

E o mesmo erro se apresenta !!! Agora vamos verificar em que ponto ocorre o estouro da exception.

DECLARE
   cursorID INTEGER;
   execStat INTEGER;
   vCodigo  NUMBER := 1;
   vSQL     VARCHAR2(2000) := 'CREATE TABLE teste_2 AS SELECT * FROM teste WHERE codigo = :cod';
   vStep    VARCHAR2(2000);

BEGIN
   vStep := 'Open Cursor';
   cursorID := DBMS_SQL.OPEN_CURSOR;
   vStep := 'Parse';

   DBMS_SQL.PARSE
      (
       cursorID
      ,vSQL
      ,DBMS_SQL.NATIVE
      );

   vStep := 'Bind';
   DBMS_SQL.BIND_VARIABLE(cursorID,':cod',vCodigo);
   vStep := 'Execute';
   execStat := DBMS_SQL.EXECUTE(cursorID);
   vStep := 'Close Cursor';       
   DBMS_SQL.CLOSE_CURSOR(cursorID);
   vStep := 'Success !';
   DBMS_OUTPUT.PUT_LINE(vStep);

EXCEPTION
   WHEN OTHERS THEN   
      DBMS_OUTPUT.PUT_LINE(vStep || ' - ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
      -- Não se esqueça de fechar sempre o seu cursor 🙂
      DBMS_SQL.CLOSE_CURSOR(cursorID); 

END;

Executando…

SQL > Parse - ORA-01027: bind variables not allowed for data definition operations

E o vencedor foi…..o PARSER !

A explicação é simples.  As declarações DDL, SCL e SQL são verificadas pelo PARSER. No caso das DDL/SCL, o PARSER simplesmente não checa informações que ele não sabe se serão válidas. Os binds poderiam simplesmente conter dados incompatíveis, invalidando a operação no caso de DDL/SCL e executando sem problemas em SQL.

Observe que não falamos de um erro de sintaxe, que não existe no exemplo acima.

Vamos fazer um teste com uma declaração SQL, para isso, substitua o conteúdo da variável vCodigo e o seu datatatype do script acima:

vCodigo DATE := SYSDATE;

Executando…

SQL > Execute - ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Perceba que o PARSER não identificou erro algum (não valida o bind), e a exception estourou apenas no EXECUTE.

Mudemos uma vez mais…

vCodigo VARCHAR2(2000) := 'X';

vSQL VARCHAR2(2000) :='SELECT * FROM teste WHERE codigo = :cod';

Irei propositalmente gerar um INVALID NUMBER, apenas para vermos onde a EXCEPTION irá estourar.

SQL > Success !

Como assim Success ?!?!?! Não deveria dar erro ao atribuir STRING em um campo NUMBER ?
Nos meus testes, para datatypes que possuem conversão implícita, não são gerados erros na execução !!! Isso inclui CHAR,VARCHAR2,INT e NUMBER. Como o campo código é do tipo NUMBER, se encaixa nessa situação.

Mudemos uma última vez para verificar a validação…

vSQL VARCHAR2(2000) := 'SELECT * FR teste WHERE codigo = :cod';

SQL > Parse - ORA-00923: FROM keyword not found where expected

Veja o PARSER fazendo o seu trabalho e verificando a sintaxe da declaração !

Após esses testes, podemos concluir que:

• DDL, SCL e SQL são validados no PARSER (menos os binds);
• Dynamic SQL funciona de modos distintos para declarações DDL/SCL e para SQL;
• Binds de campos SQL de datatypes com conversão implícitas devem ser utilizados com cuidado;
• Use Dynamic SQL com parcimônia ! Cuidado com os SQL Injections ! ☺

Para mais detalhes sobre o funcionamento de Dynamic SQL, acesse:

• How Dynamic SQL Statements Are Processed

Um grande abraço