Ler CURSOR que uma procedure retorna como Select

[raphaelias]

Boa tarde pessoal,

Meu problema: Ler um cursor que uma procedure retorna após consulta ao
banco.

O mesmo problema foi postada no tópico http://glufke.net/oracle/viewtopic.php? ... ler+cursor mas mesmo assim fiquei com dúvidas.

==A procedure:==

Selecionar tudo

Procedure SP_RESULT_CONSOLIDADO_UNIDADE
(ano IN number, p_rc OUT SYS_REFCURSOR)
IS
l_sql VARCHAR2(32700) := 'SELECT EM VARIAS TABELAS...';
l_rc SYS_REFCURSOR;
BEGIN
OPEN p_rc FOR l_sql;
END;


Ao executar com o comando:
DECLARE
TYPE CURSOR_TYPE_0 IS REF CURSOR;
p_rc CURSOR_TYPE_0;
BEGIN

-- Now call the stored program
sp_result_consolidado_unidade(012007,p_rc);

-- Output the results

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;´

Ele me traz a variável p_rc normalmente.

Mas como faço para ler essa variável no estilo SELECT * FROM p_rc e
usá-la em um OLEDB em Asp.net?

Obrigado pela atenção!

[Julian Campagnoli]

E aeee beleza raphael,

Pelo que entendi ... você esta precisando criar uma procedure que armazena dados em um cursor e e enviar esses dados (uma tabela) para ser usada em seu aplicativo.

Para isso você precisara criar uma procedure que retorne uma PL-TABLE. O exemplo será o mesmo que foi feito no tópico que já foi repondido.

Vou tentar explicar de maneira mais clara como é o funcionamento do código postado no link que você pegou referencia.

Obs.:

Primeiramente foi criado a especificação, ou seja , esta criando um tipo TABLE e tornando público na PKG.

r_employees é um tipo TABLE que contem "campos referente a sua tb": employee_id employees.employee_id%type ... etc

Selecionar tudo

CREATE OR REPLACE PACKAGE XXX AS 
 
 PROCEDURE PR_TESTE (enter_name IN VARCHAR2 , 
                     p_out OUT xxx.tab_employees) ;
  
 TYPE r_employees_type IS RECORD ( 
             employee_id employees.employee_id%type 
           , first_name employees.first_name%type 
           , salary employees.salary%type); 

   TYPE tab_employees is table of r_employees_type index by binary_integer; 
END; 

[/code]

[Julian Campagnoli]

Agora transferindo isso para o seu codigo:

Selecionar tudo

Procedure SP_RESULT_CONSOLIDADO_UNIDADE  (ano IN number, p_rc OUT p_out OUT xxx.tab_employees) IS

OPEN p_rc FOR l_sql; 
   LOOP 
      p_out(i).employee_id  :=  x.employee_id; 
      p_out(i).first_name   :=  x.first_name; 
      ...
      
      i := i + 1; 
   END LOOP;
END; 

Observação: Observer que foi criado criado uma PACKAGE.

Julian Campagnoli

[raphaelias]

Julian muito obrigado!

Agora ficou mais claro..vou tentar desenvolver e lhe aviso o resultado.

Aproveitando a última pergunta, por quê criar uma package e não apenas fazer uma procedure?

Mais uma vez obrigado!

[Julian Campagnoli]

Acredito pelo fato de ser utilizados em aplicativos externos.

Obs.: Em JAVA existem metodos para receber PL-TABLE. E o metodo(função) que já vi fazendo essa utilização necessita de ser passado como parametro a PKG.

Abração, qualquer coisa manda aeee.

[raphaelias]

Entendi Julian. Vou desenvolver com package mesmo.

Um adúvida que surgiu quando estava desenvolvendo:
O meu select que a procedure retorna vem de muitas tabelas, e não só de uma como a do exemplo(employees)

Então como essa parte onde declara-se o tipo?

Selecionar tudo

TYPE r_employees_type IS RECORD (
             employee_id employees.employee_id%type
           , first_name employees.first_name%type
           , salary employees.salary%type); 

Brigadão!!!

[Julian Campagnoli]

então, "O meu select que a procedure retorna vem de muitas tabelas, e não só de uma como a do exemplo(employees) "

Pelo que entendi de sua pergunta ...

Seu CURSOR vai receber dados de tabelas diferentes ???? Se for isso ... é só passar a tipagem referente as tabelas.
Ex.: Suponha que cod_dpto venha da tabela dpto.

Selecionar tudo

