Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP!!! BEGINNER PL/SQL QUESTION

Re: HELP!!! BEGINNER PL/SQL QUESTION

From: Ruud de Koter <ruud_dekoter_at_hp.com>
Date: Wed, 28 Jul 1999 09:07:34 +0200
Message-ID: <379EAC36.9C72CBA4@hp.com>


Hi Alain,

As far as I can see, the point is not that you would have declared a wrong number of variables, but instead that you retrieve a wrong number of rows.

Apparently, at some place in your code, you perform a 'SELECT ... INTO ...' that does not return the number of rows expected (mostly one uses this to get a single row, in that case more than one row would be found). Given the nature of your program I suppose you fetch rows from your main cursor one by one, so that is not where to look for the problem.

Supposingly, you retrieve some reference data for checking and one of your queries does not contain the complete primary key in its WHERE-clause... I suggest you check your code with this in mind.

Regards,

Ruud de Koter

BTW: if you want reponse in email, you should make it easy to respond.

Alain C. Bonnemaison wrote:
>
> I apologize for the very large distribution of this message. I hope someone
> out here can help. I am writing a stored procedure that extracts data from a
> staging table, scrubs it and pushes it in several target tables. The
> procedure works fine but spits out at random some rows and return this error
> message: "Ora 01422: exact fetch returns more than requested # of rows."
>
> The Oracle documentation doesn't say much about the meaning of this error.
> Can someone please provide me with some insight as to what could be the
> reason of this error?? My code is structured as such:
>
> Begin cursor declaration
> select
> var1
> var2
> ...
> from staging_table
> End cursor declaration
> Begin data scrubing
> ...
> End data scrubing
> Begin scrubed data push to target tables <-- suspected area of error
> ...
> End scrubed data push to target tables
>
> I am guessing that this error may be due to the fact that I am declaring
> less variables in my cursor (or in my data scrubing code) than I insert into
> my target tables. Unless this is the other way around? Am I wrong? How can I
> fix this problem? Help!!! I'll use any assistance as this problem is driving
> me nuts!!!
>
> Thanks in advance for your time and your patience!!!
> --
> Alan. (Remove NOSPAM to reply to me directly).
> ---------------------------
> Email: abonnemaison_at_NOSPAM.profoundsolutions.com

--


Ruud de Koter                    HP OpenView Software Business Unit
Senior Software Engineer         IT Service Management Operation
Telephone: +31 (20) 514 15 89    Van Diemenstraat 200  
Telefax  : +31 (2) 514 15 90     PO Box 831
Telnet   : 547 - 1589            1000 AV  Amsterdam, the Netherlands
Email : ruud_dekoter_at_hp.com
internet: http://www.openview.hp.com/itsm

          http://www.openview.hp.com/assetview

intranet: http://ovweb.bbn.hp.com/itservicemanager

--------------------------------------------------------------------------------------
Received on Wed Jul 28 1999 - 02:07:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US