java調用oracle數群組類型

標籤：調用   import   不能   oid   nec   message   ted   .exe   ref   

首先當然是在oracle中建立type

CREATE OR REPLACE TYPE cux_proxy_bid_award_rec IS OBJECT(  trading_partner_id NUMBER,  bid_price          NUMBER,  bid_publish_date   DATE,  bid_award_flag     VARCHAR2(10));

再建立引用type

CREATE OR REPLACE TYPE cux_proxy_bid_award_tbl IS TABLE OF CUX_PROXY_BID_AWARD_REC;

 

java中調用

import java.util.ArrayList;import java.sql.SQLException;import oracle.sql.ARRAY;import oracle.sql.ArrayDescriptor;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleConnection;    public void crtSuppResponseAndBidAward(String auctionHeaderId){         java.util.ArrayList  array = new  java.util.ArrayList();                PonSourceProxyBidVOImpl vo  = this.getPonSourceProxyBidVO1();        vo.first();        vo.previous();        while(vo.hasNext()){            Row row = vo.next();            Object[] obj = new Object[4];            obj[0] = (Number)row.getAttribute("TradingPartnerId");            obj[1] = (Number)row.getAttribute("BidPrice");            obj[2] = (Date)row.getAttribute("BidPublishDate");            obj[3] = (String)row.getAttribute("BidAwardFlag")!=null?row.getAttribute("BidAwardFlag"):"N";            //構造ArrayList            array.add(obj);        }                OracleCallableStatement statement = null;        OracleConnection oracleConnection = (OracleConnection)this.getOADBTransaction().getJdbcConnection();        try{            //將arraylist解析為STRUCT            STRUCT[] arrayOfSTRUCT = createMyRows(oracleConnection, array);                        //此處使用引用的TYPE
            //不能調用在PACKAGE中定義的TYPE            ArrayDescriptor tableOfBidDescriptor =                                           ArrayDescriptor.createDescriptor("CUX_PROXY_BID_AWARD_TBL",                                                                                       oracleConnection);                                                                                                          ARRAY localARRAY = new ARRAY(tableOfBidDescriptor, oracleConnection, arrayOfSTRUCT);                       OracleCallableStatement stmt = (OracleCallableStatement)getOADBTransaction().createCallableStatement("{ call CUX_PON_SOURCING_PUB.AUTO_CRT_BID_AWARD(:1, :2, :3, :4, :5) }", -1);                       String  aucHeaderIdEncrypt = SourcingServerUtil.URLEncrypt(getOADBTransaction(), auctionHeaderId );            stmt.setString(1, aucHeaderIdEncrypt);            stmt.setString(2, auctionHeaderId);            stmt.setARRAY(3, localARRAY);            stmt.registerOutParameter(4, Types.NUMERIC);            stmt.registerOutParameter(5, Types.VARCHAR);            stmt.execute();            oracle.sql.NUMBER retState = stmt.getNUMBER(4);            retState.toString();            System.out.println("stmt.getNUMBER(4)  "+stmt.getNUMBER(4));            LogUtil.of(" String.valueOf(stmt.getNUMBER(4))  "+String.valueOf(stmt.getNUMBER(4))+" retState "+retState.stringValue(), this).print(this);                        ModelUtil.commit(this, true);                        String retStatus = stmt.getNUMBER(4).stringValue() ;            String retMsg = stmt.getString(5);                                    if( "0".equals(retStatus)){                OAException dialogMsg = new OAException("決標完成！", OAException.CONFIRMATION);                this.getOADBTransaction().putDialogMessage(dialogMsg);            }else{                throw new OAException(retMsg);            }                    }catch(SQLException e){            throw OAException.wrapperException(e);        }                    }    ////將arraylist解析為STRUCT    private STRUCT[] createMyRows(Connection paramConnection,                                    java.util.ArrayList paramArrayList) {                int j = paramArrayList.size();        STRUCT[] arrayOfSTRUCT;        try {            //此處使用定義的原始TYPE OBJECT，或者TABLE            StructDescriptor localStructDescriptor =                 StructDescriptor.createDescriptor("CUX_PROXY_BID_AWARD_REC",                                                   paramConnection);            int k;            arrayOfSTRUCT = new STRUCT[j];            for (k = 0; k < j; k++) {                arrayOfSTRUCT[k] =                         new STRUCT(localStructDescriptor, paramConnection,                                    (Object[])paramArrayList.get(k));            }        } catch (Exception localException) {            throw OAException.wrapperException(localException);        }        return arrayOfSTRUCT;    }

 參考：

oracle預存程序輸入輸出數組對象和java調用情況如何把java中的arrayList轉化為oracle中的數組(array)

在PL/SQL中直接使用ORACLE數組

DECLARE  l_rec cux_proxy_bid_award_tbl := cux_proxy_bid_award_tbl();BEGIN  FOR i IN 1 .. 5 LOOP    l_rec.extend; -- 必須指定，否則會報指標越界    l_rec(i) := cux_proxy_bid_award_rec(i, 100, SYSDATE, ‘Y‘); -- 如果是record也可以直接賦值cux_proxy_bid_award_rec,record的方式僅限於在PACKAGE中定義的TYPE，獨立的TYPE只能是OBJECT  END LOOP;  dbms_output.put_line(l_rec.count);  dbms_output.put_line(l_rec(3).trading_partner_id);END;

參考：

ORA-06531: 引用未初始化的收集的問題解決

java調用oracle數群組類型