CODEWITHSUNDEEP
oracle-databasestored-procedurestoad
Von Justine Napalang
Von Justine Napalang

Reputation: 67

Creating stored procedure in toad failed to execute

I'm creating Update Stored Procedure in Toad. When I'm executing the query it returns me an error like this.

1/51    PLS-00103: Encountered the symbol "(" when expecting one of the following:
          := . ) , @ % default character

Heres my Code:

I've tried to change the AS to IN but still same error

Create Or Replace Procedure SP_EMPLOYEE_UPDATE(P_EMPLOYEEID VARCHAR2(20 CHAR),
P_EMPLOYEENAME VARCHAR2(100 CHAR),
P_STATUS VARCHAR2(50 CHAR),
P_SUPERIORID VARCHAR2(20 CHAR),
P_MANAGERSUPERIORID VARCHAR2(20 CHAR),
P_BFG VARCHAR2(10 CHAR),
P_DATEHIRED Date,
P_DESIGNATION VARCHAR2(100 CHAR),
P_JOBGRADE NUmber,
P_EMAILADDRESS VARCHAR2(100 CHAR),
P_PRODUCTLINE VARCHAR2(80 BYTE),
P_STATION VARCHAR2(80 BYTE),
P_MACHINE  VARCHAR2(80 BYTE),
P_OPERATIONGROUP VARCHAR2(80 BYTE),
P_ISACTIVE VARCHAR2(1 BYTE)) 

AS

Begin 
Update REFEMPLOYEES set REFEMPLOYEES.EMPLOYEENAME = P_EMPLOYEENAME , REFEMPLOYEES.STATUS= P_STATUS,REFEMPLOYEES.SUPERIORID = P_SUPERIORID,REFEMPLOYEES.MANAGERSUPERIORID= P_MANAGERSUPERIORID,REFEMPLOYEES.BFG = P_BFG,
REFEMPLOYEES.DATEHIRED = P_DATEHIRED,REFEMPLOYEES.DESIGNATION = P_DESIGNATION,REFEMPLOYEES.JOBGRADE = P_JOBGRADE,REFEMPLOYEES.EMAILADDRESS = P_EMAILADDRESS,REFEMPLOYEES.PRODUCTLINE = P_PRODUCTLINE,REFEMPLOYEES.STATION = P_STATION,
REFEMPLOYEES.MACHINE = P_MACHINE,REFEMPLOYEES.OPERATIONGROUP = P_OPERATIONGROUP,REFEMPLOYEES.ISACTIVE = P_ISACTIVE
 Where REFEMPLOYEES.EMPLOYEEID = P_EMPLOYEEID;



End

Upvotes: 0

Views: 185

Answers (1)

Popeye
Popeye

Reputation: 35900

Your code should look like following ideally:

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_UPDATE (
    P_EMPLOYEEID          VARCHAR2,
    P_EMPLOYEENAME        VARCHAR2,
    P_STATUS              VARCHAR2,
    P_SUPERIORID          VARCHAR2,
    P_MANAGERSUPERIORID   VARCHAR2,
    P_BFG                 VARCHAR2,
    P_DATEHIRED           DATE,
    P_DESIGNATION         VARCHAR2,
    P_JOBGRADE            NUMBER,
    P_EMAILADDRESS        VARCHAR2,
    P_PRODUCTLINE         VARCHAR2,
    P_STATION             VARCHAR2,
    P_MACHINE             VARCHAR2,
    P_OPERATIONGROUP      VARCHAR2,
    P_ISACTIVE            VARCHAR2
) AS
BEGIN
    UPDATE REFEMPLOYEES
    SET
        REFEMPLOYEES.EMPLOYEENAME = P_EMPLOYEENAME,
        REFEMPLOYEES.STATUS = P_STATUS,
        REFEMPLOYEES.SUPERIORID = P_SUPERIORID,
        REFEMPLOYEES.MANAGERSUPERIORID = P_MANAGERSUPERIORID,
        REFEMPLOYEES.BFG = P_BFG,
        REFEMPLOYEES.DATEHIRED = P_DATEHIRED,
        REFEMPLOYEES.DESIGNATION = P_DESIGNATION,
        REFEMPLOYEES.JOBGRADE = P_JOBGRADE,
        REFEMPLOYEES.EMAILADDRESS = P_EMAILADDRESS,
        REFEMPLOYEES.PRODUCTLINE = P_PRODUCTLINE,
        REFEMPLOYEES.STATION = P_STATION,
        REFEMPLOYEES.MACHINE = P_MACHINE,
        REFEMPLOYEES.OPERATIONGROUP = P_OPERATIONGROUP,
        REFEMPLOYEES.ISACTIVE = P_ISACTIVE
    WHERE
        REFEMPLOYEES.EMPLOYEEID = P_EMPLOYEEID;

END SP_EMPLOYEE_UPDATE;
/

Cheers!!

Upvotes: 1

Related Questions