File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Stored Procedure syntax errors Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Stored Procedure syntax errors" Watch "Stored Procedure syntax errors" New topic
Author

Stored Procedure syntax errors

John Paterson
Ranch Hand

Joined: Mar 12, 2012
Posts: 121
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:




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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

First of all, you need to terminate each PL/SQL block by forward slash (a single procedure definition is a PL/SQL block too):

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
Ranch Hand

Joined: Mar 12, 2012
Posts: 121
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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:*
The above gives me the following output:
It clearly shows that the error is the missing Invoices table in my environment.

Now after creating a table: and re-running the initial script I get:We can verify the update did happen:

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
Ranch Hand

Joined: Mar 12, 2012
Posts: 121
Hi,

The following works for me:



Thanks Martin Vajsar.

regards
John
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Procedure syntax errors