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.
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 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