"Tilt-Tilt" ao criar Function Pipelined

[Tinho]

Senhores tenho que gerar um relatório diário que poderá ser executado em diversos pontos da package, por procedures diferentes. Ao invés de utilzar cursores em cada ponto, estava pensando em utilizar Table Functions para retornar o resultado da consulta em PIPE ROW. Para isso, criei os seguintes objetos:

Selecionar tudo

TYPE TP_DEPENDENTE_FUNCIONARIO IS RECORD (  NM_PESSOA     GEN_PESSOA.NM_PESSOA            %TYPE 
                                           ,CD_PESSOA     GEN_PESSOA.CD_PESSOA            %TYPE
                                           ,CD_CNPJ       NUMBER(11)
                                           ,NM_DEPENDENTE GEN_PES_DEPENDENTE.NM_DEPENDENTE%TYPE
                                           ,NR_CPF        GEN_PES_DEPENDENTE.NR_CPF       %TYPE
                                           ,DT_CADASTRO   GEN_PES_DEPENDENTE.DT_CADASTRO  %TYPE
                                           ,OR_RANK       NUMBER(9)
                                         );

TYPE TB_DEPENDENTE_FUNCIONARIO IS TABLE OF TP_DEPENDENTE_FUNCIONARIO;

FUNCTION FUN_RELATORIO_DEPENDENTE ( prc_cd_pessoa VARCHAR2 DEFAULT NULL )
  RETURN PKG_RELATORIO_DEPENDENTE.TB_DEPENDENTE_FUNCIONARIO PIPELINED;

Na assinatura da minha FUNCTION eu tenho um parâmetro que servirá de filtro para executar o cursor e no retorno da função e especifico o meu TYPE RECORD, no qual eu preciso armazenar o resultado do meu cursor.

No entanto ao criar o corpo da minha FUNCTION está dando erro:

Selecionar tudo

FUNCTION FUN_RELATORIO_DEPENDENTE (  prc_cd_pessoa      VARCHAR2 DEFAULT NULL ) 
  RETURN PKG_RELATORIO_DEPENDENTE.TB_DEPENDENTE_FUNCIONARIO PIPELINED
  IS
  
  T_DEPENDENTE_FUNCIONARIO PKG_RELATORIO_DEPENDENTE.TB_DEPENDENTE_FUNCIONARIO;
  
  CURSOR cDEPENDENTE_FUNCIONARIO
  IS SELECT T.NM_PESSOA
           ,T.CD_PESSOA
           ,T.CPF_FUNCIONARIO
           ,GPD.NM_DEPENDENTE
           ,GPD.NR_CPF
           ,GPD.DT_CADASTRO
           ,RANK() OVER(PARTITION BY T.CD_PESSOA ORDER BY GPD.DT_CADASTRO) AS OR_RANK
      FROM ( SELECT  0              AS CD_TIPO_PARENTE
                    ,GP.CD_PESSOA   AS CD_PESSOA
                    ,GP.CD_CNPJ     AS CPF_FUNCIONARIO
                    ,GP.NM_PESSOA   AS NM_PESSOA
                    ,GP.DT_CADASTRO AS DT_CADASTRO
               FROM GEN_PESSOA GP
              WHERE GP.CD_PESSOA  = prc_cd_pessoa
                AND GP.ID_TITULAR = 'S'
              UNION ALL
             SELECT  GPD.CD_TIPO_PARENTE AS CD_TIPO_PARENTE
                    ,GPD.CD_DEPENDENTE   AS CD_DEPENDENTE
                    ,GPD.NR_CPF          AS CPF_DEPENDENTE
                    ,GPD.NM_DEPENDENTE   AS NM_DEPENDENTE
                    ,GPD.DT_CADASTRO     AS DT_CADASTRO
               FROM GEN_PES_DEPENDENTE GPD
              WHERE GPD.CD_PESSOA = prc_cd_pessoa
           ) T
      LEFT JOIN GEN_PES_DEPENDENTE GPD
        ON T.CD_PESSOA = GPD.CD_DEPENDENTE
      LEFT JOIN GEN_TIPO_PARENTE GTP
        ON T.CD_TIPO_PARENTE = GTP.CD_TIPO_PARENTE
    ORDER BY T.CD_PESSOA DESC;
         
  BEGIN
    FOR RG IN cDEPENDENTE_FUNCIONARIO
      LOOP
        T_DEPENDENTE_FUNCIONARIO.NM_PESSOA := RG.NM_PESSOA;

        -- ... (demais campos)
        PIPE ROW(T_DEPENDENTE_FUNCIONARIO);
      END LOOP;     
      
      RETURN;
    
