wood burning stoves*
The moose likes JDBC and the fly likes Execute pl/sql function which is having bind variables in java Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Execute pl/sql function which is having bind variables in java" Watch "Execute pl/sql function which is having bind variables in java" New topic
Author

Execute pl/sql function which is having bind variables in java

Rajukpo kumar
Greenhorn

Joined: Dec 31, 2010
Posts: 9
Hello All,

I would like to execute a pl/sql function which contains bind variables. The pl/sql Function will return one varchar value.

I have the list of bind variables and associated values. So that i can create a MapSqlParameterSource object in java and pass it as parameter while calling a executing a function.

Currently i am using SimpleJdbcCall in spring to execute the pl/sql function.



Pl sql function sample is here.



I am getting exception like:
UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call SQL_FUNCTION1()}]; SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 1

Any help!!


Thanks,
Raj



chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1771
    
  14

Hi Raj,

I'm an Oracle developer, not a Java developer, but it looks like your function has no parameters. You need to declare the parameters for any PL/SQL function/procedure e.g.

CREATE FUNCTION my_func (p_something IN VARCHAR2)
RETURN NUMBER IS
--
-- Just returns the length of the input string
--
BEGIN
RETURN LENGTH(p_something);
END my_func;

Inside the function, you refer to the parameters by name e.g. P_SOMETHING.

You'll need to investigate how to write the Java interfaces to PL/SQL stored code yourself.

You should probably also learn PL/SQL properly if you're going to use it.

It's a very powerful language for doing data-oriented processing far more efficiently in the database than using Java, but you need to know how it works in order to make good use of it. Same as any other language!

Hope this helps.

Chris

No more Blub for me, thank you, Vicar.
Rajukpo kumar
Greenhorn

Joined: Dec 31, 2010
Posts: 9
Hi chris,

Thanks for your reply,

I am sure that pl/sql function having the paramters not mandatory. But what if that pl/sql function is having a kind of bind variables(eg, :ABC in above example).

That too the pl/sql functions will not be readily available in the database. In the java code, pl/sql functions will be generated instantly for which function body will get as a text from the database which may contain bind variables, then generated pl sql functions will be called by passing SqlParameterSource object(java one) as a parameter while executing the pl/sql function.

Probably the SqlParameterSource object will replace bind variables with corresponding values at the time execution. This should give the effective performance.

While doing that i am getting exception as i mentioned earlier.

Help needed!!

Anyway thanks again for your reply.

Regards,
Raj
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1771
    
  14

You need to bind your Java values to the PL/SQL function parameters when you call it. That is your defined public interface into the PL/SQL function.

You also need to Google around a bit about calling stored procedures and how PL/SQL works.

Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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).
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1771
    
  14

Martin Vajsar wrote: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.


Great advice there from Martin. Just one point:

If you define your PL/SQL methods as stored functions/procedures or packages in the database, and use normal input parameters etc, then the code is compiled at creation time, not at runtime, and is thus available for various kinds of optimisation by the Oracle RDBMS engine. As well as improving performance and re-use, this also adds extra validation, parsing and so on, because your code is checked against the database tables etc at compilation time, instead of at runtime.

You can also define SQL cursors and return these from PL/SQL packaged code, as a way of encapsulating and pre-compiling complex parameterised SQL statements in a managed way within the RDBMS. Again this can be useful for performance and re-use etc.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Execute pl/sql function which is having bind variables in java