asked on

PL/SQL - Program Syntax - using Oracle 10g SQL Developer

I am getting the following errors:

Error report:
ORA-06550: line 3, column 28:
PLS-00302: component 'ENAME' must be declared
ORA-06550: line 3, column 18:
PL/SQL: Item ignored
ORA-06550: line 18, column 8:
PL/SQL: ORA-00918: column ambiguously defined
ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 22, column 27:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 22, column 4:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

The program prompts me to enter a CUSTOMERID from my keyboard that has orders. If the entered CUSTOMERID has orders then show Customername(ename) and the total amount of the all sales for that CUSTOMERID. Currently, I am getting the error list above after entering a CUSTOMERID. Can't figure it out Why I am getting the error .?

Also, If the CUSTOMERID has no orders then the ERROR handlers below should kick in.

P.S. need to do this without using CURSOR

DECLARE
--vcustid CUSTOMERS.empid%type;
vEname CUSTOMERS.ename%type;

BEGIN
SELECT C.custid AS Customerid, C.cname AS CustomerName,
to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99') AS TotalValue
FROM CUSTOMERS C
LEFT OUTER JOIN ORDERS O
ON O.custid = C.custid

LEFT OUTER JOIN ORDERITEMS OI
ON O.orderid = OI.orderid

LEFT OUTER JOIN INVENTORY I
ON OI.partid = I.partid

WHERE C.custid = &custid --OR O.custid IS NOT NULL
GROUP BY C.custid,C.cname
ORDER BY TotalValue ASC;

DBMS_OUTPUT.put_line( (vEname)
|| ' ' || to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99'));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('ERROR OTHERS ' || SQLERRM);
END;

SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

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

See answer

ocdc

ASKER

thanks. I noticed the typo as well. I modified it including your suggestion. But now I am getting 'PL/SQL: ORA-00904: "TOTALVALUE": invalid identifier" error.

DECLARE
vcustid CUSTOMERS.custid%type;
vCname CUSTOMERS.cname%type;
vTotalValue varchar2(100);

BEGIN

SELECT C.custid AS Customerid, C.cname AS CustomerName,
to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99') AS vTotalValue
INTO vCustId, vCname, vTotalValue
FROM CUSTOMERS C
LEFT OUTER JOIN ORDERS O
ON O.custid = C.custid

LEFT OUTER JOIN ORDERITEMS OI
ON O.orderid = OI.orderid

LEFT OUTER JOIN INVENTORY I
ON OI.partid = I.partid

WHERE C.custid = &custid --OR O.custid IS NOT NULL
GROUP BY C.custid,C.cname
ORDER BY TotalValue ASC;

DBMS_OUTPUT.put_line( (vCname)
|| ' ' || (vTotalValue ));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('ERROR OTHERS ' || SQLERRM);
END;

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

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

See answer

ocdc

ASKER

yes, got it. The final version below works now. It gives the Customername and total value if CUSTOMERID has orders. It also works for the ones that do not have orders. I am getting those values now. But when I entered CUSTOMERID that doesn't exist in the database then it runs the code but no error handeling message kicks in..

I would like to give out message that CUSTOMERID doesn't exist in the database.

DECLARE
vcustid CUSTOMERS.custid%type;
vCname CUSTOMERS.cname%type;
vTotalValue varchar2(100); -- := to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99');

BEGIN

SELECT C.custid AS Customerid, C.cname AS CustomerName,
to_char((SUM(NVL(OI.qty, 0) * NVL(I.price, 0))), '$99,999.99') AS vTotalValue
INTO vCustId, vCname,vTotalValue
FROM CUSTOMERS C
LEFT OUTER JOIN ORDERS O
ON O.custid = C.custid

LEFT OUTER JOIN ORDERITEMS OI
ON O.orderid = OI.orderid

LEFT OUTER JOIN INVENTORY I
ON OI.partid = I.partid

WHERE C.custid = &custid --OR O.custid IS NOT NULL
GROUP BY C.custid,C.cname
ORDER BY vTotalValue ASC;

DBMS_OUTPUT.put_line( (vCname)
|| ' ' || (vTotalValue ));
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('ERROR OTHERS ' || SQLERRM);
END;

slightwv (䄆 Netminder)

>>CUSTOMERID that doesn't exist

Look at your exception handler:

WHEN NO_DATA_FOUND
THEN
NULL;

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

SOLUTION

slightwv (䄆 Netminder)

membership

Create a free account to see this answer

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

See answer