pass array to oracle procedure

Question

I want to send two arrays form java to oracle stored procedures. The first Array is array of strings and the second is array of chars how can I make this??

Answer 1

The new approach is to unwrap the OracleConnection and then use createOracleArray.

OracleConnection oracleConnection = getJdbcTemplate().getDataSource().getConnection().unwrap(OracleConnection.class);
Array array = oracleConnection.createOracleArray("REF_TYPE_ARRAY", referralTypeId);

inputParameters.put("itemIds", array); 

Also see: How to create an oracle.sql.ARRAY object?

Answer 2

PeudoCode for the same how I achieved.

    # 1.You will require a structDescriptor object for an object equivalent in pl sql like :

    StructDescriptor structDes= new StructDescriptor("<schemaname in caps>.<sql_object_name>", connectionObject);

    # 2. You will need to pass one object values such name, class, id to an object array in order and accordance to 'sql_object_name' object. 

    For exmaple:
    STRUCT[] structArray=new STRUCT[<ListObj>.size()];
    int index=0;
    for (a in ListObj){

    Object[] object=new Object[]{a.getName(),a.getId()};
    STRUCT struct=new STRUCT(structDes ,connectionObject,object);
               structArray[index]=struct;
               index++;

    }

    ArrayDescriptor arrayDes=ArrayDescriptor.createDescriptor(
        "<Schema name>.<table object from sql>", connectionObject);

    ARRAY array=new ARRAY(arrayDes,connectionObject, structArray);

   then pass it to proc 

   .declareParameters(
   new SqlInOutParameter("<parameter to proc name>",OracleTypes.ARRAY,"
   <schema name>.<sql_array_or_table_obj>"))

   like 
   Hashmap<String, Object> map= new HashMap<>();
   map.put("<parameter to proc name>",array);
   psStatement.execute(map);

Hope it helps. This sequence may vary as per requirement and type of sql database used, but base is same.

I have copied this answer from one of my other answers.

Pass array as input parameter to an oracle stored procedure using simple jdbc call

Answer 3

Since ArrayDescriptor is deprecated since 12c, it should not be used anymore. Here's a code sniplet that worked for me in 12c:

            Array array = ((OracleConnection) connection).createOracleArray("T_VARCHAR2_ARRAY", data);
            CallableStatement statement = connection.prepareCall("{call p_array_test(?)}");
            statement.setArray(1, array);
            statement.execute();

Answer 4

Regex solve

select * from table_a  a where a.col in (select   regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;)

Answer 5

Look here: http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/oraarr.htm#i1058512

and here is my short example:

1) on database

SQL> create or replace type string_array as table of varchar2(100);
  2  /

Type created.

SQL> create or replace function to_string(p_array in string_array) return varchar2
  2  as
  3     l_string varchar2(32767);
  4     i binary_integer;
  5  begin
  6     i := p_array.first();
  7     while i is not null loop
  8        l_string := l_string || p_array(i) || ';';
  9        i := p_array.next(i);
 10     end loop;
 11     l_string := rtrim(l_string, ';');
 12     return l_string;
 13  end;
 14  /

Function created.

2) in java

public class ArrayTest {
    public static void main(String[] args) throws SQLException {
        DriverManager.registerDriver(new OracleDriver());
        OracleConnection connection = (OracleConnection) DriverManager.getConnection(...);

        String[] elements = {"abc", "def", "geh"};
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("STRING_ARRAY", connection);
        ARRAY array = new ARRAY(descriptor, connection, elements);

        OracleCallableStatement stmt = (OracleCallableStatement) connection.prepareCall("{? = call to_string(?)}");
        stmt.registerOutParameter(1, Types.VARCHAR);
        stmt.setARRAY(2, array);
        stmt.execute();

        String result = stmt.getString(1);
        System.out.println("to_string returned: " + result);
    }
}

seems to work: output says

to_string returned: abc;def;geh

Answer 6

Here's an example of how to do it.

The following script sets up a table, a type and a stored procedure in the database. The procedure takes a parameter of the array type and inserts each row of the array into the table:

CREATE TABLE strings (s VARCHAR(4000));

CREATE TYPE t_varchar2_array AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PROCEDURE p_array_test(
    p_strings      t_varchar2_array
)
AS
BEGIN
  FOR i IN 1..p_strings.COUNT
  LOOP
    INSERT INTO strings (s) VALUES (p_strings(i));
  END LOOP;
END;
/

The Java code then demonstrates passing an array into this stored procedure:

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class ArrayTest {
    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");

        CallableStatement stmt = conn.prepareCall("BEGIN p_array_test(?); END;");

        // The first parameter here should be the name of the array type.
        // It's been capitalised here since I created it without using
        // double quotes.
        ArrayDescriptor arrDesc =
            ArrayDescriptor.createDescriptor("T_VARCHAR2_ARRAY", conn);

        String[] data = { "one", "two", "three" };
        Array array = new ARRAY(arrDesc, conn, data);
        stmt.setArray(1, array);
        stmt.execute();

        conn.commit();
        conn.close();
    }
}

If you run the SQL script and then the Java class, and then query the table strings, you should find that all of the data has been inserted into the table.

When you say 'an array of chars', I'm guessing that you mean an array of Java chars. If I've guessed right, then I think you'd be best off converting the chars to Strings and then using the same approach as above.

Answer 7

You can use Oracle Types to map Java objects to Oracle. Also, there's Spring JDBC utilities.