• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Paul Clapham
  • Jeanne Boyarsky
  • Knute Snortum
Sheriffs:
  • Liutauras Vilda
  • Tim Cooke
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Joe Ess
  • salvin francis
  • fred rosenberger

Writing Stored Procedure Oracle 12c

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm creating a stored procedure to use in a CallableStatement and I'm kind of lost as to how to write it.  This is what I have thus far:



It's refusing to compile.  I've tried a couple different things but I think it's coming down to not understanding the rules behind the syntax for stored procedures.  

I need the procedure to update the status id in the ers_reimbursement table.  The procedure takes a value from the Java code that represents the new status id.
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I just realized I need a where clause in the statement:

 
Marshal
Posts: 6639
177
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for posting your solution back here!
 
Saloon Keeper
Posts: 21593
146
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A word about stored procedures, though.

I don't recommend using stored procedures as a general rule. There's no standard for them. They vary considerably between different brands of databases. And if you don't think that's a problem consider that Amazon used to use Oracle extensively until last year, when they completely switched over to a different DBMS platform. Straight SQL is easier to transport, especially if you're using an ORM to front for it.

Maintaining source code control for stored procedures is also a problem. Often there is little or no versioning available for the code stored in the DBMS and tools to work with code versions can be hard to come by.

Use of stored procedures in application programs can also make the applications harder/more expensive to maintain, as you may end up going on "treasure hunts" to find out what logic is in the app and what logic is in the database (stored procedure).

So when should you use stored procedures?

Well in some cases, you cannot do what you need efficiently using plain SQL. In cases like that, code that runs entirely on the server may make it worth the disadvantages I outlined above. I don't think that this applies to your example, but I have seen some cases where the differences are major.

Another instance is when you have a critical business process that operates according to a complex set of rules and you have many applications using those rules. In cases like that, it's certainly more effecient to keep one set of rules (the stored procedure) rather than having to maintain multiple copies of them.

Still another case would be where you have users who use some sort of command-line utility to issue database commands directly such as sql*plus and you want to make those commands simple and easy to use.

So there are some good reasons for using stored procedures. But try to keep them to a minumum. Because, as I said there are some very good reasons not to.
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That wasn't a correct answer, just a correction.  I'm sorry for misleading you.

I'm still receiving compiler errors:

3/1       : Encountered the symbol "DECLARE" when expecting one of the following:     begin function pragma procedure subtype type <an identifier>    <a double-quoted delimited-identifier> current cursor delete    exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.

11/0     : Encountered the symbol "end-of-file" when expecting one of the following:     ( begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << continue close current delete fetch lock    insert open rollback savepoint set sql execute commit forall    merge pipe purge

Errors: check compiler log
 
Rancher
Posts: 4450
47
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


You don't use DECLARE there, which will probably fix the later issue.
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm still having issues with the Callable Statement.  Now it compiles but it returns false when it executes from Java.  Here's what the stored procedure looks like:



and here's what my java code looks like, (the PreparedStatement is commented out b/c this needs to be a CallableStatement according to my trainer):

 
Marshal
Posts: 24943
61
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Andrew Spiteri wrote:I'm still having issues with the Callable Statement.  Now it compiles but it returns false when it executes from Java.



It seems like you think that's a problem. Could you tell us why?
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not updating the reimb_status_id like I need it to.  

I say:



The reimb_status_id doesn't update and the execute statement returns false.  I'm not sure how to debug this.  I think I need to run the procedure in sql developer to see what's wrong but I'm not good with stored procedures.  I don't know what to run to execute it.
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm trying to invoke the procedure in sql developer using this statement:



It's giving me this response, though:

ORA-00904: "ERS_APP"."UPDATE_REIMBS": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 1 Column: 8

I'm going to try testing it another way but if anyone has suggestions I'm open to them.
 
Tim Holloway
Saloon Keeper
Posts: 21593
146
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suppose I could do something horribly tedious like read the manual, but, assuming I'm not remembering the wrong DBMS system, I think the actual SQL*PLUS command may be:
 
Andrew Spiteri
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is the answer I eventually ended up getting to work.  I turned it into an UPDATE statement instead of SELECT INTO.



 
Knute Snortum
Marshal
Posts: 6639
177
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I may have missed something, but why use a procedure to do this?  Why not just execute the update in SQL?
 
Paul Clapham
Marshal
Posts: 24943
61
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Andrew Spiteri wrote:The reimb_status_id doesn't update and the execute statement returns false.



The first part may be a problem, but I suspect you haven't checked the API documentation to see what a return value of false means.

(Edit) The first part would mean that the where-clause returns zero rows. You could test that by removing it and seeing whether all of the rows in the ers_reimbursement table get updated.
 
Hoo hoo hoo! Looks like we got a live one! Here, wave this tiny ad at it:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!