Select em PL/SQL Table

[raphaelias]

Bom dia pessoal,

Não consigo dar um SELECT na PL/SQL Table que minha function retorna. Segue o código da package:

Selecionar tudo

Package SMP_RESULT_CONSOLIDADO_UNIDADE IS
  
  FUNCTION SP_RESULT_CONSOLIDADO
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL)
   RETURN SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado;

  TYPE r_resultado IS RECORD(
        codigo fb_res_gerencial.codigo%TYPE
        ,descricao fb_res_gerencial.GERENCIAL%TYPE
        ,cdmaster fb_res_gerencial.codigo%TYPE
        ,dsmaster fb_res_gerencial.GERENCIAL%TYPE
        ,m1 FBG_RES_CONTA.VALOR%TYPE
        ,m2 FBG_RES_CONTA.VALOR%TYPE
        ,m3 FBG_RES_CONTA.VALOR%TYPE
        ,m4 FBG_RES_CONTA.VALOR%TYPE
        ,m5 FBG_RES_CONTA.VALOR%TYPE
        ,m6 FBG_RES_CONTA.VALOR%TYPE
        ,m7 FBG_RES_CONTA.VALOR%TYPE
        ,m8 FBG_RES_CONTA.VALOR%TYPE
        ,m9 FBG_RES_CONTA.VALOR%TYPE
        ,m10 FBG_RES_CONTA.VALOR%TYPE
        ,m11 FBG_RES_CONTA.VALOR%TYPE
        ,m12 FBG_RES_CONTA.VALOR%TYPE
        ,valor_ano FBG_RES_CONTA.VALOR%TYPE
    );
  TYPE tab_resultado IS TABLE OF r_resultado INDEX BY BINARY_INTEGER;     
END; 

Selecionar tudo

Package Body SMP_RESULT_CONSOLIDADO_UNIDADE 
IS
 
FUNCTION SP_RESULT_CONSOLIDADO
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL)
   RETURN SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado
IS
    str VARCHAR2(3000);
    p_rc SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado;
    --p_rc_row SMP_RESULT_CONSOLIDADO_UNIDADE.r_resultado;
    TYPE cur_type IS REF CURSOR;
    c_str cur_type;
    i NUMBER:=0;
    codigo      fb_res_gerencial.CODIGO%TYPE;
    descricao   fb_res_gerencial.GERENCIAL%TYPE;
    cdmaster    fb_res_gerencial.CODIGO%TYPE;
    dsmaster    fb_res_gerencial.GERENCIAL%TYPE;
    m1          FBG_RES_CONTA.VALOR%TYPE;
    m2          FBG_RES_CONTA.VALOR%TYPE;
    m3          FBG_RES_CONTA.VALOR%TYPE;
    m4          FBG_RES_CONTA.VALOR%TYPE;
    m5          FBG_RES_CONTA.VALOR%TYPE;
    m6          FBG_RES_CONTA.VALOR%TYPE;
    m7          FBG_RES_CONTA.VALOR%TYPE;
    m8          FBG_RES_CONTA.VALOR%TYPE;
    m9          FBG_RES_CONTA.VALOR%TYPE;
    m10         FBG_RES_CONTA.VALOR%TYPE;
    m11         FBG_RES_CONTA.VALOR%TYPE;
    m12         FBG_RES_CONTA.VALOR%TYPE;
    valor_ano   FBG_RES_CONTA.VALOR%TYPE;
BEGIN
    str := 'SELECT GIGANTE';
    OPEN c_str FOR str;
    LOOP
        FETCH c_str INTO codigo,descricao,cdmaster,dsmaster,m1,m2,m3,m4,m5,m6,m7,m8,m9,m10,m11,m12,valor_ano;           
        EXIT WHEN c_str%NOTFOUND;
           i := i +1;         
           p_rc(i).codigo := codigo;
           p_rc(i).descricao := descricao;
           p_rc(i).cdmaster := cdmaster;
           p_rc(i).dsmaster := dsmaster;
           p_rc(i).m1 := m1;
           p_rc(i).m2 := m2;
           p_rc(i).m3 := m3;
           p_rc(i).m4 := m4;
           p_rc(i).m5 := m5;
           p_rc(i).m6 := m6;
           p_rc(i).m7 := m7;
           p_rc(i).m8 := m8;
           p_rc(i).m9 := m9;
           p_rc(i).m10 := m10;
           p_rc(i).m11 := m11;
           p_rc(i).m12 := m12;
           p_rc(i).valor_ano := valor_ano;      
           --PIPE ROW(p_rc(i));
    END LOOP;    
    RETURN p_rc;
