Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC - Issue when invoking Stored procedure returning multiple cursors Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC - Issue when invoking Stored procedure returning multiple cursors

    I am facing an issue when invoking an oracle stored procedure that's returning multiple cursors(7) . For some of the cursors values are returned as expected (using their respective row mappers), others are just null (when I look up by their name from the results Map after execute), though they return data while calling the procedure from outside spring jdbc for the same input(from using pl/sql developer). As far as you know, are there any restriction's on the number of cursors/out parameters when using spring jdbc stored procedure class to invoke a procedure or can some one please point out what I am missing here?

    1) the oracle stored procedure i am calling is defined as follows,
    PROCEDURE get_pg_order_by_id (
    order_no_in IN NUMBER,
    pg_order_out OUT SYS_REFCURSOR, --t_pg_order_cur
    pg_order_paymt_out OUT SYS_REFCURSOR, --t_pg_order_paymt_cur
    pg_order_lines_out OUT SYS_REFCURSOR, --t_pg_order_lines_cur
    pg_order_linesur_out OUT SYS_REFCURSOR, --t_pg_order_linesur_cur
    pg_order_lineadj_out OUT SYS_REFCURSOR, --t_pg_order_lineadj_cur
    pg_order_shipments_out OUT SYS_REFCURSOR, --t_pg_order_lineadj_cur
    pg_order_shipmentLines_out OUT SYS_REFCURSOR --t_pg_order_lineadj_cur
    ) IS
    BEGIN
    ...................


    2) Java Spring stored procedure class impl for invoking the above procedure,

    public class GetOrderDetailStoredProcedure extends StoredProcedure {

    private static final String PROCEDURE_NAME = "wcu_order_hist_pg_pk0.get_pg_order_by_id";
    // input
    private static final String ORDER_DETAIL_ORDERID = "order_no_in";

    // output's
    private static final String ORDER_DETAIL_OUTPUT = "pg_order_out";
    private static final String PAYMT_METHOD_OUTPUT = "pg_order_paymt_out";
    private static final String ORDER_LINE_OUTPUT = "pg_order_lines_out";
    private static final String ORDER_LINE_SUR_CHARGE_OUTPUT = "pg_order_linesur_out";
    private static final String ORDER_LINE_ADJSTMNT_OUTPUT = "pg_order_lineadj_out";
    private static final String ORDER_SHIPMENT_OUTPUT = "pg_order_shipments_out";
    private static final String ORDER_SHIPMENT_LINE_OUTPUT = "pg_order_shipmentLines_out";
    private static final int FETCH_SIZE = 1000;
    private final static Logger LOG = LoggerFactory
    .getLogger(GetOrderDetailStoredProcedure.class);

    public GetOrderDetailStoredProcedure(DataSource dataSource) {
    super(dataSource, PROCEDURE_NAME);
    super.setFetchSize(FETCH_SIZE);
    declareParameter(new SqlParameter(ORDER_DETAIL_ORDERID, Types.NUMERIC));

    declareParameter(new SqlOutParameter(ORDER_DETAIL_OUTPUT,
    OracleTypes.CURSOR, new OrderDetailMapper()));
    declareParameter(new SqlOutParameter(PAYMT_METHOD_OUTPUT,
    OracleTypes.CURSOR, new PaymentMethodMapper()));
    declareParameter(new SqlOutParameter(ORDER_LINE_OUTPUT,
    OracleTypes.CURSOR, new OrderLineMapper()));
    declareParameter(new SqlOutParameter(ORDER_LINE_SUR_CHARGE_OUTPUT,
    OracleTypes.CURSOR));
    declareParameter(new SqlOutParameter(ORDER_LINE_ADJSTMNT_OUTPUT,
    OracleTypes.CURSOR, new AdjustmentMapper()));
    declareParameter(new SqlOutParameter(ORDER_SHIPMENT_OUTPUT,
    OracleTypes.CURSOR, new ShipmentMapper()));
    declareParameter(new SqlOutParameter(ORDER_SHIPMENT_LINE_OUTPUT,
    OracleTypes.CURSOR, new ShipmentLineMapper()));
    compile();
    LOG.info(PROCEDURE_NAME + " Compiled....");
    }

    @SuppressWarnings("unchecked")
    public OrderDetail execute(Long orderId) {

    Map<String, Object> input = new HashMap<String, Object>();
    input.put(ORDER_DETAIL_ORDERID, orderId);
    long begin = System.currentTimeMillis();
    Map<String, Object> results = super.execute(input);
    ..................................

    all the row mapper classes are within this class as an inner class.
Working...
X