Stored Procedure returning multiple values

[Constantine AI]

Can anyone help?

Ive created the below stored procedure to allow an input parameter of post code to be entered to display properties which have this post code. The procedure creates fine but because i have two properties with the post code 'S1 4SG', the execution of the procedure fails, it gives the error:

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "CBRAID.PROC_PR OPSEARCH", line 19
ORA-06512: at line 1

Im sure procedures can return more than one value, so does anyone know why its doesnt work or what i can do to make it work?

Below is the procedure and also how I execute and view it in the notepad file attached.
[code=oracle]
CREATE OR REPLACE PROCEDURE proc_PropSearch
(v_postcode IN Property.PostCo de%TYPE,
v_propertyid OUT Property.Proper tyID%TYPE,
v_propertytypei d OUT Property.Proper tyTypeID%TYPE,
v_houseflatnum OUT Property.HouseF latNumber%TYPE,
v_street OUT Property.Street %TYPE,
v_city OUT Property.City%T YPE,
v_county OUT Property.County %TYPE,
v_furnished OUT Property.Furnis hed%TYPE,
v_noofbedrooms OUT Property.NoOfBe drooms%TYPE,
v_noofensuites OUT Property.NoOfEn Suites%TYPE,
v_noofbathrooms OUT Property.NoOfBa throoms%TYPE,
v_receptionroom s OUT Property.NoOfRe ceptionRooms%TY PE,
v_propertydesc OUT Property.Proper tyDesc%TYPE,
v_available OUT Property.Availa ble%TYPE)

IS
BEGIN
SELECT PropertyID, PropertyTypeID, HouseFlatNumber , Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRo oms, PropertyDesc, Available
INTO v_propertyid, v_propertytypei d, v_houseflatnum, v_street, v_city, v_county, v_furnished, v_noofbedrooms, v_noofensuites, v_noofbathrooms , v_receptionroom s, v_propertydesc, v_available
FROM Property
WHERE PostCode = v_postcode;
END proc_PropSearch ;[/code]
/

VARIABLE g_propertyid NUMBER
VARIABLE g_propertytypei d NUMBER
VARIABLE g_houseflatnum CHAR(4)
VARIABLE g_street VARCHAR2(30)
VARIABLE g_city VARCHAR2(30)
VARIABLE g_county VARCHAR2(30)
VARIABLE g_furnished CHAR(3)
VARIABLE g_noofbedrooms NUMBER
VARIABLE g_noofensuites NUMBER
VARIABLE g_noofbathrooms NUMBER
VARIABLE g_receptionroom s NUMBER
VARIABLE g_propertydesc VARCHAR2(50)
VARIABLE g_available CHAR(3)

EXECUTE proc_PropSearch ('S1 4SG', :g_propertyid, :g_propertytype id, :g_houseflatnum , :g_street, :g_city, :g_county, :g_furnished, :g_noofbedrooms , :g_noofensuites , :g_noofbathroom s, :g_receptionroo ms, :g_propertydesc , :g_available);
PRINT g_propertyid g_propertytypei d g_houseflatnum g_street g_city g_county g_furnished g_noofbedrooms g_noofensuites g_noofbathrooms g_receptionroom s g_propertydesc g_available

This is created in PL/SQL.
Thank you for help

[debasisdas]

That is because the SQl statment is returning more than one record.

Try to use cursor.

[Constantine AI]

That is because the SQl statment is returning more than one record.

Try to use cursor.

I am new to PL/SQL, could you show me the syntax of how the cursor should work with the stored procedure?

[debasisdas]

I am new to PL/SQL, could you show me the syntax of how the cursor should work with the stored procedure?

Please check for the articles in the HowTo section of Oracle in this forum for some related discussions.

[Constantine AI]

Hi I have incorporated a cursor into the stored procedure to return multiple values. The procedure with the cursor creates fine but it doesnt display the results in the DBMS_OUTPUT.PUT _LINE even when ive out SET SERVEROUTPUT ON. Can anybody see the problem?

Here is the code: -
[code=oracle]
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE proc_PropSearch
(v_postcode IN Property.PostCo de%TYPE,
v_propertyid OUT Property.Proper tyID%TYPE,
v_propertytypei d OUT Property.Proper tyTypeID%TYPE,
v_houseflatnum OUT Property.HouseF latNumber%TYPE,
v_street OUT Property.Street %TYPE,
v_city OUT Property.City%T YPE,
v_county OUT Property.County %TYPE,
v_furnished OUT Property.Furnis hed%TYPE,
v_noofbedrooms OUT Property.NoOfBe drooms%TYPE,
v_noofensuites OUT Property.NoOfEn Suites%TYPE,
v_noofbathrooms OUT Property.NoOfBa throoms%TYPE,
v_receptionroom s OUT Property.NoOfRe ceptionRooms%TY PE,
v_propertydesc OUT Property.Proper tyDesc%TYPE,
v_available OUT Property.Availa ble%TYPE)

AS
CURSOR Cursor_PropSear ch IS
SELECT PropertyID, PropertyTypeID, HouseFlatNumber , Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRo oms, PropertyDesc, Available
FROM Property
WHERE PostCode = v_postcode;

BEGIN
OPEN Cursor_PropSear ch;

LOOP
FETCH Cursor_PropSear ch
INTO v_propertyid, v_propertytypei d, v_houseflatnum, v_street, v_city, v_county, v_furnished, v_noofbedrooms, v_noofensuites, v_noofbathrooms , v_receptionroom s, v_propertydesc, v_available;
EXIT WHEN Cursor_PropSear ch%NOTFOUND OR
Cursor_PropSear ch%NOTFOUND IS NULL;

DBMS_OUTPUT.PUT _LINE
(TO_CHAR(v_prop ertyid) || TO_CHAR(v_prope rtytypeid) || TO_CHAR(v_house flatnum) || TO_CHAR(v_stree t) || TO_CHAR(v_city) || TO_CHAR(v_count y) || TO_CHAR(v_furni shed) || TO_CHAR(v_noofb edrooms) || TO_CHAR(v_noofe nsuites) || TO_CHAR(v_noofb athrooms) || TO_CHAR(v_recep tionrooms) || TO_CHAR(v_prope rtydesc) || TO_CHAR(v_avail able));

END LOOP;

CLOSE Cursor_PropSear ch;
END proc_PropSearch ;
/
[/code]

[debasisdas]

If you simply want to display the values using DBMS_OUTPUT why using all those OUT parameters ?

[BMantri]

Hello,

i have executed by trying a test procedure and i got the output :)
create or replace procedure test(p_name varchar2, p_type out varchar2)
is
cursor c1 is select tabtype from tab where tname = p_name;
begin
open c1;
loop
fetch c1 into p_type;
exit when c1%notfound;
dbms_output.put _line('The type is : ' || p_type);
end loop;
close c1;
end;
/


and the calling procedure as

SQL> declare
2 p_name varchar2(20) := 'TEST1';
3 p_type varchar2(20);
4 begin
5 test(p_name, p_type);
6 end;
7 /
The type is : TABLE

PL/SQL procedure successfully completed.


can you please conpare your procedure with these two?


Regards,

BMantri