Hello Experts!
I would like to ask for your assistance about Procedures and Functions in PL/SQL. I am just starting learning SQL. (Note: I am using Oracle SQL Developer 11g XE)
My objective is create a Procedure that will display the name of the customer with the highest amount of orders and this procedure will invoke a function that retrieves the name and total amount. I started doing the function below:
CREATE OR REPLACE FUNCTION GET_HIGHORDER_FUNC (CUSTOMER_NAME IN VARCHAR2, TOTAL_AMT_ORDERED IN NUMBER)
RETURN VARCHAR2, NUMBER
AS
BEGIN
SELECT * FROM (
SELECT C.CUSTOMERID, SUM( OL.ORDEREDQUANTITY) as TOTAL_QUANTITY_ORDERED
FROM CUSTOMER_T C
INNER JOIN ORDER_T O ON C.CUSTOMERID = O.CUSTOMERID
INNER JOIN ORDERLINE_T OL ON O.ORDERID = OL.ORDERID
GROUP BY C.CUSTOMERID
ORDER BY SUM( OL.ORDEREDQUANTITY) DESC) where rownum=1;
RETURN (CUSTOMER_NAME, TOTAL_AMT_ORDERED);
END;
/
but I am getting the below error message:
- Error(2,16): PLS-00103: Encountered the symbol "," when expecting one of the following: . @ % ; is default authid as cluster order using external character deterministic parallel_enable pipelined aggregate result_cache accessible rewrite
I cannot find a way to troubleshoot this error and I am not able to complete this task.
I hope someone can help.
Thank you in advance!