Thread: Calling anonymous plsql blocks

We have one requirement in which we have one plsql stored procedure which does certain data maintenance task like ADD/Drop partition, stats collection etc in Oracle database. It takes table name,owner_name..etc as input. It does have cursor loops inside it to perform the respective task, say for. e.g. rebuilding all global indexes after partition drop for the table. And output returned as success/failure etc.

Now we are planning to implement these code/functionality for multiple databases from a standalone application which will use Java. So basically we don't want to create separate procedure/objects on each of the target database to do the same task, rather, we want to achieve the functionality by just giving call to a anonymous block which will perform all the task same as that of procedure.

We are trying to see the easiest possible way to achieve this, and I see below blog which suggest some way similar to Oracle "execute immediate" code i.e. putting all the code in a concatenated string and then executing it. Not sure if any limitation exists for the cursor query or collection type execution while calling the anonymous block in such a way. But it looks to be taking quite a lot of effort to break the procedure and make such concatenated string call from Java as below. Want to understand from experts , if there is any other easy way exists in which we can just call the whole .sql file/anonymous block from Java?

In one of the blog i saw below sample code to call the plsql block from Java but it seems will consume quite a ot of time to convert each of the plsql block to this kind of concatenated string.

String plsql = "" +
" declare " +
" p_id varchar2(20) := null; " +
" l_rc sys_refcursor;" +
" begin " +
" p_id := ?; " +
" ? := 'input parameter was = ' || p_id;" +
" open l_rc for " +
" select 1 id, 'hello' name from dual " +
" union " +
" select 2, 'peter' from dual; " +
" ? := l_rc;" +
" end;";

CallableStatement cs = c.prepareCall(plsql);


**************Existing Plsql procedure Sample:-

create or replace procedure p1(IP_ARG1, IP_ARG2, OP_ARG3,OP_ARG4)
Cursor mycur(IP_ARG1) is ....
select..
from tab1 where table_name=IP_ARG1;

begin

For myrec in mycur(IP_ARG1)
Loop
...
END Loop;
Exception
.............
END;


************ Converted Sample Plsql Block to be called from java****************

DECLARE

IP_ARG1 VARCHAR2(4000);
IP_ARG2 VARCHAR2(4000);
.......

Cursor mycur(IP_ARG1) is ....
select..
from tab1 where table_name=IP_ARG1;

begin

For myrec in mycur(IP_ARG1)
Loop
...
END Loop;

end;
/

This article gives you the information you need to begin working with strings in your PL/SQL programs.