Passing array of arrays as a parameter to Oracle

JackK

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

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.