1

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.

drowny
  • 2,067
  • 11
  • 19
Frank
  • 11
  • 1
  • 1
    Put `/` in next line after `END;` and before `/*4.*/` comment. Just: `END;` + new line + `/` + new line + comment `/*4.*/` – krokodilko Sep 26 '18 at 19:25
  • Try `set serveroutput on;` see this question for details: [DBMS_OUTPUT.PUT_LINE not printing](https://stackoverflow.com/questions/10434474/dbms-output-put-line-not-printing) – krokodilko Sep 26 '18 at 21:55
  • Can't believe I missed that, was going nuts last night looking for the syntax I screwed up. Appreciate it, code is running fine now. Stupid copy paste error, I was keeping a running backup in .txt, and when SQL Developer crashed on me, I pasted it back in missed those lines, I'm an idiot lol. Really appreciate the quick response. – Frank Sep 26 '18 at 21:55

0 Answers0