aspose file tools*
The moose likes JDBC and the fly likes How to create a stored procedure in jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to create a stored procedure in jdbc" Watch "How to create a stored procedure in jdbc" New topic
Author

How to create a stored procedure in jdbc

Selvapandian Balasubramanian
Greenhorn

Joined: Oct 04, 2012
Posts: 3
Can anyone please let me know how to create a stored procedure in jdbc

eg: I m having a DDL for creating a oracle stored procedure as a string like string query = "create or repalce procedure ...";

When i m trying to execute this query using statement or prepared statement its creating a stored procedure but without a delimiter(';') at the end of procedure.

So running the procedure throwing error.

CallableStatement is used for calling a stored procedure.So i cant use callable statements .



Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

why are you trying to do this?
if it changes slightly on every run you should create parameters to encompass those changes...
Selvapandian Balasubramanian
Greenhorn

Joined: Oct 04, 2012
Posts: 3
I m creating a swing application.In that application i m taking .sql files and running that scripts .The .sql files can contain all types sql scripts like insert,update, store procedures and functions etc...

I m not using any client program for running this scripts.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

is this for fun or is your boss telling you to?

[edited: why did i think this was a mysql question?]
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

can you show us a small example of trying to create a stored procedure.

So all the program needs to do is have a string defined of the package you want to create, the connection, the jdbc commands to create the package, and the full stack trace of any exceptions that are thrown. (there is a link about this but couldn't find it)
Selvapandian Balasubramanian
Greenhorn

Joined: Oct 04, 2012
Posts: 3
I m reading the .sql file in my swing application and separating each different queries by a delimiter and executing one at a time

I given the full stored procedure create query as input to the jdbc statement and executing the statement.An executing the statement creating a store procedure in Oracle Db

with showing error symbol.After compiling the created procedure in sql client the error will gone and the procedure is executing..
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

What do you use as a delimiter in the SQL scripts?

You're in effect replicating SQL*plus or SQL Developer. The syntax of SQL scripts is perhaps more complicated. Generally, SQL statements are delimited by semicolons, but PL/SQL blocks are delimited by forward slashes; semicolons inside PL/SQL blocks (this includes definitions of packages and procedures) have to be passed on to the database. Moreover, SQL*plus allows you to override both delimiters, if I'm not mistaken.

My advice: write the exact text of all SQL statements as you're executing them in the database on a console or to the log file. When the exception occurs, you'll have the exact text of the statement you've executed right before. Post here that exact text of the command that is failing; without this, no one is probably able to help.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: How to create a stored procedure in jdbc