472,333 Members | 1,127 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,333 software developers and data experts.

Stored Procedure returning multiple values

129 100+
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_PROPSEARCH", 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.
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PROCEDURE proc_PropSearch
  2. (v_postcode IN Property.PostCode%TYPE,
  3. v_propertyid OUT Property.PropertyID%TYPE,
  4. v_propertytypeid OUT Property.PropertyTypeID%TYPE,
  5. v_houseflatnum OUT Property.HouseFlatNumber%TYPE,
  6. v_street OUT Property.Street%TYPE,
  7. v_city OUT Property.City%TYPE,
  8. v_county OUT Property.County%TYPE,
  9. v_furnished OUT Property.Furnished%TYPE,
  10. v_noofbedrooms OUT Property.NoOfBedrooms%TYPE,
  11. v_noofensuites OUT Property.NoOfEnSuites%TYPE,
  12. v_noofbathrooms OUT Property.NoOfBathrooms%TYPE,
  13. v_receptionrooms OUT Property.NoOfReceptionRooms%TYPE,
  14. v_propertydesc OUT Property.PropertyDesc%TYPE,
  15. v_available OUT Property.Available%TYPE)
  16.  
  17. IS
  18. BEGIN
  19.     SELECT PropertyID, PropertyTypeID, HouseFlatNumber, Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRooms, PropertyDesc, Available
  20.     INTO v_propertyid, v_propertytypeid, v_houseflatnum, v_street, v_city, v_county, v_furnished, v_noofbedrooms, v_noofensuites, v_noofbathrooms, v_receptionrooms, v_propertydesc, v_available 
  21.     FROM Property
  22.     WHERE PostCode = v_postcode;
  23. END proc_PropSearch;
/

VARIABLE g_propertyid NUMBER
VARIABLE g_propertytypeid 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_receptionrooms NUMBER
VARIABLE g_propertydesc VARCHAR2(50)
VARIABLE g_available CHAR(3)

EXECUTE proc_PropSearch ('S1 4SG', :g_propertyid, :g_propertytypeid, :g_houseflatnum, :g_street, :g_city, :g_county, :g_furnished, :g_noofbedrooms, :g_noofensuites, :g_noofbathrooms, :g_receptionrooms, :g_propertydesc, :g_available);
PRINT g_propertyid g_propertytypeid g_houseflatnum g_street g_city g_county g_furnished g_noofbedrooms g_noofensuites g_noofbathrooms g_receptionrooms g_propertydesc g_available

This is created in PL/SQL.
Thank you for help
Apr 12 '08 #1
6 24949
debasisdas
8,127 Expert 4TB
That is because the SQl statment is returning more than one record.

Try to use cursor.
Apr 14 '08 #2
Constantine AI
129 100+
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?
Apr 14 '08 #3
debasisdas
8,127 Expert 4TB
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.
Apr 14 '08 #4
Constantine AI
129 100+
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: -
Expand|Select|Wrap|Line Numbers
  1. SET SERVEROUTPUT ON
  2. CREATE OR REPLACE PROCEDURE proc_PropSearch
  3. (v_postcode         IN     Property.PostCode%TYPE,
  4. v_propertyid         OUT     Property.PropertyID%TYPE,
  5. v_propertytypeid     OUT     Property.PropertyTypeID%TYPE,
  6. v_houseflatnum         OUT     Property.HouseFlatNumber%TYPE,
  7. v_street         OUT     Property.Street%TYPE,
  8. v_city             OUT     Property.City%TYPE,
  9. v_county         OUT     Property.County%TYPE,
  10. v_furnished         OUT     Property.Furnished%TYPE,
  11. v_noofbedrooms         OUT     Property.NoOfBedrooms%TYPE,
  12. v_noofensuites         OUT     Property.NoOfEnSuites%TYPE,
  13. v_noofbathrooms     OUT     Property.NoOfBathrooms%TYPE,
  14. v_receptionrooms     OUT     Property.NoOfReceptionRooms%TYPE,
  15. v_propertydesc         OUT     Property.PropertyDesc%TYPE,
  16. v_available         OUT     Property.Available%TYPE)
  17.  
  18. AS
  19. CURSOR Cursor_PropSearch IS
  20. SELECT PropertyID, PropertyTypeID, HouseFlatNumber, Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRooms, PropertyDesc, Available   
  21. FROM Property
  22. WHERE PostCode = v_postcode;
  23.  
  24. BEGIN
  25. OPEN Cursor_PropSearch;
  26.  
  27. LOOP
  28.     FETCH Cursor_PropSearch 
  29.     INTO v_propertyid, v_propertytypeid, v_houseflatnum, v_street, v_city, v_county, v_furnished, v_noofbedrooms, v_noofensuites, v_noofbathrooms, v_receptionrooms, v_propertydesc, v_available; 
  30.     EXIT WHEN Cursor_PropSearch%NOTFOUND OR 
  31.         Cursor_PropSearch%NOTFOUND IS NULL;
  32.  
  33. DBMS_OUTPUT.PUT_LINE
  34. (TO_CHAR(v_propertyid) || TO_CHAR(v_propertytypeid) || TO_CHAR(v_houseflatnum) || TO_CHAR(v_street) || TO_CHAR(v_city) || TO_CHAR(v_county) || TO_CHAR(v_furnished) || TO_CHAR(v_noofbedrooms) || TO_CHAR(v_noofensuites) || TO_CHAR(v_noofbathrooms) || TO_CHAR(v_receptionrooms) || TO_CHAR(v_propertydesc) || TO_CHAR(v_available));
  35.  
  36. END LOOP;
  37.  
  38. CLOSE Cursor_PropSearch;
  39. END proc_PropSearch;
  40. /
  41.  
Apr 15 '08 #5
debasisdas
8,127 Expert 4TB
If you simply want to display the values using DBMS_OUTPUT why using all those OUT parameters ?
Apr 15 '08 #6
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
Apr 22 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Samuel Hon | last post by:
Hi I'm not sure what the best approach for this is: I have a stored procedure which I would like to use to return several output values...
5
by: Member | last post by:
Hi All. Maybe someone in here could help on this too.... Uusally I can return a value from a stored procedure without any problem. Today I ran...
4
by: randy.p.ho | last post by:
Using JDBC, is there a way to call a stored procedure with multiple return values? Thanks.
3
by: Gaz | last post by:
I have a table which has 10 columns which make up the secondary key. 1 or more of these columns can be set but the remaining columns in the...
3
by: wxbuff | last post by:
I have a report based on our product names that consists of two parts. Both insert data into a temporary table. 1. A single grouped set of...
9
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be...
0
by: adougall | last post by:
I would like to know what the outcome was on the topic for Nested Stored Procedure returning result sets in DB2 on AS/400 was. I am getting the same...
4
by: vertigo262 | last post by:
Is it possible to use to select statements in a stored procedure? I am building a movie rating system, what I am doing is creating a table with...
3
by: Homer J. Simpson | last post by:
I have the following stored procedure: ALTER PROCEDURE . AS BEGIN SET NOCOUNT ON; SELECT COUNT(*) FROM QUICKNOTES END ....and the...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.