We have one requirement in which we have existing stored procedure which does certain functions like Compress partition, stats collection, drop partition etc. 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.
I was trying to see the easiest possible way to achieve this, and i saw below blog which suggest some way similar to Oracle execute immediate call i.e. putting all the code in 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. And also code doesn't look clean enough.
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 which can then be run irrespective of specific database?
https://stackoverflow.com/questions/5101529/execute-anonymous-pl-sql-block-and-get-resultset-in-java
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);