javaoracle-databasejava-stored-procedures

calling oracle stored procedure with table object as input using java

i try to call oracle stored procedure with 4 parameters 1 as input of custom type table and get the following error and PriceInBundle_Input_Table is an object with two element of MaterialNumber nvarchar2(22), BundleNumber nvarchar2(22)

java.sql.SQLException: ORA-03115: unsupported network datatype or representation

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1034)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
    at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1088)
    at  com.wincash.test.service.PriceInBundle.getPriceInBundle(PriceInBundle.java:33)
    at com.wincash.test.Wincash.main(Wincash.java:10)

and this is the stored procedure i try to call 

      create or replace PROCEDURE        GET_PRICE_IN_BUNDLE(
    P_IN_PriceInBundle_Input IN PriceInBundle_Input_Table,
    P_OUT_PriceInBundle_Output OUT PriceInBundle_Output_Table,
    P_OUT_RETURN_CODE OUT NUMBER,
    P_OUT_RETURN_MESSAGE OUT VARCHAR2 )
AS
  V_PRICE         NUMBER:=-1;
  V_Count_Article NUMBER:=-1;
  V_Count_Bundle  NUMBER:=-1;
  V_Count_Matset  NUMBER :=-1;
BEGIN
  P_OUT_RETURN_CODE          := -200;
  P_OUT_RETURN_MESSAGE       :='Initial';
  P_OUT_PriceInBundle_Output := PriceInBundle_Output_Table();
  FOR item_record_index IN P_IN_PriceInBundle_Input.FIRST .. P_IN_PriceInBundle_Input.LAST
  LOOP
    P_OUT_PriceInBundle_Output.EXTEND ;
    P_OUT_PriceInBundle_Output(item_record_index)                := PriceInBundle_Output_Record('','',-1,-1,'');
    P_OUT_PriceInBundle_Output(item_record_index).MaterialNumber := P_IN_PriceInBundle_Input(item_record_index).MaterialNumber ;
    P_OUT_PriceInBundle_Output(item_record_index).BundleNumber   := P_IN_PriceInBundle_Input(item_record_index).BundleNumber ;
    V_PRICE                                                      :=-1;
    BEGIN
      SELECT MAX(PRICE)
      INTO V_PRICE
      FROM matset
      WHERE compid IN
        (SELECT gid
        FROM material
        WHERE matno= P_IN_PriceInBundle_Input(item_record_index).MaterialNumber
        AND status<>'D'
        )
      AND matid IN
        (SELECT gid
        FROM material
        WHERE matno= P_IN_PriceInBundle_Input(item_record_index).BundleNumber
        AND status<>'D'
        )
      AND status                                                   <>'D';
      IF V_PRICE                                                   IS NOT NULL THEN
        P_OUT_PriceInBundle_Output(item_record_index).Price        := V_PRICE;
        P_OUT_PriceInBundle_Output(item_record_index).ResultCode   :=0;
        P_OUT_PriceInBundle_Output(item_record_index).ResultMessage:='SUCCESS';
      ELSE
        SELECT COUNT(*)
        INTO V_Count_Matset
        FROM matset
        WHERE compid IN
          (SELECT gid
          FROM material
          WHERE matno= P_IN_PriceInBundle_Input(item_record_index).MaterialNumber
          AND status<>'D'
          )
        AND matid IN
          (SELECT gid
          FROM material
          WHERE matno= P_IN_PriceInBundle_Input(item_record_index).BundleNumber
          AND status<>'D'
          )
        AND status      <>'D';
        IF V_Count_Matset>0 THEN
          SELECT MAX(price)
          INTO V_PRICE
          FROM material
          WHERE matno                                                 =P_IN_PriceInBundle_Input(item_record_index).MaterialNumber
          AND status                                                  ='A';
          P_OUT_PriceInBundle_Output(item_record_index).Price        := V_PRICE;
          P_OUT_PriceInBundle_Output(item_record_index).ResultCode   :=0;
          P_OUT_PriceInBundle_Output(item_record_index).ResultMessage:='Success';
        ELSE
          SELECT COUNT(*)
          INTO V_Count_Article
          FROM material
          WHERE matno                                                   = P_IN_PriceInBundle_Input(item_record_index).MaterialNumber
          AND status                                                    ='A';
          IF V_Count_Article                                            =0 THEN
            P_OUT_PriceInBundle_Output(item_record_index).Price        := -1;
            P_OUT_PriceInBundle_Output(item_record_index).ResultCode   := -2;
            P_OUT_PriceInBundle_Output(item_record_index).ResultMessage:='Wrong Article Number';
          ELSE
            SELECT COUNT(*)
            INTO V_Count_Bundle
            FROM material
            WHERE matno                                                   = P_IN_PriceInBundle_Input(item_record_index).BundleNumber
            AND status                                                    ='A';
            IF V_Count_Bundle                                             =0 THEN
              P_OUT_PriceInBundle_Output(item_record_index).Price        := -1;
              P_OUT_PriceInBundle_Output(item_record_index).ResultCode   := -3;
              P_OUT_PriceInBundle_Output(item_record_index).ResultMessage:='Wrong Bundle Number';
              ELSE
               P_OUT_PriceInBundle_Output(item_record_index).Price        := -1;
              P_OUT_PriceInBundle_Output(item_record_index).ResultCode   := -4;
              P_OUT_PriceInBundle_Output(item_record_index).ResultMessage:='Article does not belong to bundle';
            END IF;
          END IF;
        END IF;
      END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      P_OUT_PriceInBundle_Output(item_record_index).Price         := -1;
      P_OUT_PriceInBundle_Output(item_record_index).ResultCode    := -4 ;
      P_OUT_PriceInBundle_Output(item_record_index).ResultMessage := 'Invalid Input,No price found' ;
    WHEN OTHERS THEN
      P_OUT_PriceInBundle_Output(item_record_index).Price         := -1;
      P_OUT_PriceInBundle_Output(item_record_index).ResultCode    := -100 ;
      P_OUT_PriceInBundle_Output(item_record_index).ResultMessage :='UNKNOWN ERROR ' ||SQLERRM ;
    END ;
  END LOOP;
  P_OUT_RETURN_CODE    := 0 ;
  P_OUT_RETURN_MESSAGE := 'Success' ;
