wood burning stoves*
The moose likes JDBC 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 OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "embedding PL/SQL procedure in StringBuffer" Watch "embedding PL/SQL procedure in StringBuffer" New topic
Author

embedding PL/SQL procedure in StringBuffer

Sudd Ghosh
Ranch Hand

Joined: Oct 23, 2002
Posts: 187
Hi,
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");
cstmt.executeUpdate();

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


SCJP 1.4, SCWCD, SCBCD 1.3
Paul Sturrock
Bartender

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
 
 
subject: embedding PL/SQL procedure in StringBuffer