Stored Procedure syntax errors

[John Paterson]

Hi Folks,

I posted this in another forum, no reply yet so I am posting it here. I am learning Oracle PL/SQL and was trying out some Stored Procedure queries. I was trying out a book example where a stored procecure is being called. The code in question is as follow:

CREATE OR REPLACE PROCEDURE  update_invoices_credit_total
(
	invoice_number_param  VARCHAR2,
	credit_total_param    NUMBER   
)

AS
BEGIN
	UPDATE invoices
	SET credit_total  = credit_total_param
	WHERE invoice_number = invoice_number_param;


COMMIT;
EXCEPTION
	WHEN OTHERS THEN
	ROLLBACK;
END;

CALL update_invoices_credit_total('367447', 300);

I am getting a 'Syntax error, expected:' message when I hover the mouse over the word update in the call statement. I am not sure why I am getting this, hope someone can advise. Thanks.
I am running Oracle 11g express edition on my Windows 7 machine. Oracle SQL developer 3.1.07 is the 'editor' through which I run the above commands.

[Martin Vashko]

First of all, you need to terminate each PL/SQL block by forward slash (a single procedure definition is a PL/SQL block too):
CREATE OR REPLACE PROCEDURE update_invoices_credit_total ( invoice_number_param VARCHAR2, credit_total_param NUMBER ) AS BEGIN UPDATE invoices SET credit_total = credit_total_param WHERE invoice_number = invoice_number_param; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; / CALL update_invoices_credit_total('367447', 300);
Otherwise Oracle would try to process the CALL command, which actually tests the newly created procedure, as part of the procedure.

If you're getting an error on the UPDATE statement, then perhaps the table doesn't exist or you don't have sufficient privileges. I was able to create this procedure using a name of existing table without errors.

Two more points:

1) It is a bad idea to commit in a stored procedure. It should always be the caller's response to start and end an transaction. Otherwise it can be difficult to reuse the stored procedures to form larger transactions.

2) The WHEN OTHERS clause is horrible. It is the equivalent of an empty catch clause in Java. If an error occurs, the transaction is silently rolled back and the exception is swallowed. You should not have the rollback there anyway (see the previous point), so the entire EXCEPTION clause should go away. Let the caller handle the errors.

If this is a sample code from a book, then don't use that book. It really is not good and will teach you wrong things. Oracle actually has quite good documentation, including introductory ("2 day") guides, eg. Oracle Database 2 Day Developer's Guide.

[John Paterson]

Hi Martin Vajsar,

Thanks for the response, I appreciate it. When I had intitally not made the forward slash, I was getting both a red crooked line indicator under the word update, which when mouse hovered said:

Syntax error, expected
.
;
)

and also when I run the statement I got the following error:

PROCEDURE update_invoices_credit_total compiled
Warning: execution completed with warning

After I addied the forward slash like you had mentioned, the red crooked line error is still there, but when I run the statement now I am not getting any error, instead I get a:

PROCEDURE update_invoices_credit_total compiled

message. Thinking all is well, I went to the Invoices table to check and see if the 'credit total' column's row value has changed to 50 in the line where invoice number is 367447, I see no change. Hope you can advise. Thanks.

[Martin Vashko]

Could you run the script in SQL*plus? All output (errors, warnings) are shown on the console and can be easily copied and pasted here. It's much harder to convey the state of an IDE in the forum.

I'd suggest running the following script:*CREATE OR REPLACE PROCEDURE update_invoices_credit_total ( invoice_number_param VARCHAR2, credit_total_param NUMBER) AS BEGIN UPDATE invoices SET credit_total = credit_total_param WHERE invoice_number = invoice_number_param; END; / show errors; CALL update_invoices_credit_total('367447', 300); commit;
The above gives me the following output:SQL> CREATE OR REPLACE PROCEDURE update_invoices_credit_total ( 2 invoice_number_param VARCHAR2, 3 credit_total_param NUMBER) 4 AS 5 BEGIN 6 UPDATE invoices 7 SET credit_total = credit_total_param 8 WHERE invoice_number = invoice_number_param; 9 END; 10 / Warning: Procedure created with compilation errors. SQL> SQL> show errors; Errors for PROCEDURE UPDATE_INVOICES_CREDIT_TOTAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 6/5 PL/SQL: SQL Statement ignored 6/12 PL/SQL: ORA-00942: table or view does not exist SQL> SQL> CALL update_invoices_credit_total('367447', 300); CALL update_invoices_credit_total('367447', 300) * ERROR at line 1: ORA-06575: Package or function UPDATE_INVOICES_CREDIT_TOTAL is in an invalid state SQL> commit;
It clearly shows that the error is the missing Invoices table in my environment.

Now after creating a table: create table invoices(invoice_number varchar2(10), credit_total number); insert into invoices(invoice_number, credit_total) values('367447', 0); commit; and re-running the initial script I get:SQL> CREATE OR REPLACE PROCEDURE update_invoices_credit_total ( 2 invoice_number_param VARCHAR2, 3 credit_total_param NUMBER) 4 AS 5 BEGIN 6 UPDATE invoices 7 SET credit_total = credit_total_param 8 WHERE invoice_number = invoice_number_param; 9 END; 10 / Procedure created. SQL> SQL> show errors; No errors. SQL> SQL> CALL update_invoices_credit_total('367447', 300); Call completed. SQL> commit; Commit complete. We can verify the update did happen:
SQL> select * from invoices where invoice_number='367447'; INVOICE_NU CREDIT_TOTAL ---------- ------------ 367447 300
It all works fine and dandy.

Show me the output of the above scripts in your environment.

*Edit: I've removed the commit, rollback and exception clause from the procedure. It should not be there, see my first reply.

[John Paterson]

Hi,

The following works for me:

CREATE OR REPLACE PROCEDURE  update_invoices_credit_total (   
        invoice_number_param  VARCHAR2,   
        credit_total_param    NUMBER)   
    AS   
    BEGIN   
        UPDATE invoices   
        SET credit_total  = credit_total_param  
        WHERE invoice_number = invoice_number_param;   
        
        COMMIT;
        
        
    END;   
   /   

CALL update_invoices_credit_total('367447', 19); 

Thanks Martin Vajsar.

regards
John