how to return a recordset from PL/SQL FUNCTION 
Author Message
 how to return a recordset from PL/SQL FUNCTION
Hello all,

I would like to find the way to return recordsets (or collection)
from a PL/SQL function as the return parameter and read the
results afterwards from a Java JDBC application.

Can anyone point me to resources or information of how can this
be done?

Thanks in advance,
Regards,
Giovanni



Mon, 21 Feb 2005 15:49:09 GMT
 how to return a recordset from PL/SQL FUNCTION

On Thu, 5 Sep 2002 09:49:09 +0200, "Giovanni Azua"

Quote:

>Hello all,

>I would like to find the way to return recordsets (or collection)
>from a PL/SQL function as the return parameter and read the
>results afterwards from a Java JDBC application.

>Can anyone point me to resources or information of how can this
>be done?

>Thanks in advance,
>Regards,
>Giovanni

- this groups archives : more than 1000 replies I guess
- The PL/SQL manual: topic REF CURSOR
- www.asktom.com
- and many many many many others

Please try to do your own research before posting.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address



Mon, 21 Feb 2005 16:42:46 GMT
 how to return a recordset from PL/SQL FUNCTION
create or replace package aa is

Type TQuantitesInfo IS RECORD

(

X NUMBER,

Y NUMBER

);

function t return TQuantitesInfo;

end;

/

create or replace package body aa;

create or replace function t is

e TQuantitesInfo

begin

e.X := 1;

e.Y := 2;

return e;

end;

end;

--
----------------------------------------------------------------------------
------------------------------
Trs cordialement
Denis Jeanneret - Directeur Technique, Division ORACLE
Epicentre - 529 Route des Vernes 74370 PRINGY
Tel: 04.5009.7000
Fax:04.50.66.92.19
web: www.epicentre.fr
----------------------------------------------------------------------------
------------------------------


Quote:
> Hello all,

> I would like to find the way to return recordsets (or collection)
> from a PL/SQL function as the return parameter and read the
> results afterwards from a Java JDBC application.

> Can anyone point me to resources or information of how can this
> be done?

> Thanks in advance,
> Regards,
> Giovanni



Mon, 21 Feb 2005 20:57:42 GMT
 how to return a recordset from PL/SQL FUNCTION
Hi

You can try this ...

create or replace package aa is

Type TQuantitesInfo IS RECORD

(

X NUMBER,

Y NUMBER

);

function t return TQuantitesInfo;

end;

/

create or replace package body aa;

create or replace function t is

e TQuantitesInfo

begin

e.X := 1;

e.Y := 2;

return e;

end;

end;

--
----------------------------------------------------------------------------
------------------------------
Trs cordialement
Denis Jeanneret - Directeur Technique, Division ORACLE
Epicentre - 529 Route des Vernes 74370 PRINGY
Tel: 04.5009.7000
Fax:04.50.66.92.19
web: www.epicentre.fr
----------------------------------------------------------------------------
------------------------------


Quote:
> Hello all,

> I would like to find the way to return recordsets (or collection)
> from a PL/SQL function as the return parameter and read the
> results afterwards from a Java JDBC application.

> Can anyone point me to resources or information of how can this
> be done?

> Thanks in advance,
> Regards,
> Giovanni



Mon, 21 Feb 2005 21:24:15 GMT
 how to return a recordset from PL/SQL FUNCTION

See The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Article title:
How do I return a set of records from a pl/sql procedure

This may help - although it is specifically talking about
returning a record set to VB

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

________England__________September , November

________USA (MI, TX)_____November

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Quote:

>Hello all,

>I would like to find the way to return recordsets (or collection)
>from a PL/SQL function as the return parameter and read the
>results afterwards from a Java JDBC application.

>Can anyone point me to resources or information of how can this
>be done?

>Thanks in advance,
>Regards,
>Giovanni



Mon, 21 Feb 2005 22:10:00 GMT
 how to return a recordset from PL/SQL FUNCTION
Try asktom.oracle.com, not www.asktom.com. If you are going to try to
take the m{*filter*}highground with responses such as yours, I suggest you
make your replies bomb-proof...


Tue, 22 Feb 2005 03:08:59 GMT
 how to return a recordset from PL/SQL FUNCTION
Hello all,

First of all thank you very much for your help.
I have already found the way and works fine except
one issue remaining:

I have this PL/SQL function which saves a new customer into
the Database, but it firstly checks whether there are
customer(s) already having some exact key fields (lastname
, zipcode, street name) to avoid this way having repeated
customers in the DB. I do the cheking in the same
inserting function to avoid roundtrip calls.

My problem is that I open the explicit cursor but do not
fetch it in the procedure itself but from the Java application. Since
I don't fetch it I don't have valid information in the cursor
attributes (%FOUND, %NOTFOUND, %ROWCOUNT,
etc) and this is a key condition to whether it found matching
customers and return immediatly or it did not find any matching
customers and continue to insert it as a new one.

