File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes embedding PL/SQL procedure in StringBuffer Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "embedding PL/SQL procedure in StringBuffer" Watch "embedding PL/SQL procedure in StringBuffer" New topic

embedding PL/SQL procedure in StringBuffer

Sudd Ghosh
Ranch Hand

Joined: Oct 23, 2002
Posts: 187
I recently had to code a program which had to embed/inline a complete Oracle PL/SQL procedure in a StringBuffer. It worked fine with the CallableStatement's executeUpdate method. However, after doing so I tried to google on this particular usage, but could not get any. On the internet, in numerous places it talks about calling a Stored Procedure which is stored in the database, but not inlining the way I did. I am attaching a portion of my code here. I am still not sure how it worked, but it sure did. Any idea?

String sqlString = new StringBuffer().append(
"CREATE OR REPLACE PROCEDURE someProcedure (transId IN NUMBER, newBill IN NUMBER) IS BEGIN DECLARE").append(" vNotifID VARCHAR2(38);").append(" CURSOR someCUR IS SELECT ...

It continues and is fairly long with one cascaded select and 3 table inserts. It takes 2 arguments. The sql call is as below.

java.sql.CallableStatement cstmt = connection.prepareCall("{call someProcedure(?, ?)}");
cstmt.setString(1, (String)in.get(BatchStatusPersistence.DATABASE_COLUMN_TRANSXNID));
cstmt.setString(2, "102");

My question is: How did it figure out the reference to the token name "someProcedure"? Is it because the StringBuffer is in memory and somehow the token was accessible? I just could not find any such usage mentioned on the internet.

Thanks, Sudd

Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

someProcedure must exist in the database. No other way it would work.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Sudd Ghosh
Ranch Hand

Joined: Oct 23, 2002
Posts: 187
Thanks a lot Paul. Actually, I had the same Procedure name in our existing system, already existing in the database. That's how it worked. The moment I changed the Procedure name, it threw exceptions. So effectively, I did not need any of that PL/SQL code in the StringBuffer. What a bummer I was.
Thanks again, Sudd
I agree. Here's the link:
subject: embedding PL/SQL procedure in StringBuffer
It's not a secret anymore!