This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Rajukpo kumar
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Rajukpo kumar
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic