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

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

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

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