Raj,
the topic is quite broad, so I offer just a few hints:
1) From what you've posted I understand that you're actually trying to use an
anonymous PL/SQL block. This is an example of anonymous PL/SQL block I use in my project:
Note that the last bind value is OUT, all others are IN. Also note that the returning clause can be utilized directly by the Oracle driver, and may be a little more efficient that way, using Oracle specific
JDBC extensions.
2)
SELECT in Oracle produces a cursor. You cannot assign that directly to a
VARCHAR/
VARCHAR2. You need to somehow transform the cursor into a string. I might (just might) be able to help you if you state your goal.
3) In Oracle, there are two distinct mechanisms to bind a value by name. First one uses named binds like ":ABC" you're using, second one uses names of function/procedure parameters. I don't use any of them as the standard JDBC mechanism with "?" is sufficient for me. However, they are different and I suspect that the Spring framework (I also assume the
MapSqlParameterSource class is from the Spring framework) handles the second one, as it is generally more common. It is just my guess, however. I could assist you with unnamed binds, not with named ones anyway.
4) You state your goal is performance. I may be mistaken, but as far as I know, anonymous PL/SQL blocks are compiled every time they're executed. The
PreparedStatement optimization pertains to the SQL statements, not anonymous PL/SQL blocks. (I don't suggest to stop using
PreparedStatement! Using binds protects you from SQL injection attacks and you should do it virtually always). In any case, if you want to return just one value, you should try to do it with a single SQL. SQL always performs better than PL/SQL (due to context switches in PL/SQL). Oracle has amazing possibilities in SQL, including analytic functions, CONNECT BY and so on, and can process complicated multi-table queries efficiently. Many things that would require temporary tables in other databases can be done in a single SQL query in Oracle - if you have the right indexes and up-to-date statistics in place.
If SQL is not an option and there is a limited number of anonymous PL/SQL blocks you'd generate dynamically, I'd suggest to create a package with a function for every possible case. That would probably perform better than anonymous blocks. Such package can be generated from your code (you already aim to generate all those anonymous blocks anyway), you'd just deploy the package once into your database. It would be compiled once and not for every execution/every new session. Calling functions from JDBC is also even more straightforward (and common) than anonymous blocks.
5) You would really benefit from learning PL/SQL, as chris webster suggested, and the link he provided is a good starting point. You should also read Oracle JDBC driver documentation thoroughly, and maybe Oracle's SQL as well. Trying to use tools you don't know well may get very frustrating (my own experience).