SQL Dinamico

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

Pessoal, eu tenho uma query dinamica que é armazenada na variavel v_sql.
Essa query é um select que pode retornar uma ou dezenas de colunas.
Acontece que na clausula FETCH abaixo eu teria que variar as variaveis na clausula INTO.
Abaixo tenho quatro variaveis que armazenam quatro colunas vindas do select dinamico,
mas se esse select dinamico me retornar somente uma coluna? ou se me retornar vinte colunas?

Como eu posso resolver?

Selecionar tudo

       OPEN l_cursor FOR v_sql;
       LOOP  

       FETCH l_cursor INTO aux1, aux2, aux3, aux4; --, aux5;
       EXIT WHEN l_cursor%notfound;
ballboas
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 98
Registrado em: Qui, 02 Ago 2007 3:06 pm
Localização: sp
Érico Balboa

A pergunta mais cretina é aquela que não é feita

Boa Tarde cleberz,

Não sei se a pergunta ainda é pertinente, mas como ficou sem resposta e eu precisei de algo similar, estou postando um script de como resolver este problema utilizando a package dbms_sql. Qualquer dúvida estamos aí. Abrs.

Selecionar tudo

declare 
  
  --Variável do Tipo dbms_sql.desc_tab
  v_cols dbms_sql.desc_tab;
    
  v_qtd_cols number;
  v_cursor   pls_integer;  
  
  v_Sql varchar2(4000);
  
begin
  -- Test statements here
  v_Sql := 'select (sysdate) hoje,
                   (sysdate + 1) amanha,
                   1       um,
                   2       dois
              from dual ';

   --Abre o Cursor
   v_cursor := dbms_sql.open_cursor;
   
   --Faz um Parse do Select Dinamico
   dbms_sql.parse(v_cursor, v_Sql, dbms_sql.native);
   
   --Utiliza a Proc dbms_sql.describe_columns para obter informações do Cursor Dinamico
   dbms_sql.describe_columns(c       => v_cursor,   --Cursor
                             col_cnt => v_qtd_cols, --Quantidade de Colunas
                             desc_t  => v_cols);    --Informações sobre as Colunas

   /*----------------------------------------------------------------------------
               COLUNAS DISPONÍVEIS NO TYPE v_cols (dbms_sql.desc_tab)
      
   col_type	            BINARY_INTEGER	Type of column described
   col_max_len	        BINARY_INTEGER	Maximum length of column value
   col_name	            VARCHAR2(32)	  Name of the column
   col_name_len	        BINARY_INTEGER	Length of the column name
   col_schema_name	    VARCHAR2(32)	  Name of column type schema if an object type
   col_schema_name_len	BINARY_INTEGER	Length of schema name
   col_precision	      BINARY_INTEGER	Precision of column if a number
   col_scale	          BINARY_INTEGER	Scale of column if a number
   col_charsetid	      BINARY_INTEGER	ID of character set
   col_charsetform	    BINARY_INTEGER	Character set form
   col_null_ok	        BOOLEAN	        TRUE if column can be NULL   
   ------------------------------------------------------------------------------*/
   
   --Printa a quantidade de colunas
   dbms_output.put_line(v_qtd_cols);
   
   --Loop com a quantidade de colunas printando o nome de cada coluna
   for n in 1..v_qtd_cols loop
       dbms_output.put_line(v_cols(n).col_name);
   end loop;
  
end;

DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Dando apenas um exemplo bem resumido , para ajudar, pode fazer algo como:

Selecionar tudo

DECLARE

l_sql VARCHAR2(400) := 'select t.code_id , t.description from xxx.dic_ind_codes t';
ln_code_id NUMBER;
lv_DESC VARCHAR2(30);
TYPE cursor_ref IS REF CURSOR; 
c1 cursor_ref;

BEGIN

l_sql :=  l_sql || ' where t.code_id < 10';

open c1 for l_sql;
      loop
          fetch c1 into ln_code_id,lv_DESC;
          exit when c1%notfound;
                   dbms_output.put_line(ln_code_id || ' - ' || lv_DESC);
      end loop;
 close c1; 

END;
ballboas
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 98
Registrado em: Qui, 02 Ago 2007 3:06 pm
Localização: sp
Érico Balboa

A pergunta mais cretina é aquela que não é feita

Boa Tarde Noctifero,

No seu exemplo você explicita o número de colunas, a dúvida é quando não se tem esta informação em tempo de execução.
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Ok ballboas, realmente tinha mal compreendido a questão.

Realmente se faz necessário o uso do "DBMS_SQL" nesses caso.
Aproveitando o seu trecho de código segue o seguinte que informa o número de colunas, os nomes delas, e o resultado. Tudo como se fosse um CSV, separado por ponto e vírgula.

Selecionar tudo

DECLARE

  v_cursor  NUMBER;
  v_col_cnt INTEGER;
  rec_tab   dbms_sql.desc_tab;
  p_sql     LONG;

  v_ind      NUMBER;
  v_line     VARCHAR2(250);
  v_header   VARCHAR2(250);
  v_result   VARCHAR2(4000);

BEGIN
  p_sql := 'select (sysdate) hoje,
                   (sysdate + 1) amanha,
                   1       um,
                   2       dois
              from dual
            UNION ALL              
            select (sysdate - 23) hoje,
                   (sysdate + 19) amanha,
                   71       um,
                   62       dois
              from dual ';

  dbms_output.enable(NULL); --infinito

  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, p_sql, dbms_sql.native);
  dbms_sql.describe_columns(v_cursor, v_col_cnt, rec_tab);

  
  --Printa a quantidade de colunas
   dbms_output.put_line('***TOTAL COLUNAS***:' || v_col_cnt);
   
   --Loop com a quantidade de colunas printando o nome de cada coluna
   for n in 1..v_col_cnt loop
       v_header := v_header || rec_tab(n).col_name || ';';
   end loop;
   --Exibe cabeçalho
   v_header := substr(v_header, 1, length(v_header) -1);
   dbms_output.put_line(v_header);

  /***   DEFINE COLUNAS   ***/
  FOR v_pos IN 1 .. rec_tab.LAST
  LOOP
    v_line := rec_tab(v_pos).col_name;
    dbms_sql.define_column(v_cursor, v_pos, v_line, 250);
  END LOOP;
  
  /* Loop nas linhas de retorno da consulta */
  v_ind := dbms_sql.EXECUTE(v_cursor);
  LOOP
    v_ind := dbms_sql.fetch_rows(v_cursor);
    EXIT WHEN v_ind = 0;
    /* loop para cada coluna da linha */
    FOR v_col_seq IN 1 .. rec_tab.COUNT
    LOOP
      -- PEGA O VALOR DA COLUNA
      dbms_sql.column_value(v_cursor, v_col_seq, v_line);
    
      IF v_col_seq = 1 THEN
        v_result := v_line;
      ELSE
        v_result := v_result || ';' || v_line;
      END IF;
    
    END LOOP;
    --EXIBE CADA LINHA
    dbms_output.put_line(v_result);
  END LOOP;
END;
cleberz
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 107
Registrado em: Ter, 15 Nov 2011 8:42 am

ainda continua sem dar certo.
ballboas
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 98
Registrado em: Qui, 02 Ago 2007 3:06 pm
Localização: sp
Érico Balboa

A pergunta mais cretina é aquela que não é feita

Acredito que o último exemplo montado pelo Noctifero, onde ele trabalha com as colunas e seus valores te atendem, basta alterar o exemplo para a sua necessidade.

Se você quiser detalhar a sua necessidade, podemos te ajudar para utilizar o exemplo.
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 14 visitantes