Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
|
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 15th, 2004, 09:35 PM
Authorized User
 
Join Date: Jan 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error about ShoppingcartList Procedure

Hi All
I found this error from my web application

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "SCOTT.SHOPPINGCARTLIST", line 9 ORA-06512: at line 1
*********************************
And this is a procedure for ShoppingCartList

(CARTID IN VARCHAR2)
As
PRD_PRODUCTID VARCHAR2(4);
PRD_PRODUCTTITLE VARCHAR2(50);
QUANTITY NUMBER(4);
PRD_UNITPRICE NUMBER(10,2);
EXTENEDAMOUNT NUMBER(10,2);
begin
SELECT TBLPRODUCT.PRD_PRODUCTID,TBLPRODUCT.PRD_PRODUCTTIT LE,
SHOPPINGCART.QUANTITY,
TBLPRODUCT.PRD_UNITPRICE,
TBLPRODUCT.PRD_UNITPRICE*SHOPPINGCART.QUANTITY
INTO
PRD_PRODUCTID,PRD_PRODUCTTITLE,QUANTITY,PRD_UNITPR ICE,EXTENEDAMOUNT
FROM
TBLPRODUCT,SHOPPINGCART
WHERE
TBLPRODUCT.PRD_PRODUCTID=SHOPPINGCART.PRODUCTID
AND
SHOPPINGCART.CARTID=CARTID
ORDER BY
TBLPRODUCT.PRD_PRODUCTTITLE;
end;

*********************************
Have anyone know about this error?

Thanks in advance

Blueman137


 
Old March 16th, 2004, 08:14 AM
aas aas is offline
Authorized User
 
Join Date: Mar 2004
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to aas
Default

If your query returned more than one record you can't use this query with INTO.

If you want select only one record, do add ROWNUM=1 into WHERE scope
If not. You must define of the your a query as cursor and to use it in one of next scope:

exmpl_1:
FOR row1 IN crs1
LOOP
     ...
END LOOP;

exmpl_2:
OPEN crs1;
LOOP
     FETCH crs1 INTO row1;
EXIT WHEN crs1%no_found;
     ...
END LOOP;

where:
 crs1 - declared cursor
 row1 - recordtype from cursor


 
Old March 18th, 2004, 02:52 AM
Authorized User
 
Join Date: Jan 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi all
I'd like to retrieve data from shoppingcart table and show it in datagrid (A.aspx). So, Is this a cursor as previous reply,right?

DECLARE
cursor cCartList(CartID in varchar2) is
SELECT t.PRD_PRODUCTID,t.PRD_PRODUCTTITLE, s.QUANTITY, t.PRD_UNITPRICE, t.PRD_UNITPRICE * s.QUANTITY Sum
FROM TBLPRODUCT t, SHOPPINGCART t
WHERE t.PRD_PRODUCTID = s.PRODUCTID
AND s.CARTID = CARTID
ORDER BY t.PRD_PRODUCTTITLE;

My_ProductID VarChar;
My_ProductTitle VarChar;
My_Quantity Number;
My_UnitPrice Number;
My_Sum Number;

begin
for c1 in cCartList loop
Fetch c1 INTO My_ProductID,My_ProductTitle,My_Quantity,My_UnitPr ice,My_Sum;
EXIT WHEN c1%NOTFOUND;
INSERT NTO Temp VALUES (My_ProductID,My_ProductTitle,My_Quantity,My_UnitP rice,My_Sum);
COMMIT;
end loop;
end;
/

By Temp Table,I'll use its value to show in datagrid


Thanks

Blueman137


 
Old March 18th, 2004, 05:25 AM
aas aas is offline
Authorized User
 
Join Date: Mar 2004
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to aas
Default

Why do you use a temporary table, and not do use a VIEW?

I do not work with ASP, but i think that principle of operation it not very different from Developer or Delphi.

Is always, after change of any informations in DB you must requery data in datagrid, but:
1. If you will use a temporary table, before every query you must execute procedure and then execute your query
2. If you will use a view, you will always execute of your query only.

The second way is more optimised as the first








Similar Threads
Thread Thread Starter Forum Replies Last Post
Error When Running Stored Procedure Dwizz SQL Server 2000 4 May 15th, 2007 09:07 AM
stored procedure error saurabh321 ASP.NET 2.0 Basics 0 December 20th, 2006 09:25 AM
Stored Procedure Error in MS Access belete Access 2 June 24th, 2004 02:05 PM
Stored procedure alter table error Justine Oracle 2 June 4th, 2004 09:51 AM
Invalid procedure call Error pavel Pro VB 6 2 January 30th, 2004 10:00 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.