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. -
CREATE OR REPLACE PROCEDURE proc_PropSearch
-
(v_postcode IN Property.PostCode%TYPE,
-
v_propertyid OUT Property.PropertyID%TYPE,
-
v_propertytypeid OUT Property.PropertyTypeID%TYPE,
-
v_houseflatnum OUT Property.HouseFlatNumber%TYPE,
-
v_street OUT Property.Street%TYPE,
-
v_city OUT Property.City%TYPE,
-
v_county OUT Property.County%TYPE,
-
v_furnished OUT Property.Furnished%TYPE,
-
v_noofbedrooms OUT Property.NoOfBedrooms%TYPE,
-
v_noofensuites OUT Property.NoOfEnSuites%TYPE,
-
v_noofbathrooms OUT Property.NoOfBathrooms%TYPE,
-
v_receptionrooms OUT Property.NoOfReceptionRooms%TYPE,
-
v_propertydesc OUT Property.PropertyDesc%TYPE,
-
v_available OUT Property.Available%TYPE)
-
-
IS
-
BEGIN
-
SELECT PropertyID, PropertyTypeID, HouseFlatNumber, Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRooms, PropertyDesc, Available
-
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
-
FROM Property
-
WHERE PostCode = v_postcode;
-
END proc_PropSearch;
/
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
6 25158
That is because the SQl statment is returning more than one record.
Try to use cursor.
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?
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.
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: - -
SET SERVEROUTPUT ON
-
CREATE OR REPLACE PROCEDURE proc_PropSearch
-
(v_postcode IN Property.PostCode%TYPE,
-
v_propertyid OUT Property.PropertyID%TYPE,
-
v_propertytypeid OUT Property.PropertyTypeID%TYPE,
-
v_houseflatnum OUT Property.HouseFlatNumber%TYPE,
-
v_street OUT Property.Street%TYPE,
-
v_city OUT Property.City%TYPE,
-
v_county OUT Property.County%TYPE,
-
v_furnished OUT Property.Furnished%TYPE,
-
v_noofbedrooms OUT Property.NoOfBedrooms%TYPE,
-
v_noofensuites OUT Property.NoOfEnSuites%TYPE,
-
v_noofbathrooms OUT Property.NoOfBathrooms%TYPE,
-
v_receptionrooms OUT Property.NoOfReceptionRooms%TYPE,
-
v_propertydesc OUT Property.PropertyDesc%TYPE,
-
v_available OUT Property.Available%TYPE)
-
-
AS
-
CURSOR Cursor_PropSearch IS
-
SELECT PropertyID, PropertyTypeID, HouseFlatNumber, Street, City, County, Furnished, NoOfBedrooms, NoOfEnSuites, NoOfBathrooms, NoOfReceptionRooms, PropertyDesc, Available
-
FROM Property
-
WHERE PostCode = v_postcode;
-
-
BEGIN
-
OPEN Cursor_PropSearch;
-
-
LOOP
-
FETCH Cursor_PropSearch
-
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;
-
EXIT WHEN Cursor_PropSearch%NOTFOUND OR
-
Cursor_PropSearch%NOTFOUND IS NULL;
-
-
DBMS_OUTPUT.PUT_LINE
-
(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));
-
-
END LOOP;
-
-
CLOSE Cursor_PropSearch;
-
END proc_PropSearch;
-
/
-
If you simply want to display the values using DBMS_OUTPUT why using all those OUT parameters ?
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 instead of returning a recordset.
CREATE PROCEDURE Test (@param1 int, @param2 int OUTPUT, @param3 int
OUTPUT) AS
SELECT field2, field3 FROM Table WHERE field1 = @param1
|
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 into something I cannot figure out.
Basically....what I am doing is a couple of inserts or updates depending on
what is being passed.
|
by: randy.p.ho |
last post by:
Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
|
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
secondary key will be null. For example :
id k1 k2 k3 k4 k5 k6 k7 k8 k9 k10 data
-------------------------------------------------
0 1 1 - - - - - - - - test0
1 1 1 1 - - - - - - - test1
2 1 1 2 - - - - - - - test2
|
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 results based on all products
2. Multiple tables based on individual product names.
I am getting data by calling the same stored procedure multiple
times... for the single set of data I use "product like '%'"
To get the data for individual products, I am using a cursor to parse
| |
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
happy to return a set, but I can't seem to make that work either.
Anyway,
what's wrong with this?)
Version is:
$ rpm -q postgresql
|
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 problem RESULT_SET_LOCATOR in *LIBL type *SQLUDT not found.
|
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 movies and individual user ratings.
The code needs to get a count of the ratings, then the sum of them to get the percentage.
I am getting a error
Exception Details: System.IndexOutOfRangeException: columncount
|
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 following data source in my .aspx file:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |