create SQL statement from flat file using shell script.

[devendrap]

Hi,

I would like to create SQL statements from flat file.

Flat file has follwing values:
Plant Date
AB1 2005-10-10
AC1 2004-10-10

Shell script has following variables:
set type01 = "xxx"

SQL should be:
Insert into qa set date01 = 2005-10-10(from flat file), plant01=AB1(from flat file) where type01 = xxx (shell variable $type01) ;
Insert into qa set date01 = 2004-10-10,plant01=AC1 where type01 = xxx ;

I am not able to combine awk with shell variable.

 

[PHV]

A starting point:
#!/bin/ksh
type01="xxx"
while read plant date junk
do
[ "$plant" = "Plant" ] && continue
echo "Insert into qa set date01 = $date, plant01=$plant where type01 = $type01;"
done < /path/to/flatfile > /path/to/insert2qa.sql

I am not able to combine awk with shell variable
In the awk man page look at the -v option.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[devendrap]

Your code is working like a magic

Thanks a Ton!

 

[sweetleaf]

Hi,

Does anyone know if data in a flat file be:

1) indexed (to speed up lookups)
2) queried using SQL or another data interrogation language
for reporting use

Thanks in advance!

 

[SamBones]

No, that's what the database is for.

Hope this helps.

 

[devendrap]

${ORACLE_HOME}/bin/sqlplus -s $USER_DW_ADMIN/$PWD_DW_ADMIN << EOF
set colsep ''
set pagesize 0
set heading off
set feedback off
set echo off
set arraysize 5000
set termout off
SET WRAP OFF
spool $DW_LST/temp11

while read plant TS_date junk
do
[ "$plant" = "Plant" ] && continue
@${DW_CTL}/Temp_check.sql
done < $DW_LST/temp_check.dat > $DW_LST/temp12

spool off
EOF

Above loop is not working, It is generating error for plant not found. Can you please help?

Thanks,
Dave

 

[PHV]

Are you sure that sqlplus is capable of executing shell scripts ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244