How to Call Oracle Stored Procedure Which Include User-Defined Type in Java

How to call oracle stored procedure which include user-defined type in java?

Setting up a link between Oracle SQL objects and java objects manually is not a trivial task. In particular, arrays (or nested tables) of user-defined objects are more complex to pass from java to Oracle than arrays of standard datatypes. In other words, it is easier to call a procedure with signature:

(TABLE OF NUMBER, TABLE OF NUMBER, TABLE OF NUMBER)`

than a procedure whose signature is:

(TABLE OF (NUMBER, NUMBER, NUMBER))   <- your case

You can write a wrapper around your procedure to transform the second case into the first case.

---

That being said, it is by far not impossible to map your procedure. The following example is largely inspired by a post by Tom Kyte. Tom describes how to map a TABLE OF NUMBER using oracle.sql.ARRAY. In your case we will also have to use oracle.sql.STRUCT to map the IDS SQL object.

You may also want to browse the Oracle JDBC doc, in particular the chapter Working with Oracle Object Types.

First is a setup similar to yours:

SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER );
  2  /
Type created

SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS;
  2  /
Type created

SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS
  2  BEGIN
  3     FOR i IN 1 .. p_ids.COUNT LOOP
  4        dbms_output.put_line(p_ids(i).id1
  5                             || ',' || p_ids(i).id2
  6                             || ',' || p_ids(i).id3);
  7     END LOOP;
  8  END getInfo;
  9  /     
Procedure created

This is the java procedure:

SQL> CREATE OR REPLACE
  2  AND COMPILE JAVA SOURCE NAMED "ArrayDemo"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6  import oracle.sql.*;
  7  import oracle.jdbc.driver.*;
  8  
  9  public class ArrayDemo {
 10  
 11     public static void passArray() throws SQLException {
 12  
 13        Connection conn =
 14           new OracleDriver().defaultConnection();
 15  
 16  
 17        StructDescriptor itemDescriptor =
 18           StructDescriptor.createDescriptor("IDS",conn);
 19  
 20        Object[] itemAtributes = new Object[] {new Integer(1),
 21                                               new Integer(2),
 22                                               new Integer(3)};
 23        STRUCT itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes);
 24  
 25        itemAtributes = new Object[] {new Integer(4),
 26                                      new Integer(5),
 27                                      new Integer(6)};
 28        STRUCT itemObject2 = new STRUCT(itemDescriptor,conn,itemAtributes);
 29  
 30        STRUCT[] idsArray = {itemObject1,itemObject2};
 31  
 32        ArrayDescriptor descriptor =
 33           ArrayDescriptor.createDescriptor( "IDS_TABLE", conn );
 34  
 35        ARRAY array_to_pass =
 36           new ARRAY( descriptor, conn, idsArray );
 37  
 38        OraclePreparedStatement ps =
 39           (OraclePreparedStatement)conn.prepareStatement
 40           ( "begin getInfo(:x); end;" );
 41  
 42        ps.setARRAY( 1, array_to_pass );
 43        ps.execute();
 44  
 45     }
 46  }
 47  /
Java created

Let's call it:

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME 'ArrayDemo.passArray()';
  5  /
Procedure created

SQL> exec show_java_calling_plsql ;
1,2,3
4,5,6

PL/SQL procedure successfully completed

Java - How to call an oracle procedure with custom types?

Oracle Setup:

CREATE OR REPLACE TYPE BD_TB_STRUCT AS OBJECT(
  start_ts TIMESTAMP(3),
  end_ts   TIMESTAMP(3),
  time_type NUMBER(19),
  duration NUMBER(12)
) FINAL;
/

CREATE OR REPLACE PROCEDURE merge_time_bounds(
  s1_bd_t IN  bd_tb_struct,
  s2_bd_t IN  bd_tb_struct,
  r_bd_t  OUT bd_tb_struct
)
IS
  p_start TIMESTAMP(3) := LEAST(    s1_bd_t.start_ts,  s2_bd_t.start_ts );
  p_end   TIMESTAMP(3) := GREATEST( s1_bd_t.end_ts,    s2_bd_t.end_ts );
BEGIN
  r_bd_t := new BD_TB_STRUCT( 
                  p_start,
                  p_end,
                  COALESCE( s1_bd_t.time_type, s2_bd_t.time_type ),
                  ( CAST( p_end AS DATE ) - CAST( p_start AS DATE ) ) * 24 * 60 * 60
                );
END;
/

Java SQLData Class:

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneOffset;

public class BoundsSQL implements SQLData
{
  public static final String SQL_TYPE = "BD_TB_STRUCT";
  public java.sql.Timestamp start;
  public java.sql.Timestamp end;
  public BigInteger type;
  public BigInteger duration;

  public BoundsSQL()
  {
  }

  public BoundsSQL(
      final int year,
      final int month,
      final int dayOfMonth,
      final int hour,
      final int minute,
      final int seconds,
      final long duration,
      final long type )
  {
    final long epochSeconds = LocalDateTime.of(
        year,
        month,
        dayOfMonth,
        hour,
        minute,
        seconds
      ).toEpochSecond( ZoneOffset.UTC );
    this.start    = new Timestamp( epochSeconds * 1000 );
    this.end      = new Timestamp( (epochSeconds + duration) * 1000 );
    this.duration = BigInteger.valueOf( duration );
    this.type = BigInteger.valueOf( type );
  }

  @Override
  public String getSQLTypeName() throws SQLException
  {
    return SQL_TYPE;
  }

  @Override
  public void readSQL( SQLInput stream,
      String typeName ) throws SQLException
  {
    start    = stream.readTimestamp();
    end      = stream.readTimestamp();
    type     = stream.readBigDecimal().toBigInteger();
    duration = stream.readBigDecimal().toBigInteger();
  }

  @Override
  public void writeSQL( SQLOutput stream ) throws SQLException
  {
    stream.writeTimestamp( start );
    stream.writeTimestamp( end );
    stream.writeBigDecimal( new BigDecimal( type ) );
    stream.writeBigDecimal( new BigDecimal( duration ) );
  }

  @Override
  public String toString()
  {
    return String.format(
        "Start:    %s\nEnd:      %s\nDuration: %s\nType:     %s",
        start,
        end,
        duration,
        type
    );
  }
}

Call Stored Procedure from Java:

Call the stored procedure using OracleCallableStatement#setObject( int, Object ) to pass parameters and put the class into a type map and use OracleCallableStatement#registerOutParameter( int, int, string ) and OracleCallableStatement#getObject( int ) to retrieve the parameters.

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Map;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;

public class PassStructToProcedure
{

  public static void main( final String[] args ){
    OracleConnection con = null;
    try{
      Class.forName( "oracle.jdbc.OracleDriver" );

      con = (OracleConnection) DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:orcl",
          "USERNAME",
          "PASSWORD"
      );

      BoundsSQL bound1 = new BoundsSQL( 2019, 1, 1, 0, 0, 0, 10, 1 );
      BoundsSQL bound2 = new BoundsSQL( 2019, 1, 1, 0, 0, 5, 10, 2 );

      OracleCallableStatement st = (OracleCallableStatement) con.prepareCall(
          "{ call MERGE_TIME_BOUNDS( ?, ?, ? ) }"
      );

      st.setObject( 1, bound1 );
      st.setObject( 2, bound2 );
      st.registerOutParameter( 3, OracleTypes.STRUCT, BoundsSQL.SQL_TYPE );
      st.execute();

      Map<String,Class<?>> typeMap = con.getTypeMap();
      typeMap.put( BoundsSQL.SQL_TYPE, BoundsSQL.class );

      BoundsSQL out = (BoundsSQL) st.getObject( 3 );

      System.out.println( out.toString() );

      st.close();
    } catch (ClassNotFoundException | SQLException ex) {
      System.out.println( ex.getMessage() );
      ex.printStackTrace();
    } finally {
      try{
        if ( con != null )
          con.close();
      }
      catch( SQLException e )
      {

      }
    }
  }
}

Output:

Start:    2019-01-01 00:00:00.0
End:      2019-01-01 00:00:15.0
Duration: 15
Type:     1

How do I pass a user-defined type as an input to a stored procedure?

After a fair bit of mucking around, I was able to develop a solution. A few observations:

• There is not much documentation about how to do this on the web.
• It seems to me that using user-defined types as inputs is not well supported.
• I found I had to use a Struct which was counter-intuitive (as only arrays were used for outputs).
• The SQLData interface was not used, ie. writeSQL() was never called as I found I had to build the struct manually. readSQL() is called when mapping outputs.
• I had to use DB-specific code for array creation, in my case this meant Oracle classes.

It's possible I may be going about things the wrong way, so I'd welcome comments on my solution.

public void useExample(Example example) throws SQLException {
    String query = "begin example.useExample(?); end;";
    Connection connection = jdbcTemplate.getDataSource().getConnection();
    CallableStatement callableStatement = connection.prepareCall(query);

    Map<String, Class<?>> typeMap = connection.getTypeMap();
    typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
    connection.setTypeMap(typeMap);

    // Manually convert the example object into an SQL type.
    Object[] exampleAttributes = new Object[]{example.ID, example.Name};
    Struct struct = connection.createStruct(type.getObjectType(), exampleAttributes);

    // Build the array using Oracle specific code.
    DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
    OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
    Object[] data = new Object[]{struct};
    Array array oracleConnection.createOracleArray(Example.SQL_TABLE_TYPE_NAME, data);

    // Set the input value (finally).
    callableStatement.setObject("p_example", array);

    callableStatement.execute();
}

Call stored procedure from java for Oracle type TABLE

Finally found the answer here. After including the orai18n.jar in class path, it is working fine.

Oracle stored function/procedure with VARRAY or user-defined type as IN parameter

Answering my own question. Here is the solution that worked for me.

CREATE OR REPLACE FUNCTION FOOBAR (p1 IN ARRAY_TEST_T ) 
RETURN VARCHAR2 AUTHID CURRENT_USER AS
LANGUAGE JAVA
NAME 'foobar.FoobarFunction.test_function(java.sql.Array)
      return lava.lang.String';

package foobar;
public class FoobarFunction {
    public static String test_function(java.sql.Array array) {
        final Object[] content = (Object[]) array.getArray();
        for (Object c : content) {
                // expecting java.sql.Struct type for c;

                // get TEST_T attribute values for c1 and c2
                final Object[] attrs = ((java.sql.Struct) c).getAttributes();
                String c1 = (String) attrs[0];
                String c2 = (String) attrs[1];
            .......
        }

        return null;
    }
}

---

Related Topics