END FUN_RELATORIO_DEPENDENTE;

O erro é o seguinte:

[code]

Compilation errors for PACKAGE BODY SYSTUR.PKG_RELATORIO_DEPENDENTE

Error: PLS-00302: component 'NM_PESSOA' must be declared
Line: 198
Text: T_DEPENDENTE_FUNCIONARIO.NM_PESSOA := RG.NM_PESSOA;

Error: PL/SQL: Statement ignored
Line: 198
Text: T_DEPENDENTE_FUNCIONARIO.NM_PESSOA := RG.NM_PESSOA;

Error: PLS-00382: expression is of wrong type
Line: 200
Text: PIPE ROW(T_DEPENDENTE_FUNCIONARIO);

Error: PL/SQL: Statement ignored
Line: 200
Text: PIPE ROW(T_DEPENDENTE_FUNCIONARIO);

É como se o meu objeto TYPE e suas colunas não fossem reconhecidos! Eu desconfio que é por conta do tipo de objeto criado:

Selecionar tudo

TYPE IS RECORD

O motivo de eu querer fazer isso, é que depois, toda vez que eu precisar utilizar esses dados apenas chamo a FUNCTION da seguinte maneira:

Selecionar tudo

SELECT * FROM TABLE(FUN_RELATORIO_DEPENDENTE (prc_cd_pessoa)); 

E utilizo o resultado para formatar o layout do relatório.

Por favor alguém saberia me dizer se é possível fazer isso que eu quero?

[rogenaro]

No caso, T_DEPENDENTE_FUNCIONARIO é uma table de TP_DEPENDENTE_FUNCIONARIO, portanto você deveria indexá-la:

Selecionar tudo

T_DEPENDENTE_FUNCIONARIO.extend(1);
T_DEPENDENTE_FUNCIONARIO(T_DEPENDENTE_FUNCIONARIO.last).NM_PESSOA := RG.NM_PESSOA;

Você está usando retorno pipelined para types criados dentro de uma package.
A não ser que eu esteja enganado, para utilizar este tipo de função é necessário criar os objetos no banco:

Selecionar tudo

create or replace TYPE TP_DEPENDENTE_FUNCIONARIO as object (  NM_PESSOA     GEN_PESSOA.NM_PESSOA            %TYPE
                                           ,CD_PESSOA     GEN_PESSOA.CD_PESSOA            %TYPE
                                           ,CD_CNPJ       NUMBER(11)
                                           ,NM_DEPENDENTE GEN_PES_DEPENDENTE.NM_DEPENDENTE%TYPE
                                           ,NR_CPF        GEN_PES_DEPENDENTE.NR_CPF       %TYPE
                                           ,DT_CADASTRO   GEN_PES_DEPENDENTE.DT_CADASTRO  %TYPE
                                           ,OR_RANK       NUMBER(9)
                                         );

create or replace TYPE TB_DEPENDENTE_FUNCIONARIO as TABLE OF TP_DEPENDENTE_FUNCIONARIO;

Neste caso, você usaria o pipe_row com um novo objeto TP_DEPENDENTE_FUNCIONARIO (não seria necessário utilizar o tipo TB_DEPENDENTE_FUNCIONARIO dentro do loop, pois o comando pipe row já irá preencher a table de retorno da função):

Selecionar tudo

pipe row 
( TP_DEPENDENTE_FUNCIONARIO
  ( rg.nm_pessoa
  , rg.cd_pessoa
  , rg.cd_npjf
  , rg.nm_dependente
  , rg.nr_cpf
  , rg.dt_cadastro
  , rg.or_rank
  )
);