Oracle type application

1. Create an oracle type
Create or replace type bcescde. ibps_acct_amt_record_type is object (
Old_channel_seq_no varchar2 (30), -- channel serial number applied for the original query
Partyno varchar2 (12), -- customer ID
Agreement_no varchar2 (60), -- Protocol Number
Acct_no varchar2 (32), -- account
Acct_type varchar2 (1), -- account type 0-to-public customer, 1-debit card, 2-personal passbook, 3-credit card
Sub_acct_no varchar2 (32), -- the sub-account credit card details are not found in other cases
Curr_type varchar2 (3), -- currency
Balance number, -- balance
Deposit_type varchar2 (40), -- deposit type name
From_date varchar2 (10), -- start date
End_date varchar2 (10), -- expiration date
Credit_limit number, -- credit limit
);

Create or replace type bcescde. ibps_acct_amt_table_type is table of bcescde. ibps_acct_amt_record_type;

2. Use of process input parameters
Procedure ibp_add_bill_bat_pro (in_partyno in varchar2, -- customer number
In_bill_list in ibps_acct_amt_table_type ,--
Out_retcode out varchar2, -- output:
-- 0 succeeded
-- 9 system exception
Out_message out varchar2 -- output execution result information
) Is
I number;
V_serial_no varchar2 (20 );
Begin
I: = in_bill_list.first;
Loop
V_serial_no: = ibp_gen_serial_no_pro ('1 ');
Insert into ibp_bill_tab
(Partyno,
Handflag,
Optype,
Billid,
Billflag,
Accno,
Accname,
Insert_time,
Update_time)
Values
(In_partyno,
In_bill_list (I). handflag,
'1', -- 0 modify 1 add
V_serial_no,
In_bill_list (I). billflag,
In_bill_list (I). accno,
In_bill_list (I). accname,
Sysdate,
Sysdate );

Exit when I = in_bill_list.last;
I: = in_bill_list.next (I );
End loop;

Out_retcode: = '0 ';
Out_message: = 'successful ';
Exception
When others then
Out_retcode: = '9 ';
Out_message: = '[ibp_add_bill_bat_pro] error message' | to_char (sqlcode) | '-' |
Substrb (SQL errm, 1,120 );
End ibp_add_bill_bat_pro;


3. Java call Process
Connection oconn = NULL;
Oconn = (wlconnection) getsqlmapclienttemplate (). getdatasource (). getconnection (). getvendorconnection ();
Oconn. setautocommit (false );

// Create a model
Structdescriptor structdesc = structdescriptor. createdescriptor (getschema () + ". ibp_bill_record_type", oconn );
Arraydescriptor arraydesc = arraydescriptor. createdescriptor (getschema () + ". ibp_bill_table_type", oconn );

Object [] Params = new object [billinfolist. Size ()];

// Fill in data
For (INT I = 0, size = billinfolist. Size (); I <size; I ++ ){
Billinfoindto billinfo = (billinfoindto) billinfolist. Get (I );
Bigdecimal tranamt = NULL;
Bigdecimal balance = NULL;
If (null! = Billinfo. gettranamt ()&&! "". Equals (billinfo. gettranamt ())){
Tranamt = new bigdecimal (billinfo. gettranamt ());
}
If (null! = Billinfo. getbalance ()&&! "". Equals (billinfo. getbalance ())){
Balance = new bigdecimal (billinfo. getbalance ());
}
Object [] javaobjects = {billinfo. getbillid (), billinfo. gethandflag (), billinfo. getoptype (),
Billinfo. getbillflag (), billinfo. getaccno (), billinfo. getaccname ()};
Struct = new struct (structdesc, oconn, javaobjects );
Params [I] = struct;
}
Array parray = new array (arraydesc, oconn, Params );
// Call Process
Oraclecallablestatement OCs = (oraclecallablestatement) oconn. preparecall ("{call ibp_newebank_pkg.ibp_add_bill_bat_pro (?,?,?,?)} ");
OCs. setstring (1, partyno );
OCs. setarray (2, parray );
OCs. registeroutparameter (3, types. varchar );
OCs. registeroutparameter (4, types. varchar );
Ocs.exe cute ();

String retcode = OCs. getstring (3 );
String outmessage = OCs. getstring (4 );