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

On Fri, 6 Sep 2002 16:58:23 +0200, "Giovanni Azua" <bravegag_at_hotmail.com> wrote:

> -- 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 Received on Fri Sep 06 2002 - 12:28:17 CDT