Re: Unexpected ORA-01422: exact fetch returns more than requested number of rows

On 26 Sep 2006 21:38:59 -0700, "john.mcafee_at_newscale.com" <juan.mcafee_at_gmail.com> wrote:

>I'm stumped by this one.
>
>The code below (followed by many similar fragments for different tables
>and columns) has been working to upgrade the schema of our database at
>various customer sites. For one customer, it worked fine in a first
>round of testing, but then when the schema was copied again from
>production for another round of testing, we began to get ORA-01422:
>exact fetch returns more than requested number of rows.
>
>Any ideas why this might be happening?

Yes. The affected table can occur in more than one schema. If the user running this code has privileges on both tables, your initial select will find both of them, as you nowhere take into account all_tab_columns has an attribute owner, which signifies the schema owner.
Secondly you seem to live under the misapprehension you need select intos for every individual column. Consequently, it is inefficient. I won't comment on the rest of the code, it is just horrible.

--
Sybrand Bakker, Senior Oracle DBA