Introduction
Today the database is an important factor of an application.
The Database becomes more complicated day after day. In this article I want to give a solution to work
with an Oracle database has a large number of Columns.
As Oracle Limitation Documentation, we will remember in
oracle database, a table will have no more than 1000 columns.
Our solution is using a real example in our current project,
It will store Iso8583 format to Oracle Database. Someone work in Financial
Field will know Iso8583 format and purpose of it. But only see Iso8583 format
as an array of 128 String Array value, and we will store 128 value of iso
fields into a table contains 128 columns
Design and Implementation
Database design
First
of all, we will create an Oracle database has this type
<code><code>
create or replace
type ISOMESSAGE_ARRAY
as table of varchar2(1000);
Because Oracle can only support table contains no more 1000
columns so we only create an array type of 1000 varchar2 elements
The second thing we will create a TBLISO contain 128 columns
to store ISO8583 data. By running this code
CREATE TABLE "TBLISO"
( "MSGIN_ID" NUMBER(10,0),
"MSGHEADER" VARCHAR2(100 BYTE),
"BITMAP" VARCHAR2(32 BYTE),
"F2_PAN" VARCHAR2(19 BYTE),
"F3_PROCESSING_CODE" VARCHAR2(6 BYTE),
"F4_AMOUNT_TRANS" VARCHAR2(12 BYTE),
"F5_AMOUNT_SETT" VARCHAR2(12 BYTE),
"F6_AMOUNT_CARDHOLDER_BILL" VARCHAR2(12 BYTE),
"F7_TRANSMISSION_DATETIME" VARCHAR2(10 BYTE),
"F8_AMOUNT_CARDHOLDER_BILL_FEE" VARCHAR2(8 BYTE),
"F9_CONVERSION_RATE_SETT" VARCHAR2(8 BYTE),
"F10_CONVERSION_RATE_CARDBILL" VARCHAR2(8 BYTE),
"F11_SYSTEM_TRACE" VARCHAR2(6 BYTE),
"F12_LOCAL_TIME" VARCHAR2(6 BYTE),
"F13_LOCAL_DATE" VARCHAR2(4 BYTE),
"F14_DATE_EXPIRATION" VARCHAR2(4 BYTE),
"F15_DATE_SETT" VARCHAR2(4 BYTE),
"F16_DATE_CONVERSION" VARCHAR2(4 BYTE),
"F17_DATE_CAPTURE" VARCHAR2(4 BYTE),
"F18_MERCHANT_TYPE" VARCHAR2(4 BYTE),
"F19_ACQ_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F20_PRIMARY_ACC_NUM_EXT" VARCHAR2(3 BYTE),
"F21_FORWD_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F22_POS_ENTRY_MODE" VARCHAR2(3 BYTE),
"F23_CARD_SEQ_NUM" VARCHAR2(3 BYTE),
"F24_NETW_INTER_IDENTIFY" VARCHAR2(3 BYTE),
"F25_POS_CONDITION_CODE" VARCHAR2(2 BYTE),
"F26_POS_PIN_CAPTURE_CODE" VARCHAR2(2 BYTE),
"F27_AUTHO_IDEN_RESP_LENGTH" VARCHAR2(1 BYTE),
"F28_AMOUNT_TRANS_FEE" VARCHAR2(9 BYTE),
"F29_AMOUNT_SETT_FEE" VARCHAR2(9 BYTE),
"F30_AMOUNT_TRANS_PROCESS_FEE" VARCHAR2(9 BYTE),
"F31_AMOUNT_SETT_PROCESS_FEE" VARCHAR2(9 BYTE),
"F32_ACQ_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F33_FORW_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F34_PAN_EXTEND" VARCHAR2(28 BYTE),
"F35_TRACK2" VARCHAR2(37 BYTE),
"F36_TRACK3" VARCHAR2(104 BYTE),
"F37_RETRIEVAL_REF_NUM" VARCHAR2(12 BYTE),
"F38_AUTHO_IDEN_RESP" VARCHAR2(20 BYTE),
"F39_RESP_CODE" VARCHAR2(2 BYTE),
"F40_SERV_RESTRICT_CODE" VARCHAR2(3 BYTE),
"F41_CARD_ACCEPT_TERM_IDEN" VARCHAR2(20 BYTE),
"F42_CARD_ACCEPT_IDEN_CODE" VARCHAR2(15 BYTE),
"F43_CARD_ACCCEPT_NAME" VARCHAR2(40 BYTE),
"F44_ADD_RESP_DATA" VARCHAR2(25 BYTE),
"F45_TRACK1" VARCHAR2(79 BYTE),
"F46_ADD_DATA_ISO" VARCHAR2(999 BYTE),
"F47_ADD_DATA_NATIONAL" VARCHAR2(999 BYTE),
"F48_ADD_DATA_PRIVATE" VARCHAR2(999 BYTE),
"F49_CURRENCY_CODE_TRANS" VARCHAR2(3 BYTE),
"F50_CURRENCY_CODE_SETT" VARCHAR2(3 BYTE),
"F51_CURRENCY_CODE_CARDHOLD" VARCHAR2(3 BYTE),
"F52_PIN_DATA" VARCHAR2(16 BYTE),
"F53_SECUR_RELATE_CONTROL_INFO" VARCHAR2(16 BYTE),
"F54_ADD_AMOUNT" VARCHAR2(120 BYTE),
"F55_ICC_SYS_RELATE_DATA" VARCHAR2(255 BYTE),
"F56_RESERVED_ISO" VARCHAR2(999 BYTE),
"F57_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F58_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F59_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F60_ADVICE_REASON_CODE" VARCHAR2(60 BYTE),
"F61_POS_DATA" VARCHAR2(26 BYTE),
"F62_INF_DATA" VARCHAR2(50 BYTE),
"F63_NETWORK_DATA" VARCHAR2(44 BYTE),
"F64_MAC" VARCHAR2(16 BYTE),
"F65_BITMAP_EXTEND" VARCHAR2(16 BYTE),
"F66_SETT_CODE" VARCHAR2(1 BYTE),
"F67_EXTEND_PAYMENT_CODE" VARCHAR2(2 BYTE),
"F68_RECEIV_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F69_SETT_INST_COUNTRY_CODE" VARCHAR2(3 BYTE),
"F70_NETW_MANG_INFO_CODE" VARCHAR2(3 BYTE),
"F71_MESSAGE_NUM" VARCHAR2(4 BYTE),
"F72_MESSAGE_NUM_LAST" VARCHAR2(4 BYTE),
"F73_DATE_ACTION" VARCHAR2(6 BYTE),
"F74_CREDIT_NUM" VARCHAR2(10 BYTE),
"F75_CREDIT_REVRS_NUM" VARCHAR2(10 BYTE),
"F76_DEBIT_NUM" VARCHAR2(10 BYTE),
"F77_DEBIT_REVRS_NUM" VARCHAR2(10 BYTE),
"F78_TRANSFER_NUM" VARCHAR2(10 BYTE),
"F79_TRANSFER_REVRS_NUM" VARCHAR2(10 BYTE),
"F80_INQUIRIES_NUM" VARCHAR2(10 BYTE),
"F81_AUTHO_NUM" VARCHAR2(10 BYTE),
"F82_CREDIT_PROCESS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F83_CREDIT_TRANS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F84_DEBIT_PROCESS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F85_DEBIT_TRANS_FEE_AMOUNT" VARCHAR2(12 BYTE),
"F86_CREDIT_AMOUNT" VARCHAR2(16 BYTE),
"F87_CREDIT_REVRS_AMOUNT" VARCHAR2(16 BYTE),
"F88_DEBIT_AMOUNT" VARCHAR2(16 BYTE),
"F89_DEBIT_REVRS_AMOUNT" VARCHAR2(16 BYTE),
"F90_ORIGI_DATA_ELEMNT" VARCHAR2(42 BYTE),
"F91_FILE_UPDATE_CODE" VARCHAR2(1 BYTE),
"F92_FILE_SECUR_CODE" VARCHAR2(2 BYTE),
"F93_RESP_INDICATOR" VARCHAR2(5 BYTE),
"F94_SERVICE_INDICATOR" VARCHAR2(7 BYTE),
"F95_REPLACEMENT_AMOUNT" VARCHAR2(42 BYTE),
"F96_MESAGE_SECUR_CODE" VARCHAR2(16 BYTE),
"F97_AMOUNT_NET_SETT" VARCHAR2(17 BYTE),
"F98_PAYEE" VARCHAR2(25 BYTE),
"F99_SETT_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F100_RECEIVE_INST_IDEN_CODE" VARCHAR2(11 BYTE),
"F101_FILE_NAME" VARCHAR2(17 BYTE),
"F102_ACCOUNT_IDEN_1" VARCHAR2(28 BYTE),
"F103_ACCOUNT_IDEN_2" VARCHAR2(28 BYTE),
"F104_TRANS_DESCP" VARCHAR2(100 BYTE),
"F105_RESERVED_ISO" VARCHAR2(999 BYTE),
"F106_RESERVED_ISO" VARCHAR2(999 BYTE),
"F107_RESERVED_ISO" VARCHAR2(999 BYTE),
"F108_RESERVED_ISO" VARCHAR2(999 BYTE),
"F109_RESERVED_ISO" VARCHAR2(999 BYTE),
"F110_RESERVED_ISO" VARCHAR2(999 BYTE),
"F111_RESERVED_ISO" VARCHAR2(999 BYTE),
"F112_PARCELAS_DATA" VARCHAR2(248 BYTE),
"F113_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F114_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F115_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F116_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F117_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F118_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F119_RESERVED_NATIONAL" VARCHAR2(999 BYTE),
"F120_RECORD_DATA" VARCHAR2(999 BYTE),
"F121_AUTHO_AGENT_IDEN_CODE" VARCHAR2(11 BYTE),
"F122_ADD_RECORD_DATA" VARCHAR2(100 BYTE),
"F123_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F124_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F125_RESERVED_FUTURE" VARCHAR2(999 BYTE),
"F126_RESERVED_PRIVATE" VARCHAR2(999 BYTE),
"F127_PRIVATE_DATA" VARCHAR2(50 BYTE),
"F128_MAC" VARCHAR2(16 BYTE)
)
We added MSGIN_ID column as primary key of table, will use a
sequence to generate primary key each time new iso8583 data need to be inserted
Don’t
forget to create a sequence for generating primary key
CREATE SEQUENCE "SEQ_ISO" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 101 CACHE 100 NOORDER NOCYCLE ;
And the last thing we need is an store procedure to insert
data to table. There are some ways to create this store procedure. The normal
way we will create a store procedure has 128 input parameter and use it in
insert statement. But this way will take a lot of time of developer to compose
and debug if having errors. So I will use the simpler way to pass parameters to
the store-procedure: A Array input parameter solution
Let is run this code to create package header in Oracle DB
create or replace
package isopkg as
procedure insert_iso(in_isomsg IN isomessage_array);
end isopkg;
<code>
Let is run this code to create package body in Oracle DB
create or replace
package body isopkg as
procedure insert_iso(in_isomsg IN isomessage_array) AS
new_id_in_msg number(10,0);
begin
select seq_iso.nextval into new_id_in_msg from dual;
INSERT INTO "TBLiso"
VALUES
(
new_id_in_msg,
'0000',
in_isomsg(2),
in_isomsg(3),
in_isomsg(4),
in_isomsg(5),
in_isomsg(6),
in_isomsg(7),
in_isomsg(8),
in_isomsg(9),
in_isomsg(10),
in_isomsg(11),
in_isomsg(12),
in_isomsg(13),
in_isomsg(14),
in_isomsg(15),
in_isomsg(16),
in_isomsg(17),
in_isomsg(18),
in_isomsg(19),
in_isomsg(20),
in_isomsg(21),
in_isomsg(22),
in_isomsg(23),
in_isomsg(24),
in_isomsg(25),
in_isomsg(26),
in_isomsg(27),
in_isomsg(28),
in_isomsg(29),
in_isomsg(30),
in_isomsg(31),
in_isomsg(32),
in_isomsg(33),
in_isomsg(34),
in_isomsg(35),
in_isomsg(36),
in_isomsg(37),
in_isomsg(38),
in_isomsg(39),
in_isomsg(40),
in_isomsg(41),
in_isomsg(42),
in_isomsg(43),
in_isomsg(44),
in_isomsg(45),
in_isomsg(46),
in_isomsg(47),
in_isomsg(48),
in_isomsg(49),
in_isomsg(50),
in_isomsg(51),
in_isomsg(52),
in_isomsg(53),
in_isomsg(54),
in_isomsg(55),
in_isomsg(56),
in_isomsg(57),
in_isomsg(58),
in_isomsg(59),
in_isomsg(60),
in_isomsg(61),
in_isomsg(62),
in_isomsg(63),
in_isomsg(64),
in_isomsg(65),
in_isomsg(66),
in_isomsg(67),
in_isomsg(68),
in_isomsg(69),
in_isomsg(70),
in_isomsg(71),
in_isomsg(72),
in_isomsg(73),
in_isomsg(74),
in_isomsg(75),
in_isomsg(76),
in_isomsg(77),
in_isomsg(78),
in_isomsg(79),
in_isomsg(80),
in_isomsg(81),
in_isomsg(82),
in_isomsg(83),
in_isomsg(84),
in_isomsg(85),
in_isomsg(86),
in_isomsg(87),
in_isomsg(88),
in_isomsg(89),
in_isomsg(90),
in_isomsg(91),
in_isomsg(92),
in_isomsg(93),
in_isomsg(94),
in_isomsg(95),
in_isomsg(96),
in_isomsg(97),
in_isomsg(98),
in_isomsg(99),
in_isomsg(100),
in_isomsg(101),
in_isomsg(102),
in_isomsg(103),
in_isomsg(104),
in_isomsg(105),
in_isomsg(106),
in_isomsg(107),
in_isomsg(108),
in_isomsg(109),
in_isomsg(110),
in_isomsg(111),
in_isomsg(112),
in_isomsg(113),
in_isomsg(114),
in_isomsg(115),
in_isomsg(116),
in_isomsg(117),
in_isomsg(118),
in_isomsg(119),
in_isomsg(120),
in_isomsg(121),
in_isomsg(122),
in_isomsg(123),
in_isomsg(124),
in_isomsg(125),
in_isomsg(126),
in_isomsg(127),
in_isomsg(128),
in_isomsg(129)
);
end insert_iso;
end isopkg;
Implementation
Above, we are create the database with large number columns
in table TBLISO. This session we will create a Java Application to work this
database
We will use Oracle Thin Driver to work with Oracle Database.
Sample application will contain in this session or you can download it above.
We will have 2 configuration file located in cfg folder
Dbcdg.txt:
contains database connection information
jdbc:oracle:thin:@[iphost]:[port]:[sid]
user
pass
And
iso8583data.txt store Iso 8583 data. Its structure will be shown as below
0: 420
1: F2B900018E80C4000000000004000000
2: 9876543210123456
3: 351000
4: 000000000000
7: 0418055231
9: 20000000
11: 300422
12: 130527
13: 0418
16: 0418
32: 998866
33: 704001
37: 000000224003
38: 012345
39: 05
41: 00001100
49: 704
50: 840
54: 0000000000000000000000000000000000000000
102: 00000000000000000000
As I refer above, Iso8583 format has 128 data fields. Each
row will set one value for a field. Above “: “ string is field index and behind
it is Iso Field value. You can add/remove field from this file but always refer
to ISO Format document to ensure Data is good to store to correspondence column
in table
Refer http://en.wikipedia.org/wiki/ISO_8583
to make good data range for Iso Fields
Now
we start “coding”.
Using the code
Create
class FileReader in Java
package iso8583db;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.util.logging.Level;
import java.util.logging.Logger;
public class FileReader {
private String filePath;
private String[] lineData;
public FileReader() {
filePath = "";
lineData = new String[0];
}
public void setFilePath(String filePath) {
this.filePath = filePath;
loadData();
}
public String[] getLineData() {
return lineData;
}
private void loadData() {
try {
FileInputStream fstream = new FileInputStream(filePath);
DataInputStream in = new DataInputStream(fstream);
BufferedReader br = new BufferedReader(new InputStreamReader(in));
lineData=new String[getLines(new File(filePath))];
int iLine=0;
String iData="";
while ((iData = br.readLine()) != null) {
System.out.println(String.format("Line %s: %s", iLine,iData));
lineData[iLine]= iData;
iLine++;
}
in.close();
} catch (Exception e) {
System.err.println("Error: " + e.getMessage());
}
}
public String[] toIsoArray() {
String[] isoValueArray = new String[129];
for (int i = 0; i < 129; i++) {
isoValueArray[i] = "";
}
if (lineData.length>0)
{
for(int iField=0;iField<lineData.length;iField++)
{
String isoField[]=lineData[iField].split(": ");
isoValueArray[Integer.parseInt(isoField[0])]=isoField[1];
}
}
return isoValueArray;
}
private int getLines(File aFile) {
LineNumberReader reader = null;
try {
reader = new LineNumberReader(new java.io.FileReader(aFile));
while ((reader.readLine()) != null);
return reader.getLineNumber();
} catch (Exception ex) {
return -1;
} finally {
if (reader != null) {
try {
reader.close();
} catch (IOException ex) {
Logger.getLogger(FileReader.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}Create class dbLib
<span style="line-height: 115%; font-family: "Calibri","sans-serif"; font-size: 11pt;"></span>
package iso8583db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class dbLib {
public static boolean insertIsoMsg(Connection cnn, String[] datainput)
{
CallableStatement proc = null;
boolean flag = true;
try{
String temp = "{ call isopkg.insert_iso(?)}";
proc = cnn.prepareCall(temp);
ArrayDescriptor para2Des = new ArrayDescriptor("ISOMESSAGE_ARRAY", cnn);
ARRAY para2 = new ARRAY(para2Des,cnn,datainput);
proc.setArray(1,para2);
proc.execute();
System.out.println("Insert to DB successfully");
}
catch(Exception ex)
{
flag=false;
System.out.println("Cannot insert to DB");
}
finally
{
if (proc != null){
try {
proc.close();
} catch (SQLException se) {
}
}
}
return flag;
}
} And
in main function we of project we will add this code
public static void main(String[] args) {
FileReader dbCfg = new FileReader();
dbCfg.setFilePath("cfg/dbcdg.txt");
Connection conn=null;
String driver = "oracle.jdbc.driver.OracleDriver";
try {
Class.forName(driver);
conn = DriverManager.getConnection(dbCfg.getLineData()[0], dbCfg.getLineData()[1], dbCfg.getLineData()[2]);
} catch (Exception ex) {
Logger.getLogger(Iso8583DB.class.getName()).log(Level.SEVERE, null, ex);
}
while (true) {
FileReader isoData = new FileReader();
isoData.setFilePath("cfg/iso8583data.txt");
dbLib.insertIsoMsg(conn, isoData.toIsoArray());
try {
Thread.sleep(1000);
} catch (InterruptedException ex) {
Logger.getLogger(Iso8583DB.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
I use a loop and Sleep method, when application is
running we can edit data in file iso8583data to apply new change to database!
Conclusion
This
is a simple way to work with a larger number of columns in Oracle. It will make
the code more brighten and easier to debug and develop. Hope it will be meaningful
with you!
