Re: how to return a recordset from PL/SQL FUNCTION

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Fri, 6 Sep 2002 16:58:23 +0200
Message-ID: <alafq4$1ov1l7$1@ID-114658.news.dfncis.de>

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

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:al7298$1muk8k$1_at_ID-114658.news.dfncis.de...

> 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
>
>

Received on Fri Sep 06 2002 - 09:58:23 CDT