Procedure in Package receiving an array (coming from a cursor/function) as input parameter - Oracle

Asked

Viewed 721 times

0

Good afternoon,

I already have some experience with PL/SQL but now I started working with oracle and Packages.

I have a problem from which my package process is receiving two parameters and from it running some tasks working with Bulk Collect, forall and rowtype to fetch the information in various tables.

The question is: I need these parameters to come from another function or cursor and from that return, run my Procedure inside the package. This cursor/function needs to be within the same package as well.

This cursor/function will query an X table and if this table contains some results, they will search from the previous table for the necessary information and insert them in a temporary table that will be consumed by another process.

Has anyone ever worked with it or knows more or less the way I can accomplish it?

Thank you

oracle input pl-sql packages

  • See if I understand your question ... You want a function that returns a collection, this value must be sent by parameter to your procedure, which will process the information and carry out the implementation in a database. This is the desired flow?

  • Yeah! Exactly

1 answer

0

It is possible to create a function with the Type return of your collection, in the procedure you want to receive this collection you need to pass the same type parameter (If you need to handle the collection data you need to be an IN OUT parameter).

Here is an example similar to the one requested (at the end there is a link with the creation of tables and query used in the example):

create or replace package PCK_TESTE is

  Type TPessoas is table of pessoas%Rowtype;

  Function fRetornaPessoas return TPessoas;

  Procedure PreencheTabelaTemporaria(aPessoas IN OUT TPessoas );

end PCK_TESTE;
/
create or replace package body PCK_TESTE is

  Function fRetornaPessoas return TPessoas is
    vRetorno TPessoas; 
    cursor TodasPessoas is
      select * from pessoas;
  begin
    -- Carrega coleção e retorna  
    open TodasPessoas;
    fetch TodasPessoas bulk collect into vRetorno;
    close TodasPessoas;

    return vRetorno;
  end;

  Procedure PreencheTabelaTemporaria( aPessoas IN OUT TPessoas ) is
  begin
    --Exemplo de regra de negócio 
    for i in aPessoas.first .. aPessoas.last loop
      aPessoas(i).nome := aPessoas(i).nome || ' Sobrenome não informado';
    end loop;
    --Efetivação na tabela temporária
    forall i in aPessoas.First .. aPessoas.Last
      insert into TMPpessoas (id, nome, Data_Nascimento) values (aPessoas(i).id, aPessoas(i).nome, aPessoas(i).Data_Nascimento);
  end;

end PCK_TESTE;
/

Method call: 

declare
  vPessoa PCK_TESTE.TPessoas;
begin
  vPessoa := pck_teste.fRetornaPessoas();
  pck_teste.preenchetabelatemporaria(vPessoa);
end;

Examples with data in SQL Fiddle: http://sqlfiddle.com/#! 4/bed69/1/0

Browser other questions tagged oracle input pl-sql packages

You are not signed in. Login or sign up in order to post.