java - passing array in oracle stored procedure

Question

I have a Java app accessing an oracle stored procedure. The arguments to the stored procedure include an array type. I do it like the following...

con = this._getConnection();  
Connection narrowdConn = (Connection)WSJdbcUtil.getNativeConnection( (WSJdbcConnection)con );  

callable = con.prepareCall("{call MY_PKG.MY_PROCEDURE(?, ?)}");  


ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("VARCHAR2_ARR", narrowdConn);  
ARRAY arrayArg1 = new ARRAY(arrayDescriptor, con, docNames);  
ARRAY arrayArg2 = new ARRAY(arrayDescriptor, con, docTypes);  

callable.setArray(1, arrayArg1);  
callable.setArray(2, arrayArg2);  

callable.execute();

Now, I am getting this Exception...

java.sql.SQLException: invalid name pattern: MY_PKG.VARCHAR2_ARR

VARCHAR2_ARR is a public TYPE, defined inside an Oracle Package like the following:

TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(50);

And used as such in my stored proc...

PROCEDURE MY_PROCEDURE  
    (V_ARR_ARG1  IN VARCHAR2_ARR,  
     V_ARR_ARG2  IN VARCHAR2_ARR)

Vincent Malgrat · Accepted Answer · 2009-09-09 14:04:15Z

1

the type VARCHAR2_ARR is a PLSQL type, you won't be able to interface it directly from java. I suggest you look into this thread on AskTom regarding a similar question.

Here are a couple suggestions:

create a SQL TYPE that you can bind from java
insert into a temporary table from java and read from it in plsql

In both cases you will have to either modify the PLSQL procedure or add a new translation procedure.

answered Sep 9, 2009 at 14:04

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

coder Over a year ago

actually the problem is that any type created within a package is not visible by java. If I create the type at schema level then it works.

Vincent Malgrat Over a year ago

@Coder: PLSQL types can't be accessed directly from java. You will have to use a true SQL type (CREATE type...) and either a wrapper procedure with this SQL type or modify your procedure to accept the new type.

Andy Dufresne Over a year ago

If I understand it correctly, if a type is declared inside a package it's called PLSQL type right? I am confused on how does jdbc succeed to make a call to a procedure declared and defined inside a package if calling a PLSQL type fails. What's the difference between the two?

zytham Over a year ago

It is similar to how you are able to call public method of a class in java , however no direct access to private member. Just an analogy , I know it is not the reason.

score 0 · Accepted Answer · 2012-08-24 06:05:52Z

0

We need to set accessToUnderlyingConnectionAllowed falg true while creating a datasource

edited Aug 24, 2012 at 6:05

answered Aug 23, 2012 at 14:03

user1620001

Comments

Aztec · Accepted Answer · 2022-06-09 14:58:06Z

I did stuck at this problem. Hope this illustration might help you:

Steps to create the procedure in oracle.

Create type of the desired object as:

CREATE OR REPLACE TYPE STUDENT_TYPE IS OBJECT
( ID NUMBER,
  NAME VARCHAR2(50));

Create another object of array as:

CREATE OR REPLACE TYPE STUDENT_ARRAY IS TABLE OF STUDENT_TYPE;

Create procedure to accepting array of object and do the desired operation, I am here just inserting the entries in table as:

CREATE OR REPLACE PROCEDURE SP_INSERT_STUDENT_RECORD_IN_BULK(
IN_STUDENT_RECORDS STUDENT_ARRAY) IS
BEGIN 
  FOR i IN IN_STUDENT_RECORDS.first.. IN_STUDENT_RECORDS.last
   LOOP
    BEGIN 
      INSERT INTO STUDENT(ID,NAME) VALUES
      (IN_STUDENT_RECORDS(i).ID, IN_STUDENT_RECORDS(i).NAME)
    END;
   END LOOP:
END SP_INSERT_STUDENT_RECORD_IN_BULK;

Code snippet to call Procedure through Java

import org.springframework.jdbc.core.*;
import oracle.jdbc.OracleConnection;
....
List<Student> students = getStudentList();
try(Connection hikariCon = dataSource.getConnection()){
    if(hikariCon.isWrapperFor(OracleConnection.class)){
        OracleConnection con = hikariCon.unwrap(OracleConnection.class);
        Object[] students = students.stream().map(student -> {
            return con.createStruct(STUDENT_TYPE, new Object[]{
                student.getId(),
                student.getName()
            });
        }).collect(Collectors.list()).flatMap(List::stream).toArray();
        
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource)
                                .withProcedureName("SP_INSERT_STUDENT_RECORD_IN_BULK")
                                .declareParameters(
                                new SqlParameter("IN_STUDENT_RECORDS", Types.ARRAY));
        jdbcCall.execute(con.createOracleArray(STUDENT_ARRAY,students))
    }
} catch(Exception e){
    log.error("Error due to- {}",e,getMessage());
}

Collectives™ on Stack Overflow

java - passing array in oracle stored procedure

3 Answers 3

4 Comments

Comments

Steps to create the procedure in oracle.

Code snippet to call Procedure through Java

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Steps to create the procedure in oracle.

Code snippet to call Procedure through Java

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related