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

Question

I have a plsql procedure

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);

And I try to call it inside of my Java Code. I did it already with other procedures where all parameters was of type VARCHAR, but here all params are "bd_tb_struct"

create or replace TYPE bd_tb_struct FORCE
AS
OBJECT
(
start_ts   TIMESTAMP (3) ,
end_ts     TIMESTAMP (3) ,
time_type  NUMBER (19) ,
duration   NUMBER (12) ) FINAL ;

I also have a Java Class with this Comment. "Class of the corresponding type in the database. (bd_tb_struct )"

BoundsSqlType.java

Can some one explain me how I can call my procedure?

Answer 1

If you're open to using a third party library, jOOQ generates code for all of your PL/SQL packages, UDTs, procedures, functions, etc. to spare you the hassle of doing the binding manually.

So, in your case, you could call your procedure like this, using the generated stubs:

BdTbStructRecord result = Routines.mergeTimeBounds(
    configuration, // This contains your JDBC connection and other things
    new BdTbStructRecord(start1, end1, time1, duration1),
    new BdTbStructRecord(start2, end2, time2, duration2)
);

This does something similar to what the accepted answer does with JDBC directly, but:

• You don't have to work with strings, everything type checks (and IDE auto completes)
• You don't have to remember data types and parameter order
• When you change the procedure in your database, and regenerate the code, then your client code stops compiling, so you'll notice the problem early on

Disclaimer: I work for the company behind jOOQ.

Answer 2

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

Answer 3

Use oracle.jdbc.OracleStruct to map your custom type. Check Oracle's docs at https://docs.oracle.com/database/121/JJDBC/oraoot.htm#JJDBC28431

PreparedStatement ps= conn.prepareStatement("text_of_prepared_statement");
Struct mySTRUCT = conn.createStruct (...);
((OraclePreparedStatement)ps).setOracleObject(1, mySTRUCT);