TYPE r_employees_type IS RECORD ( 
employee_id employees.employee_id%type 
, first_name employees.first_name%type 
, salary employees.salary%type
, cod_dpto dpto.cod_dpto%type);

[raphaelias]

Julian muito obrigado pela sua ajuda! Tem sido fundamental para o andamento do meu trabalho aqui.

Fiz o package como você falou mas estou com dificuldade de preencher o parametro de resposta com o resultado do select.

Pesquisei e fiz por EXECUTE IMMEDIATE, porém a mesma só apresenta erros . Segue abaixo os códigos:

Selecionar tudo

Package SMP_RESULT_CONSOLIDADO_UNIDADE IS
  
  PROCEDURE SP_RESULT_CONSOLIDADO
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL
    , p_rc OUT 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
 
PROCEDURE SP_RESULT_CONSOLIDADO
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL
    , p_rc OUT SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado)
IS
    str VARCHAR2(3000);
    c_str SMP_RESULT_CONSOLIDADO_UNIDADE.r_resultado;    
BEGIN
    str := 'SELECT RES1.CODIGO AS CODIGO,RES1.GERENCIAL AS DESCRICAO,RES2.CODIGO AS CDMASTER,RES2.GERENCIAL AS DSMASTER'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''01'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M1'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''02'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M2'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''03'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M3'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''04'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M4'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''05'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M5'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''07'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M7'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''08'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M8'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''09'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M9'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''10'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M10'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''11'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M11'||
    ' ,SUM(CASE WHEN CONTA.MESANO=''12'||ano||''' THEN decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR) ELSE NULL END) AS M12'||
    ' ,SUM(decode(nvl(FBGGRCONTA.OPERADOR,''N''),''N'',-1*CONTA.VALOR,CONTA.VALOR)) AS VALOR_ANO'||
    ' FROM'||
    ' ((fb_res_gerencial RES1 JOIN fb_res_gerencial RES2 ON RES1.CODTOTAL=RES2.CODIGO AND RES1.CODTOTAL IS NOT NULL)'||
    ' LEFT JOIN FB_DEPA_GERENCIAL DEPA ON DEPA.CODGER=RES1.CODIGO)'||
    ' JOIN FBG_RES_CONTA CONTA ON CONTA.CODCONTA=DEPA.CODCONTA AND CONTA.RATCOM=DEPA.COMUM'||
    ' LEFT JOIN SCCUSTO SC ON SC.COD_RED=CONTA.SCC'||
    ' LEFT JOIN FBGCONTA ON FBGCONTA.CODCONTA=CONTA.CODCONTA'||
    ' LEFT JOIN FBGGRCONTA ON FBGGRCONTA.CODIGO=FBGCONTA.GRUPO'||
    ' LEFT JOIN FBGSCCUSTO ON FBGSCCUSTO.CODIGO=SC.COD_RED'||
    ' WHERE substr(CONTA.mesano,3,4) = '''||ano||''''||
    ' AND SC.COD_RED NOT IN(SELECT COD_RED FROM FBGSCCOM)'||
    ' AND FBGSCCUSTO.PRODUTIVO=''S''';
    IF(LENGTH(unidade)>0)THEN
        str:=str||' AND CONTA.SCC='''||unidade||'''';
    END IF;
    IF(LENGTH(grupo)>0)THEN
        str:=str||' AND SC.CDGRUPOCUS='''||grupo||'''';
    END IF;    
    str:=str||' GROUP BY RES1.CODIGO,RES1.GERENCIAL,RES2.CODIGO,RES2.GERENCIAL'||
    ' ORDER BY CDMASTER,CODIGO';

    EXECUTE IMMEDIATE str INTO c_str;
--ERRO ORA-01007: variable not in select list
    --p_rc := TABLE OF c_str INDEX BY BINARY_INTEGER;
--COMO FAÇO PARA PREENCHER O PARAM DE RESPOSTA P_RC?
END;     

END;

Muito obrigado novamente pela ajuda!

[Julian Campagnoli]

E aeee raphael, dei uma olhada no seu codigo ...

Tenta fazer assim:

Selecionar tudo

DECLARE
    
     
    TYPE CUR_TYPE IS REF CURSOR;
     C_CUR  CUR_TYPE;
     
    WL   NUMBER; -- CONTADOR
        

     -- DECLARAÇÃO DAS SUAS VARIAVEIS
     codigo                 VARCHAR2(11); -- tipagem delas na tabela
     descricao  
     cdmaster 
     dsmaster  
     
     -- OBS.: Declare todas suas variaveis  
     -- até a VARIAVEL_N   

BEGIN

     OPEN  C_CUR  FOR str;    
         LOOP
         -------------------------------------------------------------------------
         FETCH C_TESTE_EXTERNAL   INTO codigo ,
                                                            VARIAVEL_2,
                                                             ... ,
                                                         VARIAVEL_N;
           
           WL := WL +1;
          
           c_str(WL).codigo := codigo;
           c_str(WL).variavel_n := VARIAVEL_N;
     
     END LOOP;

END;

Note que foi criado um TYPE de REF_CURSOR;

Acho que assim você conseguira armazenar os dados em sua PL-TABLE.


Abração

[Julian Campagnoli]

raphael,

Obs.: Verificar se seu SELECT ira retornar a mesma quantidade de campos que você possui em sua PL-TABLE.
O conceito seria basicamente um "SELECT INTO" mesma quantidade de campos.

Abração, qualquer coisa entre em contato.

[raphaelias]

Vou tentar Julian, valeu..

Estou desesperado cara..tenho que entregar essa fase do projeto amanhã, só falta isso dar certo, o o Oracle só da erro cara..tudo que faço dá erro!!! hehe

Tentei até mandar um email para o "julian_campag arrôba hotmail com" mas acho que está errado..

Valeu cara..se não fosse você estava na roça! hauhaua

[Julian Campagnoli]

hehehheehe, calma que daki a pouco nois ajeita rsrsrsrsr

Na verdade eu uso aquele endereço somente para MSN, e estou em horario de trabalho e não utilizo msn nem e-mail pessoal aqui na empresa.

Qualquer coisa manda aeee.

[raphaelias]

Aeeeeeeeeeeeeeee Juliannnnnnn parece que funcionou cara!!!!! Vou numa churrascaria depois do trampo comemorar!! huahauha

Pra matar a pau agora..como pego os resultados que a procedure retorna com SELECT para usar no OLEDB em asp.net??

Brigadão mesmo cara!!

[Julian Campagnoli]

Beleza, depois toma uma pra mim rsrsrsrsrsrs.

Brother, agora você vai ter que ver como o asp.net trabalha com ORACLE PL-TABLE.

Depois compartilha seu codigo no forum.

Abração.

[raphaelias]

Vou pesquisar sim Julian, mas dentro do Oracle mesmo não tem como fazer um código do tipo:

Selecionar tudo

DECLARE
p_rc SMP_RESULT_CONSOLIDADO_UNIDADE.TAB_RESULTADO;
BEGIN

-- Now call the stored program
  smp_result_consolidado_unidade.sp_result_consolidado('2007','GRUPO A','',p_rc);
  SELECT * FROM TABLE(p_rc);
END;

?

Tem algum jeito de fazer isso quando as colunas são montadas dinamicamente? Esse na verdade é uma outra procedure quase igual, só que em vez dos meses trago as empresas dos registros nas colunas.
Desse jeito não posso jogar o resultado do fetch em uma variavel estática já declarada.

Valeu mesmo cara..e desculpa se estou sendo chato, mas quando ficar bom vou ajudar os outros como você..valeu!

[Julian Campagnoli]

Beleza raphael,

então você terá uma outra procedure

Selecionar tudo

sp_result_consolidado('2007','GRUPO A','',p_rc); 

Nessa nova procedure você vai precisar dos valores obtidos na procedure anterior "SP_RESULT_CONSOLIDADO".
(Obs.: os nomes das suas procedures estao iguais, devem ser diferentes).

Acredito que passar os passar os valores esticamente(Apenas declarando como você fez), não vai ter como.

Mas você pode passar sua PL_TABLE da procedure SP_RESULT_CONSOLIDADO para sua nova procedure.

[Julian Campagnoli]

Tenta fazer assim ...

Na especificação de sua PKG,

Selecionar tudo

  


  PROCEDURE SP_RESULT_CONSOLIDADO 
    (ano IN VARCHAR, grupo IN VARCHAR DEFAULT NULL, unidade IN VARCHAR DEFAULT NULL 
    , p_rc OUT SMP_RESULT_CONSOLIDADO_UNIDADE.tab_resultado); 
   
   PROCEDURE NOVA_PROCEDURE(param_1  IN  tipo,
                                                   c_str        IN SMPCONSOLIDADO_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; 

Essa nova procedure estara recebendo sua PL-TABLE com todos registros armazenados.

Qualquer coisa manda ai.

Abraço

[raphaelias]

Obrigado Julian.

Como entrou em um novo assunto, vou criar um novo tópico no fórum para que se futuramente alguém precisar, possa achar oks!

Valeuu e espero sua ajuda no outro tópico!