END;     
END;

Como ler o resultado da function com SELECT?

Se eu faço assim:

Selecionar tudo

SELECT * FROM TABLE(smp_result_consolidado_unidade.sp_result_consolidado('2007','','')) 

ele dá erro de ORA-00902: invalid datatype

Já tentei usar CAST, MULTISET, mas nada funciona. E agora? rs

Obridgado à todos!

[Julian Campagnoli]

Não brother ... rsrsrsrsrs

você esta criando basicamente um "objeto".

A PL-TABLE que você gerou ela não pode ser tratada como Tabela armazenada no Banco de Dados.

Conforme você postou a pergunta apenas as duas primeiras linhas já teria respondido sua duvida.



Mas explique o que você esta precisando fazer em seu projeto(uma visao geral sobre ele) ... acho que assim ficara mais claro. E por que a nova procedure criada vai precisar desse PL-TABLE (objeto).

Abraço

[raphaelias]

O projeto é o seguinte:

Entrei numa empresa recentemente para reestruturar a parte web. Eles utilizam Oracle (primeiro contato que estou tendo) com Asp.net.

Quero fazer tudo back-end, deixar toda a parte de processamento no lado do servidor.

Para isso, preciso criar algumas SELECTs bem complexas como referência cruzada entre outras.

No SQL Server se você criar uma procedure com select * from tabela, e fizer um select * from procedure, funciona.

Agora preciso saber como faço isso no Oracle. Crio minha string de select, rodo e depois puxo as informações!

Muito obrigado novamente pela ajuda Julian

[Julian Campagnoli]

"No SQL Server se você criar uma procedure com select * from tabela, e fizer um select * from procedure, funciona".

Observação: No exemplo você criou uma PL-TABLE com varios registro. (Ou seja ela é como uma tabela com resultados de seu SELECT GIGANTE)

você quer trabalhar com esses dados (PL-TABLE) Isso dentro de uma outra procedure ?????

Ou você quer apartir de aplicativo externo utilizar(pegar) essa PL-TABLE ????

Esse um "select * from procedure" você faz no Asp.net ou na dentro da Procedure criada ????

Abraço

[raphaelias]

Quero utilizar o resultado da procedure tanto para usar em uma aplicação externa (relatórios e etc.) tanto para ser usada em outras procedures que utilizam do resultado com tabela base do novo select.

Então se eu conseguir dar corretamente um select nesse resultado, posso utilizá-la em ambos os casos.

Pelo que pesquisei seria assim

Selecionar tudo

SELECT * FROM TABLE(procedure)

mas dá erro de ORA-00902: invalid datatype.

Já tentei usar o CAST, mas também não funciona.

Esse é meu problema. Não sabia que no Sql tbn era como se fosse uma PL SQL Table, então o segredo é só como eu vou ler esse resultado!

Valeu!!

[rogenaro]

É possível sim, usando o CAST, mas é necessário criar um object type no banco e não uma pl/sql table dentro de sua package.

Você pode encontrar alguns exemplos neste link ou neste link

[raphaelias]

Para cada procedure que eu fizer vou ter que criar um type diferente no meu BD?

Sei que para criar um type do tipo Object ela não pode estar dentro de uma package, então tenho que deixa-las soltas no meu banco sem poder organizar em uma package?

Como vocês fazem para deixar uma aplicação de relatórios back-end?

Valeu Rafa!

[gokden]

então, você já pensou em implementar o conceito de inserir em um TEMP TABLE ??

ai você poderia dar o select normal, como se fosse uma tabela.
mais você teria qui definir se a sua tabela é ON COMMIT DELETE ROWS ou PRESERVE ROWS

de acordo com o que você precisa, você pode usar o PRESERVE ROWS e usar uma sequence que gera um id, para as linhas criadas no momenta, ai você retorna essa sequence da package e usa para fazer o select em sessões diferentes, mais depois você teria qui colocar um "delete from" explicitamente, depois que usar os dados, para não fical alocando a tabela......

[raphaelias]

Eu usei esse conceito na primeira procedure que fiz, mas queria um jeito que como você disse não precisasse deletar ou me preocupar depois que colhi os dados.

Posso estar falando besteira, não sei se está errado meu pensamento pois sou novato em Oracle, mas pelo que sei vindo do SQL e Mysql, eles utilizam esse conceito..

Bacana que muitos desenvolvedores estão comentando, obrigado à todos e vamos chegar a uma decisão rsrs

[Julian Campagnoli]

então raphael,

Acredito que a solução é simples mas estamos todos meio perdidos "ainda" rsrsrsrsrsrs. Legal que surgiram mais ideias rsrsrsrsrs

