PL/SQL cursor status without fetching???

Hi all,

I am developing a Oracle 9i J2EE application which at some point needs to insert a new customer, this is done from a PL/SQL function which first checks there are no existing customers in the DB which seem to match the new customer. If there are found matching customers the function returns immediatly to propose them to the user otherwise continue to add a new customer with the data provided.

The point is that my explicit cursor does not fetch any data whithin the procedure itself but from the Java application. and hence I don't have valid information regarding to whether it found or not matching customers (%FOUND, %ROWCOUNT , etc).

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)

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

• ... ) RETURN FOGTYPE_CUSTOMERS_REFCURSOR

• ... 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;
    • At this point continue to insert the customer as a new one...
    • ...
    • Dummy return, would be nice to have something like:
    • RETURN NULL or SELECT NULL INTO pvCUSTOMERS_RC FROM DUAL; RETURN pvCUSTOMERS_RC;

END FOGFC_INSERTCUSTOMER; TIA,
Best Regards,
Giovanni Received on Fri Sep 06 2002 - 10:10:12 CDT