Re: PL/SQL cursor status without fetching???

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 6 Sep 2002 17:03:40 -0700
Message-ID: <albfos025cb@drn.newsguy.com>

In article <alagg9$1oqfm3$1_at_ID-114658.news.dfncis.de>, "Giovanni says...
>
>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
>
> 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));

I sure hope you know ALL ABOUT function based indexes, or we have another java program going down the tubes real fast (user VARCHAR2, skip the trim, whats up with upper(zip_code)?).....

anyway, You'll have to FETCH at least a row to see. So, something like:

begin

   select * into l_rec
     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))
      AND ROWNUM = 1;

    p_found_one_for_you := 1;

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

exception

   when no_data_found

         insert into ......
         p_found_one_for_you := 0;
         open pvCustomers_Rc for select * from fogvw_searchcustomers where 1=0;

end;     

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

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Sep 06 2002 - 19:03:40 CDT