I have some PL/SQL
blocks for an assignment, and I'm having some trouble with them, the both run separately okay, but when I run the whole of the code at once (which I need for the assignment), I am getting an error. Below is the code and error I'm getting, any help on what I'm missing would be really appreciated. Thank you
DECLARE
Salesbyzip NUMBER(10,2):=0;
inputZip NUMBER(5):=00000;
FUNCTION Zip_Code_Sales(zipcode IN NUMBER)
RETURN NUMBER IS
total_Sales NUMBER(10,2):=0;
BEGIN
SELECT SUM(GROSS_SALE_PRICE)
INTO total_Sales
FROM SALES_OVS
INNER JOIN CUSTOMERS_OVS ON SALES_OVS.CUST_ID =
CUSTOMERS_OVS.CUSTOMER_ID
WHERE zipcode = CUSTOMERS_OVS.Address_zip;
RETURN total_Sales;
END;
BEGIN
inputZip:=21009;
Salesbyzip:=Zip_Code_Sales(inputZip);
dbms_output.put_line('The total sales for zip code ' || inputZip ||
' is $'|| Salesbyzip);
END;
/*4.*/
DECLARE
zipMostSales NUMBER(5):=00000;
FUNCTION Zip_Code_Max_Sales
RETURN NUMBER IS
maxZip NUMBER(5):=00000;
BEGIN
WITH sales_customers AS
(SELECT ADDRESS_ZIP, COUNT(*) AS Salesbyzip
FROM SALES_OVS
INNER JOIN CUSTOMERS_OVS ON SALES_OVS.CUST_ID =
CUSTOMERS_OVS.CUSTOMER_ID
GROUP BY ADDRESS_ZIP
ORDER BY Salesbyzip DESC, ADDRESS_ZIP)
SELECT ADDRESS_ZIP
INTO maxZip
FROM sales_customers
WHERE ROWNUM <= 1;
RETURN maxZip;
END;
BEGIN
zipMostSales:=Zip_Code_Max_Sales;
dbms_output.put_line('The Zipcode with the most sales is ' ||
zipMostSales);
END;
Error report - ORA-06550: line 27, column 2: PLS-00103: Encountered the symbol "DECLARE" ORA-06550: line 35, column 7: PLS-00103: Encountered the symbol "SALES_CUSTOMERS" when expecting one of the following:
06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:
I am looking to get all of the blocks to run through, without error, and display the requested information.