Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedure Oracle/setplsqlindextable Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedure Oracle/setplsqlindextable

    How do I declare Sql parameters in a Spring StoredProcedure class for an Oracle StoredProcedure that take "table of" type parameters? More specifically is the oracle setPlsqlSetIndexTable method supported? If the setPlsqlSetIndexTable is not supported is there away to still use spring JDBC support and make a call to the stored procedure?

    Ex. Oracle Stored Procedure:

    Code:
    create or replace package ora_pkg is
    
    type T_TableOfId is table of bulk_table.id%type index by binary_integer;
    type T_TableOfName is table of bulk_table.name%type index by bineary_integer;
    
    procedure testInsert(
      nId in T_TableOfId, 
      nName in T_TableOfName,
      vStatus out number, 
      vErrorString out varchar2) is
    begin
      -- plsql code
    end testInsert;
    Example Spring StoredProcedure:

    Code:
    package loader.dao.jdbc;
    
    import java.sql.Types;
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    
    import loader.model.
    
    /**
     * @author GTUBERSON
     *
     */
    public class BulkProcedure extends StoredProcedure {
        private static final String SQL = "ora_pkg.testInsert";
    
        /**
         * 
         */
        public BulkProcedure(DataSource ds) {
            setDataSource(ds);
            setSql(SQL);
            setFunction(false);
           
            declareParameter(new SqlParameter("nId", ???));
            declareParameter(new SqlParameter("nName", ???));
            
            declareParameter(new SqlOutParameter("vStatus", Types.INTEGER));
            declareParameter(new SqlOutParameter("vErrorString", Types.VARCHAR));
            compile();
        }
    
        public Map excute(BulkData data) {
            Map results = new HashMap();
            inParams.put("nId", data.getIds());
            inParams.put("vName", data.getNames());
            return results;
        }
    
    }
    BulkData code:
    Code:
    package loader.model;
    
    import java.util.List;
    
    /**
     * @author GTUBERSON
     *  
     */
    public class BulkData {
    
        private List ids;
    
        private List names;
    
        public BulkData() {
            super();
        }
    
        public List getIds() {
            return ids;
        }
    
        public void setIds(List ids) {
            this.ids = ids;
        }
    
        public List getNames() {
            return names;
        }
    
        public void setNames(List names) {
            this.names = names;
        }

  • #2
    StoredProcedure Oracle/setplsqlindextable

    After scouring the forum I have found a solution to my problem. I could not find the exact solution because I had to change the procedure from using an Index table to an array.

    I made the following changes to my code:

    1) I moved the type definitions outside of the Oracle package.
    2) I removed index by binary_integer from the type definitions
    3) I found a piece of code that shows how to create an Oracle array descriptor for the parameters declared in the stored procedure.
    4) Modified stored procedure to be initialized with a JdbcTemplate instead of a datasource. I did this because I needed access to the native jdbc objects and you can only do this by setting the NativeJdbcExtractor on the JdbcTemplate.

    Any comments or suggestion would be appreciated.

    The following forum topics helped with the solution:
    http://forum.springframework.org/vie...rocedure+array
    http://forum.springframework.org/viewtopic.php?t=833

    New stored procdure
    Code:
    CREATE OR REPLACE TYPE T_nRECORD_UID IS TABLE OF NUMBER(10);
    /
    
    CREATE OR REPLACE TYPE TYPE_TNARRAY IS TABLE OF NUMBER(10);
    /
    
    CREATE OR REPLACE TYPE TP_vArray IS TABLE OF VARCHAR2(10);
    /
    
    CREATE OR REPLACE TYPE T_ERR_INDEX IS TABLE OF NUMBER(3);
    /
    
    CREATE OR REPLACE PACKAGE TEST_PKG IS
    
      PROCEDURE SP_INS_T (
        nRECORD_UID  IN T_nRECORD_UID,
        nArray       IN TP_nArray,
        vArray       IN TP_vArray,
        vStatus      OUT NUMBER,
        vErrorString OUT VARCHAR2,
        vErrorArray  IN OUT T_ERR_INDEX
      );
      
    END TEST_PKG;
    /
    
    CREATE OR REPLACE PACKAGE BODY TEST_PKG is
    
      PROCEDURE SP_INS_T (
        nRECORD_UID  IN T_nRECORD_UID,
        nArray       IN TP_nArray,
        vArray       IN TP_vArray,
        vStatus      OUT NUMBER,
        vErrorString OUT VARCHAR2,
        vErrorArray  IN OUT T_ERR_INDEX
      ) 
      IS
        e_InsertBulkFailed EXCEPTION;
        Extended_error VARCHAR2(2000);
        bulk_errors EXCEPTION;
        error_count NUMBER;
        error_length NUMBER;
        PRAGMA exception_init(bulk_errors, -24381);
      BEGIN
      -- body
      END SP_INS_T;
    
      
    END TEST_PKG;
    /
    New Spring Stored Procedure:
    Code:
    package loader.dao.jdbc;
    
    import java.sql.Types;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    
    import loader.model.TestDataLists;
    
    public class SpBulkInsertTest extends StoredProcedure {
        private static final String SQL = "TEST_PKG.SP_INS_T";
    
        public SpBulkInsertTest(JdbcTemplate ds) {
            setJdbcTemplate(ds);
            setSql(SQL);
            setFunction(false);
    
            declareParameter(new SqlParameter("nRECORD_UID", Types.ARRAY,
                    "T_NRECORD_UID"));
            declareParameter(new SqlParameter("nArray", Types.ARRAY,
                    "TP_NARRAY"));
            declareParameter(new SqlParameter("vArray", Types.ARRAY,
                    "TP_VARRAY"));
            declareParameter(new SqlOutParameter("vStatus", Types.INTEGER));
            declareParameter(new SqlOutParameter("vErrorString", Types.VARCHAR));
            declareParameter(new SqlOutParameter("vErrorArray", Types.ARRAY,
                    "T_ERR_INDEX"));
            compile();
        }
    
        public Map bulkLoadData(List listOfData) {
            Map results = new HashMap();
            TestDataLists testDataLists = new TestDataLists();
            int start = 0;
            while &#40;&#40;start < listOfData.size&#40;&#41;&#41; || &#40;start < 0&#41;&#41; &#123;
                start = testDataLists.addData&#40;listOfData, start&#41;;
    
                if &#40;start < 0&#41; &#123;
                    break;
                &#125;
    
                Map inParams = new HashMap&#40;&#41;;
                inParams.put&#40;"nRECORD_UID", testDataLists.getRecordUids&#40;&#41;&#41;;
    
                inParams.put&#40;"nArray", testDataLists.getNumbers&#40;&#41;&#41;;
                inParams.put&#40;"vArray", testDataLists.getValues&#40;&#41;&#41;;
                inParams.put&#40;"vErrorArray", testDataLists.getErrorCodes&#40;&#41;&#41;;
                results = execute&#40;inParams&#41;;
                printMap&#40;results&#41;;
            &#125;
            return results;
        &#125;
    
        private static void printMap&#40;Map r&#41; &#123;
            Iterator i = r.entrySet&#40;&#41;.iterator&#40;&#41;;
            while &#40;i.hasNext&#40;&#41;&#41; &#123;
                System.out.println&#40;&#40;String&#41; i.next&#40;&#41;.toString&#40;&#41;&#41;;
            &#125;
        &#125;
    
    &#125;
    Test data lists:
    Code:
    package loader.model;
    
    import java.util.ArrayList;
    import java.util.List;
    
    import jdbc.support.OracleSqlArray;
    
    public class TestDataLists &#123;
    
        private final int MAX_ITEMS = 100;
    
        private OracleSqlArray recordUids;
    
        private OracleSqlArray numbers;
    
    
        private OracleSqlArray values;
    
        private OracleSqlArray errorCodes;
    
        public TestDataLists&#40;&#41; &#123;
            super&#40;&#41;;
        &#125;
    
        public int addData&#40;List listOfData, int start&#41; &#123;
            int result = -1;
    
            recordUids = null;
            numbers = null;
            values = null;
    
            if &#40;listOfData != null&#41; &#123;
                int arraySize = 0;
                int amtToAdd = listOfData.size&#40;&#41; - start;
    
                if &#40;amtToAdd <= MAX_ITEMS&#41; &#123;
                    arraySize = amtToAdd;
                &#125;
    
                if &#40;amtToAdd > MAX_ITEMS&#41; &#123;
                    arraySize = MAX_ITEMS;
                &#125;
    
                ArrayList newRecordUids = new ArrayList&#40;arraySize&#41;;
                ArrayList newNumbers = new ArrayList&#40;arraySize&#41;;
                ArrayList newValues = new ArrayList&#40;arraySize&#41;;
                ArrayList newErrorCodes = new ArrayList&#40;arraySize&#41;;
    
                for &#40;int i = 0; i < arraySize; i++&#41; &#123;
                    int idx = i + start;
                    TestData testData = &#40;TestData&#41; listOfData.get&#40;idx&#41;;
                    newRecordUids.add&#40;new Long&#40;idx&#41;&#41;;
                    newNumbers.add&#40;testData.getNumber&#40;&#41;&#41;;
                    newValues.add&#40;testData.getValue&#40;&#41;&#41;;
                    newErrorCodes.add&#40;new Integer&#40;-1&#41;&#41;;
                &#125;
    
                recordUids = new OracleSqlArray&#40;newRecordUids&#41;;
                numbers = new OracleSqlArray&#40;newNumbers&#41;;
                values = new OracleSqlArray&#40;newValues&#41;;
                errorCodes = new OracleSqlArray&#40;newErrorCodes&#41;;
                result = start + arraySize;
            &#125;
    
            return result;
        &#125;
    
        public OracleSqlArray getNumbers&#40;&#41; &#123;
            return numbers;
        &#125;
    
        public void setNumbers&#40;OracleSqlArray numbers&#41; &#123;
            this.numbers = numbers;
        &#125;
    
        public OracleSqlArray getValues&#40;&#41; &#123;
            return values;
        &#125;
    
        public void setValues&#40;OracleSqlArray values&#41; &#123;
            this.values = values;
        &#125;
    
        public OracleSqlArray getRecordUids&#40;&#41; &#123;
            return recordUids;
        &#125;
    
        public void setRecordUids&#40;OracleSqlArray ids&#41; &#123;
            recordUids = ids;
        &#125;
    
        public OracleSqlArray getErrorCodes&#40;&#41; &#123;
            return errorCodes;
        &#125;
    
        public void setErrorCodes&#40;OracleSqlArray errorCodes&#41; &#123;
            this.errorCodes = errorCodes;
        &#125;
    &#125;
    New OracleSqlArray type:
    Code:
    package jdbc.support;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.ArrayList;
    
    import oracle.jdbc.OracleConnection;
    
    import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
    
    public class OracleSqlArray extends AbstractSqlTypeValue &#123;
        private ArrayList values;
    
        public OracleSqlArray&#40;ArrayList values&#41; &#123;
            this.values = values;
        &#125;
    
        public Object createTypeValue&#40;Connection con, int sqlType, String typeName&#41;
                throws SQLException &#123;
            OracleConnection oracle = &#40;OracleConnection&#41; con;
    
            oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor&#40;
                    typeName, oracle&#41;;
            
    
            Object&#91;&#93; a = values.toArray&#40;&#41;;
            Object o = new oracle.sql.ARRAY&#40;desc, oracle, a&#41;;
            return o;
        &#125;
    &#125;
    Test Code:
    Code:
    package loader.dao.jdbc;
    
    import java.sql.Types;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.log4j.Logger;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    import org.springframework.jdbc.object.StoredProcedure;
    import org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor;
    
    import loader.model.TestData;
    
    public class TestStoredProcedure &#123;
    
        private static final Logger logger = Logger
                .getLogger&#40;TestStoredProcedure.class&#41;;
    
        public static void main&#40;String&#91;&#93; args&#41; &#123;
            TestStoredProcedure t = new TestStoredProcedure&#40;&#41;;
            t.test&#40;&#41;;
            System.out.println&#40;"Done!"&#41;;
        &#125;
    
        void test&#40;&#41; &#123;
            logger.info&#40;"setting up driver manager data source."&#41;;
            DriverManagerDataSource ds;
            JdbcTemplate jt;
    
            try &#123;
                ds = new DriverManagerDataSource&#40;&#41;;
                ds.setDriverClassName&#40;"oracle.jdbc.driver.OracleDriver"&#41;;
                ds.setUrl&#40;"jdbc&#58;oracle&#58;thin&#58;@xxx&#58;1521&#58;xxx"&#41;;
                ds.setUsername&#40;"xxx"&#41;;
                ds.setPassword&#40;"xxx"&#41;;
    
                SimpleNativeJdbcExtractor nje = new SimpleNativeJdbcExtractor&#40;&#41;;
                nje.setNativeConnectionNecessaryForNativeCallableStatements&#40;true&#41;;
                nje.setNativeConnectionNecessaryForNativePreparedStatements&#40;true&#41;;
                nje.setNativeConnectionNecessaryForNativeStatements&#40;true&#41;;
                jt = new JdbcTemplate&#40;ds&#41;;
                jt.setNativeJdbcExtractor&#40;nje&#41;;
    
                logger.info&#40;"Calling bulk insert stored procedure&#58;"&#41;;
                List testDataList = new ArrayList&#40;&#41;;
                SpBulkInsertTest bulkInsertTest = new SpBulkInsertTest&#40;jt&#41;;
                TestData testData;
    
                for &#40;int i = 0; i < 1001; i++&#41; &#123;
                    testData = getTestData&#40;i, "VAL"+i&#41;;
                    testDataList.add&#40;testData&#41;;
                &#125;
                logger.info&#40;"call string&#58; " + bulkInsertTest.getCallString&#40;&#41;&#41;;
                res = bulkInsertTest.bulkLoadData&#40;testDataList&#41;;
                printMap&#40;res&#41;;
    
            &#125; catch &#40;Exception e&#41; &#123;
                logger.error&#40;e&#41;;
            &#125;
    
        &#125;
    
        private static void printMap&#40;Map r&#41; &#123;
            Iterator i = r.entrySet&#40;&#41;.iterator&#40;&#41;;
            while &#40;i.hasNext&#40;&#41;&#41; &#123;
                System.out.println&#40;&#40;String&#41; i.next&#40;&#41;.toString&#40;&#41;&#41;;
            &#125;
        &#125;
    
        private static TestData getTestData&#40;int id, String name&#41; &#123;
            TestData result = new TestData&#40;&#41;;
            result.setNumber&#40;new Long&#40;id&#41;&#41;;
            result.setValue&#40;name&#41;;
            return result;
        &#125;
    &#125;

    Comment

    Working...
    X