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

Hello Thomas,

Again and again ;-) thank you a lot for your continous support!

And yes here in Switzerland there are zip codes like CH-8048 ch-8048 :-)

Thank you again
Best Regards,
Giovanni

> 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 Mon Sep 09 2002 - 03:15:35 CDT