SQL dinâmico no Oracle

Existe alguma forma de criar dinamicamente um comando SQL, tanto DML quanto DDL, no Oracle?

Sim, é possível. A partir da versão 8i podemos usar o "EXECUTE IMMEDIATE" statement para executar um comando SQL montado em tempo de execução. Essa funcionalidade foi introduzida para substituir o “DBMS_SQL package”. Veja alguns exemplos abaixo:

-- Executando comando DDL “Create”
EXECUTE IMMEDIATE 'CREATE TABLE MyZNTable (ZN_ID NUMBER, ZNDescricao Varchar2(30))';

--- executando comando DML “INSERT”
EXECUTE IMMEDIATE 'insert into MyZNTable values (1, ''ZNteste'') ’;

Esse comando é um recurso nativo do Oracle para abordagem de SQL dinâmico, apropriado para tuning pois reduz overhead, otimiza a performance do comando SQL. Considerando que, Comandos SQL criados e executados dinamicamente apresentam overhead em performance. Utilização do comando "EXECUTE IMMEDIATE" reduz este overhead e proporciona maior performance.
Através dessa técnica podemos também usar placeholders para bind.
O que é placeholder? R - Um placeholder é um identificador não declarado que vai servir como parâmetro, cujo o valor será atribuído dinamicamente. O compilador SQL vai entender o identificador porque ele estará marcado com o prefixo, o sinal de “:” (dois pontos).

DECLARE
V_NUM_CONTRATO varchar2(20) := 'ZN1000';
V_NUM_GUIA    varchar2(10) := '3000ZN';
ZnSQL_TMP VARCHAR2 (100);              
BEGIN
-- Busca ASSOCIADO, DATA DE CADASTRO, e ORIGEM
ZnSQL_TMP :=  ' SELECT
              DATA_CADASTRO, COD_ASSOCIADO, ASSOCIADO_ORIGEM
            FROM
              ASSOCIADOS
            WHERE              
              NUM_CONTRATO = :NUM_CONTRATO AND
              NUM_GUIA = :NUM_GUIA AND
              ROWNUM = 1';
  
EXECUTE IMMEDIATE ZnSQL_TMP
  USING   V_NUM_CONTRATO
        , V_NUM_GUIA;

END;

Acredito que isso pode facilitar muito o trabalho de desenvolvedor. Portanto, vejamos maiores detalhes sobre a sintaxe desse comando:

EXECUTE IMMEDIATE ZNSQL_string
[INTO {variável[, variável]...
 | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];

Analisando a sintaxe:

ZNSQL_string : Trata-se de uma string com comando SQL construído dinamicamente, conforme exemplificamos anteriormente. Essa string pode conter qualquer comando SQL sem o terminador. Comandos DML “SELECT” não devem retornar mais de uma linha. A string também pode conter placeholders (exemplo: :ZN_ASSOCIADO) e bind_arguments. Um detalhe importante, caso o camando SQL na string for uma chamada a uma procedure ou function (ou seja, uma PL/SQL) é necessário o emprego de “begin” e “end”.

INTO (palavra reservada): Indica quais variáveis (uma ou mais) cujos valores selecionados, em uma consulta, serão atribuídos. O record é um TYPE ou %ROWTYPE definido pelo usuário. Ele pode receber uma linha inteira retornada por uma consulta. Atenção, esta cláusula é opcional. É utilizada somente quando a String for uma consulta que retorne somente uma linha. Além disso, tanto a quantidade de variáveis empregadas, quanto a ordem em que elas estarão dispostas no comando, deve respeitar a quantidade e a ordem das colunas retornadas na consulta. Do mesmo modo, o tipo da variável ou registro (record) deve ser compatível com o valor que lhe será atribuído.

USING (palavra reservada): seção onde declaramos os valores atribuídos a cada argumento na bind variables da string. Não é permitido fazer bind para repassar nomes de objetos de um esquema, como nomes de tabelas ou de colunas. Também não é permitido valor booleano ou NULL. Suponha que o código exemplificado abaixo seja o trecho de uma procedure ou function:

SQL_TMP VARCHAR2 (1000);              
BEGIN

SQL_TMP :=  ' SELECT
              DATA_REALIZACAO, COD_ASSOCIADO, ORIGEM
            FROM
              ASSOCIADOS
            WHERE
              MES_REF = :MES_REF AND
              COD_CONTADOR = :COD_CONTADOR AND
              NUM_CONTRATO = :NUM_CONTRATO AND
              NUM_GUIA = :NUM_GUIA AND
              ROWNUM = 1';
BEGIN     
EXECUTE IMMEDIATE SQL_TMP
  INTO V_DATA_REALIZACAO, V_COD_ASSOCIADO, V_ORIGEM
USING V_MES_REF   
    , V_COD_CONTADOR
    , V_NUM_CONTRATO
    , V_NUM_GUIA;
EXCEPTION
WHEN no_data_found THEN
  V_DATa_ZN := NULL;
  V_NUM_ASSOCIADO_ZN := NULL;
END;

Preciso retornar várias linhas de uma consulta usando SQL dinâmico. Isso é possível?

Como mencionei anteriormente, consultas que retornem mais de uma linha não são suportadas como valor de retorno. Entretanto, existem alternativas: Ou utilizar uma tabela temporária para armazenar os registros, ou ref cursores.

Adiante, pretendo falar mais sobre o emprego do “EXECUTE IMMEDIATE”.