Pense nisso:

No seu exemplo que você mostrou, você já conseguiu passar como parametro do tipo SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado com os dados adquiridos do seu SELECT GIGANTE.

Agora acredito que a questao seja no refinamento que será feito dentro das procedures que receberao esse parametro essa tabela ou esse objeto.

Se puder postar o codigo das procedures que receberao o parametro SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado .

Abração

[raphaelias]

Então Julian são duas coisas que quero fazer com o resultado:

Uma lê-lo na aplicação externa para montar um relatório. Digamos que é um simples

Selecionar tudo

SELECT * FROM (TABLE(primeira_procedure))

A outra é usá-lo em uma outra procedure. Posso passar o resultado como parametro sim, mas o que estava pensando mesmo é nessa nova procedure dar um

Selecionar tudo

SELECT CAMPOS FROM (SELECT * FROM (TABLE(primeira_procedure)))

Resumindo, preciso que uma Procedure ou Function retorne dados que eu possa tratar com um simples select, tanto para aplicações externas quanto para selects internos.

E desculpem se não sei expressas meu problema direito rsrs

Valeuuuu!!

[Julian Campagnoli]

raphael, beleza brother ???????

Achei algo que talves possa te ajudar com referencia no select que você quer fazer.

De uma olhada nesse link:
http://glufke.net/oracle/viewtopic.php?t=469

No link acima de uma olhada nessa parte:

Selecionar tudo

open crs_aaa for 
   select * from table(pktemp_2.RETORNA_DADOS); 
   return crs_aaa; 
   close crs_aaa; 

Qualquer coisa manda aeeee ...

[raphaelias]

Fala Julian..beleza cara e tú?

Então..dei uma olhada no temporary table e pareceu útil sim, mas o problema é que tem de se criar primeiro a tempory fisicamente já com as colunas.

E no caso de precisar de colunas dinâmicas no caso de referência cruzada por exemplo? Aí acho que já não vai ser útil.

O que eu pensei, fazer uma function que retorna um Cursor e tentar ler com

Selecionar tudo

select * from TABLE(FUNÇÃO) FROM DUAL

, mas ainda não tive tempo de ver se funciona.

Por enquanto fiz o processamento na página mesmo, está uma carroça, mas já estão reclamando e vou ter que alterar o mais rápido possível hehe

Valeu mesmo pela ajuda!!

[ricardorauber]

Sei que o tópico já foi respondido adequadamente mas segue um exemplo prático:

Selecionar tudo

SQL> REM ************************************
SQL> REM * Passo 1: Criar o objeto DE BANCO *
SQL> REM ************************************
SQL> CREATE OR REPLACE TYPE varchar2_table_4000 IS TABLE OF VARCHAR2(4000);
  2  /

Type created

