Skip to Main Content

Java APIs

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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Passing array of arrays as a parameter to Oracle

JackKJan 23 2023

Hi All.
I don't know Java language and I'm not sure if I am writing in the correct subforum.
We have a function that needs an array of arrays as an input parameter. The type of this parameter is t_a_varchar2s defined as:

create or replace type t_varchar2s is table of varchar2(255);
/
create or replace type t_a_varchar2s is table of t_varchar2s;
/

We have a problem with defining an array in Java that can be passed to Oracle into a parameter of this t_a_varchar2s type.
May you help us, please?

Best regards,
Jacek

Comments

Billy Verreynne

ChatGPT's answer - how accurate it is I don't know as I stay away from my least favourite language Java. Either way it should give you some ideas as to how to resolve your problem.
AI:
In Java, you can use the ArrayDescriptor class from the oracle.sql package to create an array that can be passed as a parameter to an Oracle stored procedure.
Here is an example of how you can create an Array object of type t_a_varchar2s to pass as a parameter to an Oracle stored procedure:

import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.sql.ArrayDescriptor;
// ...
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("T_A_VARCHAR2S", connection);
String[][] data = { {"value1", "value2", "value3"}, {"value4", "value5", "value6"} };
Array array = new ARRAY(descriptor, connection, data);
// pass the array as a parameter to the stored procedure
callableStatement.setArray(1, array);

In this example, connection is an open java.sql.Connection object to the Oracle database, and callableStatement is a java.sql.CallableStatement object representing the stored procedure that you are calling. Note that you need to have ojdbc.jar added to your classpath.

1 - 1

Post Details

Added on Jan 23 2023
1 comment
980 views