Java calls Oracle array type

First of all, of course, create the type in Oracle

CREATE OR REPLACE  is OBJECT (  number,  bid_price          number,  bid_publish_date   DATE,  bid_award_flag     VARCHAR2(ten));

Re-establish reference type

CREATE OR REPLACE  is TABLE  of Cux_proxy_bid_award_rec;

Called in Java

Importjava.util.ArrayList;Importjava.sql.SQLException;ImportOracle.sql.ARRAY;ImportOracle.sql.ArrayDescriptor;ImportOracle.sql.STRUCT;ImportOracle.sql.StructDescriptor;Importoracle.jdbc.OracleCallableStatement;Importoracle.jdbc.OracleConnection;  Public voidCrtsuppresponseandbidaward (String auctionheaderid) {java.util.ArrayList array=Newjava.util.ArrayList (); Ponsourceproxybidvoimpl Vo= This. getPonSourceProxyBidVO1 ();        Vo.first ();        Vo.previous ();  while(Vo.hasnext ()) {row row=Vo.next (); object[] obj=NewObject[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"; //Construction ArrayListarray.add (obj); } oraclecallablestatement Statement=NULL; OracleConnection OracleConnection= (OracleConnection) This. Getoadbtransaction (). Getjdbcconnection (); Try{            //resolve ArrayList to structstruct[] Arrayofstruct =createmyrows (oracleconnection, array); //The referenced type is used here
The type defined in the package cannot be calledArraydescriptor Tableofbiddescriptor =Arraydescriptor.createdescriptor ("Cux_proxy_bid_award_tbl", OracleConnection); ARRAY LocalArray=NewARRAY (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=NewOaexception ("The bid is complete!") ", oaexception.confirmation);  This. Getoadbtransaction (). Putdialogmessage (DIALOGMSG); }Else{                Throw Newoaexception (retmsg); }                    }Catch(SQLException e) {Throwoaexception.wrapperexception (e); }                    }    ////resolve ArrayList to struct    Privatestruct[] Createmyrows (Connection paramconnection, Java.util.ArrayList Paramarrayl IST) {intj =paramarraylist.size ();        Struct[] arrayofstruct; Try {            //the original type OBJECT defined here is used, or the tableStructdescriptor Localstructdescriptor =Structdescriptor.createdescriptor ("Cux_proxy_bid_award_rec", paramconnection); intK; Arrayofstruct=NewStruct[j];  for(k = 0; k < J; k++) {Arrayofstruct[k]=NewSTRUCT (Localstructdescriptor, Paramconnection, (object[]) Paramarraylist.get (k)); }        } Catch(Exception localexception) {Throwoaexception.wrapperexception (localexception); }        returnarrayofstruct; }

Reference:

Oracle stored procedure input and output array objects and Java calls How to convert ArrayList in Java to arrays in Oracle (array)

Using Oracle arrays directly in PL/SQL

DECLAREL_rec cux_proxy_bid_award_tbl:=cux_proxy_bid_award_tbl ();BEGIN   forIinch 1..5LOOP l_rec.extend;--must be specified, otherwise the pointer is out of boundsL_rec (i):=Cux_proxy_bid_award_rec (I, -, Sysdate,'Y');--If the record can also be directly assigned the Cux_proxy_bid_award_rec,record method is limited to the type defined in the package, the standalone type can only be an object  ENDLOOP; Dbms_output.put_line (L_rec.Count); Dbms_output.put_line (L_rec (3). trading_partner_id);END;

Reference:

ORA-06531: Reference to uninitialized collection problem resolution

Java calls Oracle array type