The cursor attributes appear somehow inconsistent because I
have tried "IF SQL%FOUND THEN" but only works if the
table *is not* empty otherwise returns TRUE??? I have also
tried the "pvCUSTOMERS_RC%FOUND" where pvCUSTOMERS_RC
is the explicit cursor but since I do not fetch, it returns TRUE all
the time...

How can I solve this? The complete relevant snippet bellow (sorry
for the upper case but I don't know why when I think of any SQL
statement the upper case letters come into my mind, I have to
change that in the near future ;-))

TYPE FOGTYPE_CUSTOMERS_REFCURSOR is REF CURSOR RETURN
FOGVW_SEARCHCUSTOMERS%ROWTYPE;
 FUNCTION FOGFC_INSERTCUSTOMER (
 -- ...
 ) RETURN FOGTYPE_CUSTOMERS_REFCURSOR

 AS

 -- ...
 pvCUSTOMERS_RC  FOGTYPE_CUSTOMERS_REFCURSOR;

 -- Implicit cursors most of the way long...
 BEGIN
   -- Check for existing customers...
   IF ipFLAG_FORCE_CREATE = 0 THEN
    OPEN pvCUSTOMERS_RC FOR
        SELECT * FROM FOGVW_SEARCHCUSTOMERS
               WHERE UPPER(TRIM(LAST_NAME))=UPPER(TRIM(ipLAST_NAME))
          AND UPPER(TRIM(ZIPCODE))=UPPER(TRIM(ipZIPCODE))
          AND UPPER(TRIM(STREET_NAME))=UPPER(TRIM(ipSTREET_NAME));

  -- If finds that this customer was already registered then
  -- propose it otherwise continue inserting it as a new one...
  --This condition does not work properly!!!!!
    IF pvCUSTOMERS_RC%FOUND THEN
      RETURN pvCUSTOMERS_RC;
    END IF;
   END IF;
END FOGFC_INSERTCUSTOMER;

TIA,
Best Regards,
Giovanni


Quote:
> Hello all,

> I would like to find the way to return recordsets (or collection)
> from a PL/SQL function as the return parameter and read the
> results afterwards from a Java JDBC application.

> Can anyone point me to resources or information of how can this
> be done?

> Thanks in advance,
> Regards,
> Giovanni



Tue, 22 Feb 2005 22:58:23 GMT
 how to return a recordset from PL/SQL FUNCTION
On Fri, 6 Sep 2002 16:58:23 +0200, "Giovanni Azua"

Quote:

>  -- If finds that this customer was already registered then
>  -- propose it otherwise continue inserting it as a new one...
>  --This condition does not work properly!!!!!
>    IF pvCUSTOMERS_RC%FOUND THEN
>      RETURN pvCUSTOMERS_RC;
>    END IF;
>   END IF;
>END FOGFC_INSERTCUSTOMER;

>TIA,
>Best Regards,
>Giovanni

The PL/SQL manual tells you the %FOUND and %NOTFOUND condition are
only available after a FETCH.  The condition DOES work as expected and
documented.
You would better use appropiate keys and raise an exception instead of
the extra cursor, you also shouldn't use a resultset.

Also

I have this PL/SQL function which saves a new customer into
the Database, but it firstly checks whether there are
customer(s) already having some exact key fields (lastname
, zipcode, street name) to avoid this way having repeated
customers in the DB. I do the cheking in the same
inserting function to avoid roundtrip calls.

Your unnecessary SELECT still executes a context switch (switching
from PL/SQL to SQL and vice versa), it also generates a roundtrip.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address



Wed, 23 Feb 2005 01:28:17 GMT
 how to return a recordset from PL/SQL FUNCTION
Hello Sybrand,

Quote:
> I have this PL/SQL function which saves a new customer into
> the Database, but it firstly checks whether there are
> customer(s) already having some exact key fields (lastname
> , zipcode, street name) to avoid this way having repeated
> customers in the DB. I do the cheking in the same
> inserting function to avoid roundtrip calls.

> Your unnecessary SELECT still executes a context switch (switching
> from PL/SQL to SQL and vice versa), it also generates a roundtrip.

When I said roundtrip I was meaning exactly network traffic, having to
achieve a checking from the client-side front end whatever it is, and not
a context switching which always is inside the Database.

Regards,
Giovanni



Wed, 23 Feb 2005 03:41:41 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. Q: How to return an array (PL/SQL) table in PL/SQL function

2. Q: How to return an array (PL/SQL table) in PL/SQL function

3. PL/SQL: how to create a user-defined function returning appropriate char type

4. Help with INSERT PL/SQL function with returning clause

5. Function returns a PL/SQL table

6. Return a value from a javascript function to a PL/SQL procedure

7. PL/SQL - Returning resultset-like values from functions

8. Returning many values in a PL/SQL function

9. PL/SQL function core dumps when returning un-assigned REF CURSOR variable

10. PL/SQL function returning a table?

11. Cursor in PL/SQL function never returns data.

12. Function: Return PL /SQL table or VARCHAR2


 
Powered by phpBB® Forum Software