How to handle Oracle Store Procedure call with Ora

问题:

I doing a Proof Of Concept to figure out how efficient to call a store procedure using EclipseLink. I was able to call oracle store procedure using EclispeLink with Scalar/primitive data types (link Integer, varchar etc). I wanted to understand how can I handle Oracle Store procedure from EclipseLink with collection(Oracle Types/User defined types) as input or output parameters. I would really appreciate if some one help me understand this with an example.

Thanks MAK

Here is the example which I implemented using cursor as output, and I want to replace cursor with user defined oracle type which is also mention below. Call procedure get_holiday_list_type from Java using Eclipselink.

Any help I really appreciate it.

Main Class

package services;

import java.util.ArrayList;
import java.util.List;
import java.util.ListIterator;

import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;
import org.eclipse.persistence.queries.DataReadQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseRecord;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.Project;
import org.eclipse.persistence.sessions.Session;

public class TestEclipseLinkJPA {

    Session s;

    TestEclipseLinkJPA() {

        String DATABASE_USERNAME = "SD64_FIRMCONFIG";
        String DATABASE_PASSWORD = "sdbaseline";
        String DATABASE_URL = "jdbc:oracle:thin:@GWMPCMTD.ea:1527:GWMPCMTD";
        String DATABASE_DRIVER = "oracle.jdbc.driver.OracleDriver";

        DatabaseLogin login = new DatabaseLogin();
        login.setUserName(DATABASE_USERNAME);
        login.setPassword(DATABASE_PASSWORD);
        login.setConnectionString(DATABASE_URL);
        login.setDriverClassName(DATABASE_DRIVER);
        login.setDatasourcePlatform(new Oracle11Platform());
        ((DatabaseLogin) login).bindAllParameters();

        Project project = new Project(login);
        s = project.createDatabaseSession();
        s.setLogLevel(SessionLog.FINE);
        ((DatabaseSession) s).login();

    }

    public static void main(String args[]) {

        TestEclipseLinkJPA test = new TestEclipseLinkJPA();
        test.run();
    }

    protected void run() {
        testProcCursor();
    }

    /*
     * Run Proc with scalar input and cursor output
     */
    @SuppressWarnings("unchecked")
    private void testProcCursor() {
        List outList = new ArrayList();
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("test_holiday_pkg.get_holiday_list_cur");
        call.addNamedArgument("i_user_id");
        call.addNamedArgument("i_hol_year");
        call.useNamedCursorOutputAsResultSet("o_approvalreasonlist");

        DataReadQuery query = new DataReadQuery();
        query.setCall(call);
        query.addArgument("i_user_id");
        query.addArgument("i_hol_year");

        List<Integer> queryArgs = new ArrayList<Integer>();
        queryArgs.add(Integer.valueOf(50077));
        queryArgs.add(Integer.valueOf(2010));

        outList = (List) s.executeQuery(query, queryArgs);
        ListIterator<DatabaseRecord> litr = ((List<DatabaseRecord>) outList)
                .listIterator();
        while (litr.hasNext()) {
            DatabaseRecord record = (DatabaseRecord) litr.next();
            System.out.println("Value -->" + record.get("BSNS_OBJCT_ID"));
        }
    }

}

Object

package dataobjects;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class KeyValuePairTo implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = -5022039632874557887L;
    @Id
    @Column(name = "BSNS_OBJCT_ID")
    private String key;
    @Column(name = "DSCRPTN")
    private String value;

    public String getKey() {
        return key;
    }

    public void setKey(String key) {
        this.key = key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

Procedure details

drop type obj_key_value_array2;

CREATE OR REPLACE TYPE obj_key_value2 AS OBJECT (
   bsns_objct_id   VARCHAR2 (15 CHAR),
   dscrptn         VARCHAR2 (65 CHAR)
)
/

CREATE OR REPLACE TYPE obj_key_value_array2 AS TABLE OF obj_key_value2
/


CREATE OR REPLACE PACKAGE test_holiday_pkg1
AS
   PROCEDURE get_holiday_list_cur (
      i_user_id              IN       NUMBER,
      i_hol_year             IN       NUMBER,
      o_approvalreasonlist   OUT      sys_refcursor
   );

   PROCEDURE get_holiday_list_type (
      i_user_id              IN       NUMBER,
      i_hol_year             IN       NUMBER,
      o_approvalreasonlist   OUT      obj_key_value_array2
   );
END test_holiday_pkg1;
/

CREATE OR REPLACE PACKAGE BODY TEST_HOLIDAY_PKG1
AS
   PROCEDURE get_holiday_list_cur (
      i_user_id              IN       NUMBER,
      i_hol_year             IN       NUMBER,
      o_approvalreasonlist   OUT      sys_refcursor
   )
   AS
   BEGIN
      OPEN o_approvalreasonlist FOR
         SELECT to_char(BSNS_OBJCT_ID) bsns_objct_id, DSCRPTN
           FROM holiday
          WHERE hol_year = i_hol_year;
   END get_holiday_list_cur;

   PROCEDURE get_holiday_list_type (
      i_user_id              IN       NUMBER,
      i_hol_year             IN       NUMBER,
      o_approvalreasonlist   OUT      obj_key_value_array2
   )
   AS
   BEGIN
         SELECT obj_key_value2 (to_char(bsns_objct_id), dscrptn)
      BULK COLLECT INTO o_approvalreasonlist
        FROM holiday
       WHERE hol_year = i_hol_year;
   END get_holiday_list_type;
END test_holiday_pkg1;
/
show errors;

回答1:

Maybe have a look at the EclipseLink extensions for Stored Procedure Query. Note that JPA extensions are obviously not portable, like oracle specific types (so this defeats JPA twice).