Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Help with Oracle packaged procedures/functions in JDBC

393808Jun 13 2003 — edited Jun 18 2003
Up to this point I've pretty much used vanilla SQL calls for Oracle (with the JDBC thin driver).

However, I just received a PL/SQL script (that I didn't write) which I need to execute in a Java application (and get a result set). So I started looking at the Callable object. However, I'm having problems:

I'm trying to execute the following, piece by piece:

exec pay_balance_pkg.set_context('TAX_UNIT_ID','143') ;
select /*+ ORDERED */
ppf.employee_number,
ppf.full_name,
pdb.balance_name,
ppa.effective_date,
pay_balance_pkg.get_value(pdb.defined_balance_id, paa.assignment_action_id) amount
from
.
lots more SQL
.
/

Using a Callable statement, I can run the pay_balance_pkg.set_context('TAX_UNIT_ID','143') as "{call pay_balance_pkg.set_context(?,?)}" and set up the input parameters. It runs.

But the pay_balance_pkg.get_value(pdb.defined_balance_id, paa.assignment_action_id) attempt generates errors because of the implicit parameters (pdb.defined_balance_id, paa.assignment_action_id). I was assuming the previous call set up the "context" for these parameters. But it doesn't seem to in JDBC.

What's the solution for this?

Is there a way to just "run" the .sql file via JDBC or JDeveloper? Or do I literally have to parse out the PL/SQL and SQL from the file?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 16 2003
Added on Jun 13 2003
2 comments
470 views