-2

I am trying to create a Stored Procedure in Oracle, when i run command SQL> start g:\oracle\totalsales.sql it displayed errors as given below

ERROR at line 6:
ORA-06550: line 6, column 47:
PL/SQL: ORA-00904: "CID": invalid identifier
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 20, column 3:
PLS-00201: identifier 'TOT' must be declared
ORA-06550: line 20, column 3:
PL/SQL: Statement ignored

Here is my stored procedure code. Please take a look and suggest me points i'm missing.

CREATE OF REPLACE PROCEDURE TotalSales (CID IN NUMBER, TOT OUT NUMBER)
IS
    DECLARE
        P Sales.Price%TYPE;
        T NUMBER(10,2) := 0;

        CURSOR CUR_SALES IS
            SELECT Price FROM Sales WHERE EmployeeID = CID;

    BEGIN
        OPEN CUR_SALES;
        LOOP
            FETCH CUR_SALES INTO P;
            IF CUR_SALES%ROWCOUNT = 0 THEN
                RAISE_APPLICATION_ERROR(-20020, 'No data found.');
            END IF;
            EXIT WHEN CUR_SALES%NOTFOUND;
            T := T + P;
        END LOOP;
        CLOSE CUR_SALES;

        TOT := T;

    EXCEPTION
        WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

END TotalSales;
/
Abhimanyu
  • 2,173
  • 2
  • 28
  • 44

1 Answers1

0

Change 

CREATE OF REPLACE

To

CREATE OR REPLACE

:)

And remove the DECLARE. The IS implicitly marks the beginning of a DECLARE block. If you want to keep it you need to close it with an extra END - and also it would need to follow the begin.

Jens Krogsboell
  • 1,093
  • 11
  • 18