Home » SQL & PL/SQL » SQL & PL/SQL » Problems with parmeter in procedures
Problems with parmeter in procedures [message #36542] Tue, 04 December 2001 04:23 Go to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: help for retrieve the columns name from a table
Next Topic: primary key
Goto Forum:
  


Current Time: Mon Apr 06 01:15:00 CDT 2020