populate database table with the the contents of flat file.

[tekpr00]

Hello All,

In a Korn Shell,

I am trying to populate database table with the the contents of flat file.
Here is the sample of my flat file and its contents: sample_file.txt

- source: "\"GRP\".\"GEC_CLAIM_EVENT_SRI_PROCESS\""
target: "\"GRP\".\"GEC_CLAIM_EVENT_SRI_PROCESS\""
rsid: c0a8a91c-26-0
flags: 0
- source: "\"GRP\".\"GPB_BONUS_PMT_SMMRY\""
target: "\"GRP\".\"GPB_BONUS_PMT_SMMRY\""
rsid: c0a8a91c-26-0
flags: 0
- source: "\"SIC\".\"CCP_ADP_DETAIL\""
target: "\"SIC\".\"CCP_ADP_DETAIL\""
rsid: c0a8a91c-26-0
flags: 0

Here is the intended structure of the population of the destination table. Note that the table already has the column headings:

Here is the attempt:
#!/bin/ksh

USER=splex
PASS=splexpassword
DATABASE=mydbname

sqlplus -s /nolog << EOF
conn ${USER}/${PASS}@${DATABASE}
for i in `cat file.txt`
do
col1=`cat $i | awk -F ',' '{print $1}'`
col2=`cat $i | awk -F ',' '{print $2}'`
col3=`cat $i | awk -F ',' '{print $3}'`
col4=`cat $i | awk -F ',' '{print $4}'`
col5=`cat $i | awk -F ',' '{print $5}'`
col6=`cat $i | awk -F ',' '{print $6}'`
col7=`cat $i | awk -F ',' '{print $7}'`

INSERT INTO TEST_TAB
(COLUMN1, COLUMN2.....................)
VALUES
($col1, $col2, $col3,.................)
commit;
done
--doing other operations in the database after the aoove
set pagesize 0 linesize 4000 feedback off trimspool on

spool rowcount.txt;
select source_name from splex.count_match_vw;
spool off;

EOF

However the above is not helping.
Please Help.

 

[SamBones]

The best tool for that job is Oracle's SQL Loader. You'll need to learn it's commands and syntax, but it's by far the best way to load files into tables. In the long run it will be much easier to maintain than a custom shell script. Plus it's much faster when the files to load get bigger.

You might need to reformat your input (one line per record) and get rid of some of the noise (remove the quoted back-slashes), but loading flat files is a simple task for SQL Loader. It's what it was made for.

A very rough untested example...

Here's the data file...

GRP.GEC_CLAIM_EVENT_SRI_PROCESS,GRP.GEC_CLAIM_EVENT_SRI_PROCESS,c0a8a91c-26-0,0
GRP.GPB_BONUS_PMT_SMMRY,GRP.GPB_BONUS_PMT_SMMRY,c0a8a91c-26-0,0
SIC.CCP_ADP_DETAIL,SIC.CCP_ADP_DETAIL,c0a8a91c-26-0,0

Here's a parameter file for our login...

userid=splex/splexpassword

Here's a VERY simple control file for SQL Loader...

load data
 infile '/path/to/files/sample_file.txt'
 append
 into table test_tab
 fields terminated by ","
 ( source_name, target_name, routing_number, flag )

And a command to do the load...

sqlldr parfile=/path/to/files/parfile.dat control=/path/to/files/sqlldr-append.ctl

You can even have it send bad records to a separate output file so you can handle them as exceptions. Just Google "Oracle SQL Loader examples" and you'll get all kinds of things. It's pretty powerful and a good tool to know how to use.

 

[johnherman]

Agree with SamBones, SQL Loader is much better for loading files. Faster, too.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[tekpr00]

Thanks fellas, I am familiar with sql loader and datapump as well.
I thought there is a one line awk command that i can use to load this data.

 

[SamBones]

No [tt]awk[/tt] one liners for this one. [tt]awk[/tt] doesn't know how to talk to an Oracle database.

You can do it with a shell script. The example you give in your post needs work, but it's pointed in the right direction. I wouldn't personally spend time developing it though when you actually have a tool created for loading files into an Oracle database available.