SQL> REM *******************************************************
SQL> REM * Passo 2: Criar a package com as funções necessárias *
SQL> REM *******************************************************
SQL> CREATE OR REPLACE PACKAGE pltable_teste_pck AS
  2  
  3    FUNCTION f_tabela RETURN varchar2_table_4000;
  4  
  5    FUNCTION f_campo(p_campo     VARCHAR2,
  6                     p_coluna    NUMBER,
  7                     p_separador VARCHAR2) RETURN VARCHAR2;
  8  
  9  END;
 10  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY pltable_teste_pck AS
  2  
  3    --========================================================--
  4    -- Monta a tabela em tempo de execução
  5    --========================================================--
  6  
  7    FUNCTION f_tabela RETURN varchar2_table_4000 IS
  8      v_tab varchar2_table_4000 := varchar2_table_4000();
  9    BEGIN
 10      v_tab.EXTEND;
 11      v_tab(1) := 'a;b;c;d';
 12      v_tab.EXTEND;
 13      v_tab(2) := '1;2;3;4';
 14      v_tab.EXTEND;
 15      v_tab(3) := 'e;f;g;h';
 16      v_tab.EXTEND;
 17      v_tab(4) := '5;6;7;8';
 18      RETURN v_tab;
 19    END;
 20  
 21    --========================================================--
 22    -- Separa o campo solicitado
 23    --========================================================--
 24  
 25    FUNCTION f_campo(p_campo     VARCHAR2,
 26                     p_coluna    NUMBER,
 27                     p_separador VARCHAR2) RETURN VARCHAR2 IS
 28  
 29     -----------------------------------------------------------
 30     -- Variável de retorno
 31     -----------------------------------------------------------
 32      v_retorno VARCHAR2(1000);
 33  
 34      -----------------------------------------------------------
 35      -- Contador de separadores
 36      -----------------------------------------------------------
 37      v_cont NUMBER := 0;
 38  
 39    BEGIN
 40  
 41      -----------------------------------------------------------
 42      -- Alimenta a variável
 43      -----------------------------------------------------------
 44  
 45      v_retorno := p_campo;
 46  
 47      -----------------------------------------------------------
 48      -- Verifica se é o primeiro campo
 49      -----------------------------------------------------------
 50  
 51      IF p_coluna = 1 THEN
 52        RETURN substr(v_retorno,1,instr(v_retorno,p_separador)-1);
 53  
 54      -----------------------------------------------------------
 55      -- Se for qualquer campo maior que 1 então procura os separadores
 56      -----------------------------------------------------------
 57  
 58      ELSIF p_coluna > 1 THEN
 59  
 60        -----------------------------------------------------------
 61        -- Passa caracter por caracter
 62        -----------------------------------------------------------
 63  
 64        FOR i IN 1 .. length(v_retorno) LOOP
 65  
 66          -----------------------------------------------------------
 67          -- Se encontrou um separador, alimenta o contador
 68          -----------------------------------------------------------
 69  
 70          IF substr(v_retorno,i,1) = p_separador THEN
 71            v_cont := v_cont + 1;
 72          END IF;
 73  
 74          -----------------------------------------------------------
 75          -- Verifica se chegou no campo desejado
 76          -----------------------------------------------------------
 77  
 78          IF v_cont = p_coluna - 1 THEN
 79  
 80            v_retorno := substr(v_retorno,i+1,length(v_retorno));
 81  
 82            -----------------------------------------------------------
 83            -- Se for um campo no meio da linha, busca o próximo separador
 84            -- Senão pega tudo até o fim
 85            -----------------------------------------------------------
 86  
 87            IF instr(v_retorno,p_separador) > 0 THEN
 88              RETURN substr(v_retorno,1,instr(v_retorno,p_separador)-1);
 89            ELSE
 90              RETURN substr(v_retorno,1,length(v_retorno));
 91            END IF;
 92  
 93          END IF;
 94  
 95        END LOOP;
 96  
 97        -----------------------------------------------------------
 98        -- Se não encontrou o campo, retorna nulo
 99        -----------------------------------------------------------
100  
101        RETURN NULL;
102  
103      END IF;
104  
105    END;
106  
107  END;
108  /

Package body created

SQL> REM ************************************************************
SQL> REM * Passo 3: Fazer a consulta na função que retorna o objeto *
SQL> REM ************************************************************
SQL> SELECT COLUMN_VALUE campo_completo,
  2         pltable_teste_pck.f_campo(COLUMN_VALUE,1,';') coluna1,
  3         pltable_teste_pck.f_campo(COLUMN_VALUE,2,';') coluna2,
  4         pltable_teste_pck.f_campo(COLUMN_VALUE,3,';') coluna3,
  5         pltable_teste_pck.f_campo(COLUMN_VALUE,4,';') coluna4
  6  FROM TABLE (pltable_teste_pck.f_tabela)
  7  /

CAMPO_COMPLETO   COLUNA1   COLUNA2   COLUNA3   COLUNA4
---------------- --------- --------- --------- ---------
a;b;c;d          a         b         c         d
1;2;3;4          1         2         3         4
e;f;g;h          e         f         g         h
5;6;7;8          5         6         7         8

SQL> 

[dr_gori]

Valeu bróder.
É sempre bom ter exemplos práticos !
Aqui nesse link tem vários outros exemplos:
http://www.psoug.org/reference/type.html

[luciomferro]

Olá raphaelias ao invés de criar como função crie como procedure, desta forma:

Selecionar tudo

PROCEDURE SP_RESULT_CONSOLIDADO
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL, p_tab_resultado out SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado).

No corpo do procedimento chamador declare uma variável do tipo desta collection e passe para chamada da procedure.

Selecionar tudo

declare
   v_tb_resultado SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado;
begin
   SMP_RESULT_CONSOLIDADO_UNIDADE.SP_RESULT_CONSOLIDADO(
'2007','','',v_tb_resultado);
   for v_i in 1 .. v_tb_resultado.count loop
       dbms_output.put_line('coluna1: '||v_tb_resultado(v_i).codigo);
       -- e assim para todas as colunas da sua collection
       -- as colunas podem ser usada com if's, etc individualmentes
       -- só identificandos com o indice do type e o nome da coluna
       -- v_tb_resultado(v_i).coluna
   end loop;
end;

Bom acho que é isto, espero tê-lo ajudado.