Can a Package function return table?

[adamlevine]

Can a Package function return table?

Hi all,

I got a set of data to display in a crystal report; But before the raw data be able to shown in crystal report, I have to massage the data via logic with insert and update statements to form my result table .
From MS SQL Server, there are stored procedure which I can write my own logic to interact with Tables or Values and if I end the stored procedure by a select statement, the SP can return the table result for me.

I knew that in Oracle, package act like stored procedure in MS SQL Server, but I dont know how to define the return type as a table in a package function....

But I only can get only 1 row, if i modified the SELECT statement that collect more than one row, the package can be compile but when I execute It will prompt error and said there are more than one row in result.

I also tried to use cursor and loop it, but still not working; May I ask can you guys give me some advise or help please?

 

[Tichij]

Hi adamlevine,
I think you need to be use a Pipelined Function:
https://asktom.oracle.com/pls/asktom/f?p=100:11::::11_QUESTION_ID:19481671347143

 

[Maxmix]

If you have to combine DML + Query logic in one call, then you need a function, returning CURSOR. Function can be within a package or standalone:

create or replace package body  MYPKG
..............
procedure P (result out sys_refcursor, Param1 date, Param2 number, ...)
is
begin
  -- Prepare output in RES_TABLE
  delete RES_TABLE ....
  insert into RES_TABLE ....
  update RES_TABLE ....
 open result for
  select *  from  RES_TABLE;
end;
.................
end MYPKG;

Then you could test your function as usual, the result set will open in a new tab of Test Window.

 

[vlevi]

the use of pipelined keyword makes it easier.

create or replace package sorok is

type tr_sor is record(
n_ev integer,
d_dat date,
c_sor varchar2(400)
);
type ta_sor is table of tr_sor;

function sorokp(pn_ev integer) return ta_sor pipelined;

end sorok;
/

create or replace package body sorok is

function sorokp(pn_ev integer) return ta_sor pipelined is
r tr_sor;
begin
r:=null; r.n_ev:=pn_ev; r.d_dat:=to_date(pn_ev||'.01.01','yyyy.mm.dd'); r.c_sor:='year is '||r.n_ev;
pipe row(r);

r:=null; r.n_ev:=pn_ev; r.d_dat:=to_date(pn_ev||'.02.01','yyyy.mm.dd'); r.c_sor:='year is '||r.n_ev;
pipe row(r);

for i in 3..9 loop
r:=null; r.n_ev:=pn_ev; r.d_dat:=to_date(pn_ev||'.0'||i||'.01','yyyy.mm.dd'); r.c_sor:='year is '||r.n_ev;
pipe row(r);
end loop;

end;

end sorok;
/

the usage of it is :

select * from table (sorok.sorokp(2018))

the result

2

 

[William Robertson]

I knew that in Oracle, package act like stored procedure in MS SQL Server

No, a procedure in Oracle is like a procedure in SQL Server. A PL/SQL package is a container for multiple procedures and functions etc, like a C library or a Perl module. As far as I know, SQL Server has no equivalent.

 

[Maxmix]

the use of pipelined keyword makes it easier

But any DML prohibited within pipelined function.
Serious limitation, I'd say

 

[Jens Naumann]

Hi, DML is allowed if You use pragma autonomous_transaction.
Regards

 

[Maxmix]

Hi, DML is allowed if You use pragma autonomous_transaction.
Regards

Nice!
I wish I learned it before...

 

[Maxmix]

Deleted duplicate

 

[Maxmix]

Deleted duplicate

 

[adamlevine]

h

 

[Ganu12345]

would like to generate a SQL statement within a package function and bulk collect the records into a table type returned by the function. I then need to get the data using regular SQL (SELECT * FROM TABLE(

 

[Jens Naumann]

Hi, this returns integer values from 1 to a given maximum.

create or replace package test is
type t_id is table of integer;
function test(max integer) RETURN t_id PARALLEL_ENABLE PIPELINED;
end test;
/
create or replace package body test is
function test(max integer) RETURN t_id PARALLEL_ENABLE PIPELINED
is
begin
for r in (select level
from dual
connect by level