Oracle Package or function ... is in an invalid state

Question

I stuck with Oracle store procedure calling. The code looks simple, but I seriously don't know how to make it work. This is my code for creating the procedure

DELIMITER @@
CREATE OR REPLACE PROCEDURE updateAward(_total_amount in Number, _no_of_sales in Number, _agent in NUMBER, _id in NUMBER) AS
    BEGIN
      update Award set total_amount = _total_amount, no_of_sales = _no_of_sales, agent_id = _agent where ID = _id @@
      commit @@

So, when I execute it through NetBean (it is the only tool I have at this moment), the code run well. I also tried to run the compile statement

alter PROCEDURE updateAward compile;

and then, use

select *
  from user_errors
 where name = 'ORG_SPGETTYPE'

The select return empty, proving that the compile process is ok. However, when I trigger the procedure

call updateAward(1,1,1,1);

It returns the error

Package or function UPDATEAWARD is in an invalid state

and the command

SELECT object_name FROM user_objects WHERE status='INVALID';

return the name of the procedure. How can I solve this problem ?

Update 1:

if I use

BEGIN
updateAward(1,1,1,1);
End;

I got error

    Error code 6550, SQL state 65000: ORA-06550: line 2, column 20:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( % ;

Update 2:

The reason I put the deliminator is because i got error with ";" when working through some vpn to the other network (still not sure why). So, i updated the code like your answer, but then, with the End; in the end of the procedure and then, get the Invalid SQL statement1. If i remove it and execute (through Netbean), the procedure is created successfully. However, after compiling and check the user_errors, it got the

"PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     ; "

Answer 1

First things first, your procedure syntax looks wrong. Don't use DELIMITER as that syntax is specific to MySQL. Instead, try something like the following.

CREATE OR REPLACE PROCEDURE updateAward(_total_amount in Number, _no_of_sales in Number, _agent in NUMBER, _id in NUMBER) AS
    BEGIN
      update Award set total_amount = _total_amount, no_of_sales = _no_of_sales, agent_id = _agent where ID = _id;
      commit;
    END;

Firstly, there are a couple of things wrong with your procedure:

1. You're not using delimiters correctly. Delimiters should be used to terminate the whole procedure, not each line.

   The NetBeans SQL window doesn't know SQL very well so it cannot tell when the procedure ends and something else begins. Normally, it uses semicolons (;) to tell when one statement ends and another begins, but stored procedures can contain semicolons within them so that wouldn't work. Instead, we change the delimiter to something else so that the NetBeans SQL window sends the entire stored procedure to the database in one go.

2. Variable names are not allowed to begin with an underscore (_). In particular, rule 5 in the list of Schema Object Naming Rules at this Oracle documentation page states that

   Nonquoted identifiers must begin with an alphabetic character from your database character set.

   Underscores are not alphabetic characters.

I've taken your procedure, fixed the use of delimiters and added an extra p onto the front of each parameter name (p for 'parameter'), and I got the following, which ran successfully in NetBeans and created a procedure without errors:

delimiter $$
CREATE OR REPLACE PROCEDURE updateAward(p_total_amount in Number, p_no_of_sales in Number, p_agent in NUMBER, p_id in NUMBER) AS
    BEGIN
      update Award set total_amount = p_total_amount, no_of_sales = p_no_of_sales, agent_id = p_agent where ID = p_id;
      commit;
    END;
$$
delimiter ;

Secondly, you write

[...] and then, use

select *
  from user_errors
  where name = 'ORG_SPGETTYPE'

The select return empty, proving that the compile process is ok.

Um, no. This proves that there are no errors in the procedure ORG_SPGETTYPE (or no procedure with that name exists). Your procedure is named updateAward, which Oracle will capitalise to UPDATEAWARD. Try

select *
  from user_errors
 where name = 'UPDATEAWARD';

instead.