PL/SQL call to procedure

Question

I wrote a PL/SQL procedure to update the salary of the table Employee

create table Employee
(ID                 VARCHAR2(4 BYTE)         NOT NULL,
First_Name         VARCHAR2(10 BYTE),
   Last_Name          VARCHAR2(10 BYTE),
   Start_Date         DATE,
   End_Date           DATE,
    Salary             Number(8,2),
   City               VARCHAR2(10 BYTE),
    Description        VARCHAR2(15 BYTE)
)
 /

And this is the procedure

CREATE OR REPLACE PROCEDURE update_employee_salary(
   p_factor IN NUMBER
    ) AS
    v_employee_count INTEGER;
    BEGIN
        UPDATE employee
       SET salary = salary * p_factor;
       COMMIT;
    EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK;
   END update_employee_salary;
   /

When I try to call the procedure

CALL update_employee_salary (1.5)

oracle displays ORA-00900: invalid SQL statement

How are you calling the procedure? From SQL\*Plus? JDBC? ODP.NET? SQL Developer? Something else? — Luke Woodward, Mar 25 '16 at 11:09

mahi_0707 · Accepted Answer · 2016-03-26T18:45:03.637

5

It is incorrect way of calling procedure. You can use the below method :

    Begin
    update_employee_salary(1.5);
    End;

Read more here : PL/SQL Procedure

edited Mar 26 '16 at 18:45

answered Mar 25 '16 at 11:22

mahi_0707

1,030
9
17

score 1 · Answer 2 · answered Mar 25 '16 at 11:24

1

You can also execute it like this, in SQL*PLUS and SQL-Developer:

EXEC update_employee_salary(1.5)

answered Mar 25 '16 at 11:24

Erich Kitzmueller

36,381
5
80
102

score 0 · Answer 3 · answered Mar 26 '16 at 17:04

You can call a procedure with CALL as you did. The reason for not working could be related to a previous sql statement that hasn't been closed with ;. Moreover a piece of advice: you should not use oracle/plsql reserved words as table column name as you did (id is a reserved word).

PL/SQL call to procedure

3 Answers3