• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

embedding PL/SQL procedure in StringBuffer

 
Sudd Ghosh
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
someProcedure must exist in the database. No other way it would work.
 
Sudd Ghosh
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic