Skip to Main Content
  • Questions

  • Calling PL/SQL packaged stored procedure from Java with TABLE OF RECORD

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mark.

Asked: May 08, 2017 - 2:37 pm UTC

Last updated: June 01, 2017 - 12:32 am UTC

Version: Oracle 12.2

Viewed 1000+ times

You Asked

Dear Oracle Masters,

I am trying to call an Oracle packaged stored procedure that has a formal input parameter of TABLE OF RECORD type. There are plenty of examples of calling a SP with either a RECORD or TABLE type but not both. I also want to take full advantage of the new 12.1 feature by referencing the TYPE in the package specification.

My package wf_state has the following TYPE definitions:

TYPE wf_state_rt IS RECORD (state_name IN wf_state.name%TYPE, description IN wf_state.description%TYPE);
TYPE wf_state_tt IS TABLE OF wf_state_rt INDEX BY BINARY_INTEGER;

The stored procedure within the same package is called add_workflow_state and has the signature:

PROCEDURE add_workflow_state(states IN wf_state_tt, workflow_name IN VARCHAR2);

Within a PL/SQL anonymous block, it is trivial to create the necessary structures to call this SP with success. Calling it from Java using the Oracle 12c JDBC driver is not. I am learning Java and would greatly appreciate it if you are able to post a Java snippet on how to achieve this given the structures defined above.

Many thanks in advance,
Mark.

and Connor said...

I dont think we can do that. Your options are

- simple PL/SQL arrays (ie, you would have an array for *each* attribute)

Details on that here

http://docs.oracle.com/database/122/JJDBC/Oracle-extensions.htm#JJDBC28180

Or, you would database types, so in your case, it would be something like:

create or replace TYPE wf_state_rt as object
(state_name varchar2(10), description varchar2(10));

create or replace TYPE wf_state_tt as TABLE OF wf_state_rt;

which is covered here

http://docs.oracle.com/database/122/JJDBC/Oracle-object-types.htm#JJDBC28431

If you cannot change the package, one option is to have a small wrapper, ie,

- java calls proc with the object type array
- the proc populate the table of records from the table of objects
- and then calls your package

======================

Addenda:

Things look better in 12c and above. Check the JDBC docs on improvements that have come along.

http://docs.oracle.com/database/121/JJDBC/apxref.htm#JJDBC29032

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library