asked on

PL/SQL - program error - using Oracle 10g SQL Developer

When I run the code below, Dbms output gives me this error: ERROR OTHERS ORA-01427: single-row subquery returns more than one row. Why?

DECLARE
vPrice INVENTORY.Price%TYPE;
--vPartid INVENTORY.PartID%type;
vDescription INVENTORY.Description%TYPE;
vPartid NUMBER :='1005' ; ----- Assign the vPartid variable a value of 1005.
---WHERE clause should check for the value in the variable vPartid.

BEGIN
SELECT Description, Price
INTO vDescription, vPrice
FROM INVENTORY
WHERE vPartid = (SELECT INVENTORY.Partid FROM INVENTORY);

DBMS_OUTPUT.PUT_LINE ('Part Number ' || TRIM(vPartid) ||
' is a ' || TRIM(vDescription) ||
' and costs '|| to_char(vPrice,'fm$999,99.00') );

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR OTHERS ' || SQLERRM);
END;

ASKER CERTIFIED SOLUTION

awking00

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

ajexpert

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

awking00

This is backwards
select ...
from inventory
where vpartid = (select partid from inventory) => This is sure to return many rows

select ...
from inventory
where partid = vpartid => This is only likely to select one row since I assume each partid has only one description and price combination.

ocdc

ASKER

This one works but gives me the first record in the table. It needs to give me the one that is in the WHERE clause. Partid 1005. So it is not seeing that.

DECLARE
vPrice INVENTORY.Price%TYPE;
--vPartid INVENTORY.PartID%type;
vDescription INVENTORY.Description%TYPE;
vPartid NUMBER := '1005'; -----Assign the vPartid variable a value of 1005.
----WHERE clause should check for the value in the variable vPartid.

BEGIN
SELECT Description, Price
INTO vDescription, vPrice
FROM INVENTORY
WHERE Partid = (SELECT INVENTORY.Partid
FROM INVENTORY
WHERE ROWNUM = 1);

DBMS_OUTPUT.PUT_LINE( 'Part Number '
|| TRIM (vPartid)
|| ' is a '
|| TRIM (vDescription)
|| ' and costs '
|| TO_CHAR (vPrice, 'fm$999,99.00'));
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('ERROR OTHERS ' || SQLERRM);
END;

ocdc

ASKER

Thanks. it works after modifying the where clause where partid = vpartid

ajexpert

DECLARE
   vPrice         INVENTORY.Price%TYPE;
   --vPartid INVENTORY.PartID%type;
   vDescription   INVENTORY.Description%TYPE;
   vPartid        NUMBER := '1005'; ----- Assign the vPartid variable a value of 1005.
---WHERE clause should check for the value in the variable vPartid.

BEGIN
   SELECT   Description, Price
     INTO   vDescription, vPrice
     FROM   INVENTORY
    WHERE   Partid = (SELECT   INVENTORY.Partid
                         FROM   INVENTORY
                        WHERE   partid = vpartid);

   DBMS_OUTPUT.PUT_LINE(   'Part Number '
                        || TRIM (vPartid)
                        || ' is a '
                        || TRIM (vDescription)
                        || ' and costs '
                        || TO_CHAR (vPrice, 'fm$999,99.00'));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR OTHERS  ' || SQLERRM);
END;

Open in new window