Objeto para pipelined com colunas dinâmicas

[raphaelias]

Boa tarde galera,

Estou usando pipelined para retornar resultados de queries que estou montando aqui e funciona tudo certinho com um tipo objeto(ROWS) e um tipo tabela(TABLE OF ROWS)

Porém, como fazer um pipelined para selects quando as colunas são dinâmicas? Vou mostrar um exemplo:

Selecionar tudo

Function  FN_RESULT_ACUMULADO 
  ( unidade IN VARCHAR,
  anoInicial IN NUMBER,
  anoFinal IN NUMBER)
  RETURN NEWAGE.TP_TABLE_RES_ACUMULADO PIPELINED
IS
    str VARCHAR2(3000);
    strGeral VARCHAR2(32000);
    p_rc_row TP_ROW_RES_ACUMULADO := TP_ROW_RES_ACUMULADO(NULL,NULL,NULL,NULL,NULL,NULL);
    TYPE cur_type IS REF CURSOR;
    c_str cur_type;
BEGIN
    str := 'SELECT COLUNA1, COLUNA2';
    FOR i IN anoInicial..anoFinal
    LOOP
        str := str || ' ,SUM(CASE WHEN SUBSTR(X.MESANO,-4,4)='''||i||
        ''' THEN X.VALOR) ELSE NULL END) AS A'||i;       
    END LOOP;

    ' FROM TABELA'||
        
    OPEN c_str FOR str;
    LOOP        
        FETCH c_str INTO 
           p_rc_row.COLUNA1,
           p_rc_row.COLUNA2, 
           p_rc_row.a1,  p_rc_row.a2, ....--E AGORA??????????????????
        EXIT WHEN c_str%NOTFOUND; 
        PIPE ROW(p_rc_row);
    END LOOP;
    
    RETURN;
END;

Usando o Type de Rows ela também teria de ser dinâmica. E agora, o que faço?

Valeu pela atenção!!

[Trevisolli]

Brother,

Pelo que entendi, você precisa pegar o nome das colunas da tabela dinâmica?
Caso positivo, tem como abrir teu cursor com o nome da tabela e buscar essa informação na user_cons_columns.
qualquer coisa, manda ai.

[raphaelias]

Faala Trevisolli,

Mais ou menos cara..a situação é a seguinte..

Estou montando relatórios bem complexos com colunas dinâmicas via function. então se a pessoa escolher do ano 2004 à 2008 além das colunas fixas vão ter as colunas 2004,2005,2006,2007 e 2008.

Lá na minha aplicação web, leio o resultado da function pelo comando

Selecionar tudo

 SELECT * FROM TABLE(CAST(FUNCAO AS TYPE_TABLE))

Para isso, utilizo 2 tipos..um de OBJECT que tem as colunas e outro TABLE OF 1º tipo.. Mas as colunas são fixas!

Como faço para alimentar esse objeto sendo que as colunas variam, ou algum outro jeito para ler o resultado em forma de select!

Não sei se fui claro mas valeu pela força!!

[Julian Campagnoli]

E aeee raphael com vai ...

Andei dando uma olhada nas PL-TABLE, TABELAS ANINHADAS, etc ...

Vi seu exemplo usando PIPELINED, da hora esse comando rsrsrssrssrsrs

Veja um exemplo que criei:

Selecionar tudo

create or replace
TYPE TesteType AS OBJECT (
COLN1      VARCHAR2(4),
COLN2      VARCHAR2(1),
NUMB       NUMBER(10));

create or replace
TYPE TesteTypeSet AS TABLE OF TesteType

 -- CRIAR CORPO DA PKG
 CREATE OR REPLACE PACKAGE refcur_pkg IS

TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;

END refcur_pkg;

Criar a função:

Selecionar tudo

create or replace
FUNCTION FN_TESTE_PIPELINED(
p refcur_pkg.refcur_t)
RETURN TesteTypeSet PIPELINED IS

out_rec  TesteType := TesteType(NULL,NULL,NULL);
in_rec   p%ROWTYPE;

BEGIN
  LOOP
    FETCH p INTO in_rec;
    EXIT WHEN p%NOTFOUND;
    
    -- Acredito que para seu exemplo real você tera qua fazer outro LOOP
    out_rec.COLN1 := 'A';
    out_rec.COLN2 := 'A';
    out_rec.NUMB := 1111;
    PIPE ROW(out_rec);

    out_rec.COLN1 := 'B';
    out_rec.COLN2 := 'B';
    out_rec.NUMB := 11111;
    PIPE ROW(out_rec);

  END LOOP;
  CLOSE p;
  RETURN;
END FN_TESTE_PIPELINED;

Faça o SELECT:

Selecionar tudo

SELECT *
FROM TABLE(FN_TESTE_PIPELINED(CURSOR(SELECT * FROM DUAL)));

Brother, teste o exemplo. Aqui ficou beleza !!!!!!

você viu que tive que tive que fazer um esquema com a Table DUAL rrsssrsrrs para esconder os campos, pois você ira trazer campos varias tabelas.

Se for isso mesmo basta você fazer a adaptação no seu código. Acredito que devera acrascentar um ou outro FOR para pegar seus dados.

Abração,

[Julian Campagnoli]

Putz desculpa, altere o corpo da PKG por favor:

Selecionar tudo

 -- CRIAR CORPO DA PKG 
 CREATE OR REPLACE PACKAGE refcur_pkg IS 

TYPE refcur_t IS REF CURSOR RETURN DUAL%ROWTYPE; 

END refcur_pkg; 

Abração

[raphaelias]

Faaaala Julian..valeu mais uma vez pela ajuda!!

Então, minha dúvida não é bem essa, é a seguinte..

No exemplo você criou o TesteType que tem as colunas do meu retorno..maravilha, mas e quando essas colunas são dinâmicas?

Por exemplo..minhas colunas vão ser os campos de uma select qualquer..digamos que da tabela TIPO

Então meu resultado vai ser:

Selecionar tudo

COLUNA1 | COLUNA 2 | TIPO1 | TIPO2 |...TIPO N

Se fôssemos pegar com anos..digamos que seria de 2005 ao ano atual..

Selecionar tudo

COLUNA1 | COLUNA 2 | 2005 | 2006 | 2007 | 2008

No ano que vem mais uma coluna apareceria no resultado!

Então como fazer com meu objeto TesteType já que ele tem colunas fixas e não consigo alterá-lo em tempo de execução em uma procedure?

Isso entra naquela questão que tinha feito em meu 1º post..já consegui 50% da solução que é os resultados com colunas fixas, agora só falta os outros 50% que são resultados com colunas dinâmicas.

Valeu pessoal!!!