This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,294 Users
  • 2,269,776 Discussions
  • 7,916,824 Comments

Discussions

Passing array of arrays as a parameter to Oracle

JackK
JackK Member Posts: 712 Bronze Badge

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

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Cape TownMember Posts: 28,976 Red Diamond

    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.