How to get multiple lines from a stored function using oracle

Question

I am trying to create a stored function in oracle that returns multiple rows.

My question is very similar to this , except that I want to receive a select * request

In short, I want to create a function that returns the result of this query

 select * from t_email_queue 

I tried this:

 create or replace PACKAGE email_queue AS type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE; FUNCTION lock_and_get return t_email_queue_type; END email_queue; create or replace PACKAGE BODY email_queue AS FUNCTION lock_and_get RETURN t_email_queue_type AS queue_obj t_email_queue_type; cursor c (lockid in varchar2) is select * from t_email_queue where lockedby = lockid; lockid varchar2(100) := 'alf'; BEGIN OPEN c(lockid); FETCH c bulk collect INTO queue_obj; return queue_obj; END lock_and_get; END email_queue; 

The package compiles just fine, but when I try to call it with this request

 select * from table(email_queue.lock_and_get); 

Oracle throws the following error

 ORA-00902: invalid datatype 00902. 00000 - "invalid datatype" *Cause: *Action: Error at Line: 1 Column: 20 

I think Oracle wants me to create my return type at the schema level, but when I try to do

 create type t_email_queue_type is table of T_EMAIL_QUEUE%ROWTYPE; 

Oracle complains

 Type IMAIL.T_EMAIL_QUEUE_TYPE@imail dev Error(1): PL/SQL: Compilation unit analysis terminated Error(2,37): PLS-00329: schema-level type has illegal reference to IMAIL.T_EMAIL_QUEUE 

Can someone point me in the right direction? What am I missing here?

Thank you for reading!

Answer 1

with SQL types that you cannot do% ROWTYPE, you will need to enter each column according to table *.

* sys.anydataset aside. but the descent along this route is much more difficult.

eg. if your table was

 create table foo (id number, cola varchar2(1)); 

then

 create type email_queue_type is object (id number, cola varchar2(1)); / create type t_email_queue_type as table of email_queue_type; / 

and use this email_queue_type_tab table as the result of your function.

but I would recommend a pipelined function since your current code does not scale.

eg:

 SQL> create table foo (id number, cola varchar2(1)); Table created. SQL> SQL> create type email_queue_type is object (id number, cola varchar2(1)); 2 / Type created. SQL> create type t_email_queue_type as table of email_queue_type; 2 / Type created. SQL> insert into foo select rownum, 'a' from dual connect by level <= 10; 10 rows created. SQL> SQL> create or replace PACKAGE email_queue AS 2 3 4 FUNCTION lock_and_get return t_email_queue_type pipelined; 5 6 END email_queue; 7 / Package created. SQL> create or replace PACKAGE BODY email_queue AS 2 3 FUNCTION lock_and_get RETURN t_email_queue_type pipelined AS 4 queue_obj t_email_queue_type; 5 6 BEGIN 7 8 for r_row in (select * from foo) 9 loop 10 pipe row(email_queue_type(r_row.id, r_row.cola)); 11 end loop; 12 13 END lock_and_get; 14 15 END email_queue; 16 / Package body created. SQL> select * from table(email_queue.lock_and_get()); ID C ---------- - 1 a 2 a 3 a 4 a 5 a 6 a 7 a 8 a 9 a 10 a 10 rows selected. SQL> 

Answer 2

If you are not particularly interested in using an SQL type, you can do this with sys_refcursor :

 create or replace package email_queue as function lock_and_get return sys_refcursor; end email_queue; / create or replace package body email_queue as function lock_and_get return sys_refcursor AS c sys_refcursor; lockid varchar2(100) := 'alf'; begin open c for select * from t_email_queue where lockedby = lockid; return c; end lock_and_get; end email_queue; / 

From SQL * Plus, you can call it something like:

 var cur refcursor; exec :cur := email_queue.lock_and_get; print cur 

and how exec is a shorthand for a simple anonymous block, which you can call, if from other PL / SQL objects. However, you cannot:

 select * from table(email_queue.lock_and_get); 

I am not familiar with function calls from PHP, but from Java you can use this directly as a return from the called operator, so you don't need the select * from table() construct at all. I have no idea if you can execute an anonymous block in a PHP call, something like begin $cur = email_queue.lock_and_get; end; begin $cur = email_queue.lock_and_get; end; , and have $cur as your result set, which you can iterate through?

I understand that this is not a complete answer, since the PHP side is too vague, but may give you some ideas.

Answer 3

If you are using PHP and want to access a function stored in Oracle. You can use something like this

 //Your connection details $conn = oci_connect($username, $password, '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))' ); /* Your query string; you can use oci_bind_by_name to bind parameters or just pass the variable in it*/ $query = "begin :cur := functionName('".$param1."','".$param2."','".$param3."'); end;"; $stid = oci_parse($conn, $query); $OUTPUT_CUR = oci_new_cursor($conn); oci_bind_by_name($stid, ':cur', $OUTPUT_CUR, -1, OCI_B_CURSOR); oci_execute($stid); oci_execute($OUTPUT_CUR); oci_fetch_all($OUTPUT_CUR, $res); // To get your result var_dump($res); 

Hope this helps.