Problems with parmeter in procedures [message #36542] |
Tue, 04 December 2001 04:23 |
Steve
Messages: 190 Registered: September 1999
|
Senior Member |
|
|
I have defined a couple of stored procedures in SCHEMA test
CREATE OR REPLACE PROCEDURE foo(param VARCHAR2)
...
SELECT id
INTO v_id
FROM ACCESS_MANAGER.USERS u WHERE u.USERNAME=param;
...
CREATE OR REPLACE PROCEDURE foo1(param VARCHAR2)
...
SELECT id
INTO v_id
FROM ACCESS_MANAGER.USERS u WHERE u.USERNAME=param;
..
The procedures called sequently from a SQL+ script:
CONNECT system/manager@m3s;
SET VERIFY OFF
ACCEPT p_name PROMPT 'Please enter name:'
DECLARE
v_name TESTSCHEMA.USERS.name%TYPE := '&p_name';
test.foo(v_name);
test.foo1(v_name);
END;
/
If i execute these two calls i get the error
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "test.foo1", line 5
What is going wrong here ?
----------------------------------------------------------------------
|
|
|
Re: Problems with parmeter in procedures [message #36543 is a reply to message #36542] |
Tue, 04 December 2001 04:57 |
tinel
Messages: 42 Registered: November 2001
|
Member |
|
|
That error means that the number of rows returned form your select statement in more then one, when you use select into the row returned must not be more then one.
The error may be generated if your username value is not unique.
Bye
----------------------------------------------------------------------
|
|
|