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?

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

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:


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

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

[quote]I knew that in Oracle, package act like stored procedure in MS SQL Server
[/quote]
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.

[quote]the use of pipelined keyword makes it easier[/quote]
But any DML prohibited within pipelined function.
Serious limitation, I'd say

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

[quote=Jens Naumann]Hi, DML is allowed if You use pragma autonomous_transaction.
Regards[/quote]

Nice!
I wish I learned it before...

Deleted duplicate

Deleted duplicate