Home » SQL & PL/SQL » SQL & PL/SQL » Invalid ORA-01422 Error (Oracle Forms 9.0.4.3 Oracle DB 10.2.0.3)
Invalid ORA-01422 Error [message #414340] Tue, 21 July 2009 12:52 Go to next message
goldflash22
Messages: 1
Registered: July 2009
Junior Member
Hello,

I'm currenty trying to update an interface from an Oracle Forms app to a view on a 10G database. When I query the view to check if the user is a valid user of the interfacing database/application it returns a ORA-01422 Error.

Here is my query in Forms:

select userid into string from recharge_link_user.maxuser
where userid = upper(userid);


When I execute this (modified)query in Toad, against the same view, it returns ONE row.

select userid from recharge_link_user.maxuser
where userid = upper('admin');


I have even retrieved all rows in the view and verified there is only one entry for 'admin' by sorting.

It seems as though Oracle is throwing this error for a few different queries and not just this one. Is this an issue between the version of Forms and 10G? Am I missing something here?

Thanks
Re: Invalid ORA-01422 Error [message #414341 is a reply to message #414340] Tue, 21 July 2009 12:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
01422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested
Re: Invalid ORA-01422 Error [message #414413 is a reply to message #414340] Wed, 22 July 2009 01:28 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
select userid into string from recharge_link_user.maxuser
where userid = upper(userid);

select userid from recharge_link_user.maxuser
where userid = upper('admin');

Both queries may be run in pure SQL; however they return different result:
The first one returns all rows, where USERID column does not contain lower case letter.
The second one returns all rows, where USERID column contain value 'ADMIN'.

You may have a variable named USERID in Forms; however it will not be used in this query. The only way to use a variable in query is renaming it. The good coding convention is adding a prefix to a variable name, which identifies its scope (e.g. L_ for local variables, P_ for parameters etc.). You may use any other naming convention though.
Previous Topic: Compilation error while CREATE FUNCTION
Next Topic: Help in Retrieving Oracle Keywords Defined Columns
Goto Forum:
  


Current Time: Sat Nov 02 12:49:15 CDT 2024