Shell Programming and Scripting

I have a tab delimited file which has 27 character fields. The file needs to be loaded into an Oracle table. But the challenge is that everytime the file comes it may or may not have values in all 27 fields.

For example: If today's file has 5 records, three records have values in all 27 fields and two records have values in 10 fields. The rest of them are blank or empty.

Tomorrow's file will be different...I am not sure how to deal with this dynamically changing file.

I did an extensive search on the forum, but unable to find any related hits. There are hits on how to invoke SQL through UNIX.

Any help will be highly appreciated...

Thank You,
Madhu

Use SQL Loader

Thank You Ambikesh..

Our requirement is only to read this file in UNIX, invoke Oracle and load it into the specific table...

Any shell script examples for this will really help me a lot....

A shell script solution is simple enough but a better solution is to use an external table definition. This is defined in the database and has its own sqlldr like control file incorporated into the table definition. This then should give you access to whatever the file updates are as they occur throughout the day without invoking a shell script.

Whether or not same columns are provided in the file, as long as their location in the fixed width or CSV formatted flat file, you will be fine.

Thank you tmarikle...

I did not quite understand what exactly you meant by it...

Yes...It is a tab delimited file. But the problem is that if there are no values in the rest of the columns, they are not filled with spaces or tabs...

Row1: A B C D E F G H
Row2: A B C D
Row3: A B

I am absolutely not sure how I can do this...

Both sqlldr and external tables can process data in the format that you have described. I'll edit this post with some examples shortly.

Additional info:

Here is how you can define an external table using another table and a sqlldr control file to start with.

1) Create your table:

CREATE TABLE my_external_table (
my_key_no VARCHAR2(45)
,load_date DATE
,col_a VARCHAR2(30)
,col_b VARCHAR2(30)
,col_c VARCHAR2(30)
,col_d VARCHAR2(30)
,col_e VARCHAR2(30)
,col_f VARCHAR2(30)
)

2) Create a control file for sqlldr that allows for "missing" columns as per your requirement.

LOAD DATA

INTO TABLE "MY_EXTERNAL_TABLE"
APPEND

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS

(
my_key_no "SUBSTRB(:my_key_no, 1, 45)"
,load_date "TO_DATE(:load_date, 'YYYYMMDD')"
,col_a "SUBSTRB(:col_a , 1, 30)"
,col_b "SUBSTRB(:col_a , 1, 30)"
,col_c "SUBSTRB(:col_a , 1, 30)"
,col_d "SUBSTRB(:col_a , 1, 30)"
,col_e "SUBSTRB(:col_a , 1, 30)"
,col_f "SUBSTRB(:col_a , 1, 30)"
)

3) Use sqlldr to define a comperable external table definition.

sqlldr user/pwd test.ctl external_table=generate_only

4) The resulting log file contains the external table DDL and examples on how to use it.

CREATE TABLE "SYS_SQLLDR_X_EXT_MY_EXTERNAL_T"
(
"MY_KEY_NO" VARCHAR(255),
"LOAD_DATE" VARCHAR(255),
"COL_A" VARCHAR(255),
"COL_B" VARCHAR(255),
"COL_C" VARCHAR(255),
"COL_D" VARCHAR(255),
"COL_E" VARCHAR(255),
"COL_F" VARCHAR(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"MY_KEY_NO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"LOAD_DATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_A" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_B" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_C" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_D" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_E" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_F" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """
)
)
location
(
'test.dat'
)
)REJECT LIMIT UNLIMITED

At any rate, you can read up on external tables at asktom.oracle.com.

By the way, if you don't want to use an external table, you can still use sqlldr. You need a controlfile as follows:

Code:

LOAD DATA

INTO TABLE "MY_EXTERNAL_TABLE"
APPEND

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
my_key_no "SUBSTRB(:my_key_no, 1, 45)"
,load_date "TO_DATE(:load_date, 'YYYYMMDD')"
,col_a "SUBSTRB(:col_a , 1, 30)"
,col_b "SUBSTRB(:col_a , 1, 30)"
,col_c "SUBSTRB(:col_a , 1, 30)"
,col_d "SUBSTRB(:col_a , 1, 30)"
,col_e "SUBSTRB(:col_a , 1, 30)"
,col_f "SUBSTRB(:col_a , 1, 30)"
)

Then you can script your sqlldr call in Unix as follows:

Code:

sqlldr user/password control=test.ctl data=test.dat

Any column missing will be set to null. This is accompished in the "fields terminated by" statement in the control file.

Please note that this example assume CSV not tab delimited. Tab delimited use this syntax:

Code:

FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS