Search code examples
sqloracle-databasestored-proceduresjdbcprocedure

ORA -00909 //sql statement ignored ...procedure created with compilation errors


create or replace procedure Pupdate(   
peno in emp.empno%type,  
psal in emp.sal%type,  
pcomm in emp.comm%type)  
IS   
BEGIN  
UPDATE emp SET sal=sal+psal, comm=NVL(comm)+pcomm WHERE empno=peno;  
END Pupdate;  
/

The stored procedure takes the sal and comm from user and updates it into the emp table. enter image description here


Solution

  •  create or replace procedure Pupdate(   
    peno in emp.empno%type,  
    psal in emp.sal%type,  
    pcomm in emp.comm%type)  
    IS   
    BEGIN  
    UPDATE emp SET sal=sal+psal, comm=NVL(comm,0)+nvl(pcomm,0) WHERE empno=peno;  
    END Pupdate;  
    /
    

    Just try this. NVL will be like NVL(column_name,value_if_null).