EXCEPTION
WHEN OTHERS THEN
  P_OUT_RETURN_CODE    := -100 ;
  P_OUT_RETURN_MESSAGE :='UNKNOWN ERROR ' ||SQLERRM ;
END;

i try to call using the following java code 

import com.wincash.test.config.DBConnection;

import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class PriceInBundle {
    private static Logger logger= Logger.getLogger(PriceInBundle.class);
    private static Connection conn=null;
    private static OracleCallableStatement callStatement=null;

    public Boolean getPriceInBundle() {
        Boolean rs=Boolean.FALSE;
        conn=DBConnection.getConnection();
        try {
            Object [] table= new Object[2];

            ArrayDescriptor priceInBundleTable= ArrayDescriptor.createDescriptor("PRICEINBUNDLE_INPUT_TABLE", conn);
            ARRAY priceInTable= new ARRAY(priceInBundleTable, conn, table);
            callStatement=(OracleCallableStatement)conn.prepareCall("{call GET_PRICE_IN_BUNDLE(?,?,?,?)}");
            callStatement.setARRAY(1, priceInTable);
            callStatement.registerOutParameter(2, java.sql.Types.ARRAY);
            callStatement.registerOutParameter(3, java.sql.Types.NUMERIC);
            callStatement.registerOutParameter(4, java.sql.Types.VARCHAR);

            rs=callStatement.execute();
        } catch (SQLException e) {
//          logger.info(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                conn.close();
                callStatement.close();
            } catch (SQLException e) {
                logger.info(e.getMessage());
            }

        }
        return rs;
    }
}

please support guys

Solution

  • i found the solution here just add oracle custom type to output paramter like the following code change this code to 

    callStatement.registerOutParameter(2, java.sql.Types.ARRAY);

    to

    callStatement.registerOutParameter(2, java.sql.Types.ARRAY,"PRICEINBUNDLE_OUTPUT_TABLE");

    and problem solved thanks to Marmite Bomber for his comment