From: "Giovanni Azua" Newsgroups: comp.databases.oracle.server Subject: Re: PL/SQL cursor status without fetching??? Date: Mon, 9 Sep 2002 10:15:35 +0200 Lines: 82 Message-ID: References: NNTP-Posting-Host: mail.kdlabs.ch (195.49.119.90) X-Trace: fu-berlin.de 1031559326 60422414 195.49.119.90 (16 [114658]) X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 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 >