• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Consultation on dynamic control file, Oracle SQLLDR

Hi, I'm trying to create control files, to use the tool SQLLDR in dynamic form.
I have in $TEMP this generic.ctl control file:
LOAD DATA
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
FILENAME CONSTANT ":FILE"
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )

Open in new window

and these data files, In $TRANSFER:
Transfer_data.20141118194103
Transfer_data.20141118194132

Open in new window

This is my script ejecuto.sh:
#
#! /bin/ksh
#
echo "Ejecuto"
TRANSFER=/xahome/xa21/CAP/TRANSFER
LOGS=/xahome/xa21/CAP/LOGS
SCRIPTS=/xahome/xa21/CAP/SCRIPTS
CORRECTOS=/xahome/xa21/CAP/CORRECTOS
BAD=/xahome/xa21/CAP/BAD
DISCARD=/xahome/xa21/CAP/DISCARD
TEMP=/xahome/xa21/CAP/TEMP

rm TEMP/Transfer_data.201411181941*
echo "Borro control files anteriores"

rm TRASNFER/Transfer_data*.log
echo "Borro logs anteriores"

cd $TRANSFER

FILES=`ls Transfer_data*`
CTL=$TEMP/generic.CTL

for f in $FILES
do
   cat $CTL| sed 's|":FILE"|"$f"|g' > $TEMP/$f.ctl
 
     # sqlldr epe/epe@xa21 control=$TEMP/$f.ctl data=$TRANSFER/$f
 
     done

Open in new window

When I executed the script, the outputs are generated OK:
TEMP/Transfer_data.20141118194103.ctl
TEMP/Transfer_data.20141118194132.ctl

Open in new window

but with the line:
FILENAME CONSTANT "$f" 

Open in new window

when I search:
FILENAME CONSTANT Transfer_data.20141118194103 

Open in new window

like this:
LOAD DATA
APPEND
INTO TABLE "INT_OPERATIONS"
FIELDS TERMINATED BY '!'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
FILENAME CONSTANT "$f"
(
SCADA_ID CHAR,
SCADA_SISTEMA CHAR,
SCADA_OPERATION_CLASS CHAR,
SCADA_OPERATION_SUBCLASS CHAR,
SCADA_OPERATION_DATE ,
SCADA_DATE_OFFSET ,
SCADA_OPERATION_TIMESTAMP "TO_TIMESTAMP(RPAD(SUBSTR(:SCADA_OPERATION_TIMESTAMP,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_PROCESS_STATUS ,
CERTA_PROCESS_DATE "TO_TIMESTAMP(RPAD(SUBSTR(:CERTA_PROCESS_DATE,1,21),24,'0'),'dd/mm/yyyy hh24:mi:ss.ff)')",
CERTA_OPERATION_ID ,
CERTA_NOTES CHAR,
INT_ID
 )

Open in new window

Then execute sqlldr line, now disabled

Is it okay to try this with the "sed" command?, the reason for failure is the shell?
Should I escape variables?, You know another way of how to do this?

Thankyou for any kind of help.
Regards
0
carlino70
Asked:
carlino70
  • 4
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
>>sed 's|":FILE"|"$f"|g'

My Unix is really old and rusty and I don't have it around to test but I don't remember sed using the '|' characters.

I've always seen it with '/'.

Maybe try:
sed 's/":FILE"/"$f"/g'

The ':' also looks strange to me.
0
 
carlino70Author Commented:
I read that you can use differents separators.
I tried with your idea:
sed 's/":FILE"/"$f"/g'

Open in new window

, but doesn't work. Show errors like:
sed: -e expression #1, char 3: unterminated `s' command
sed: -e expression #1, char 3: unterminated `s' command

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Again, going from old memory try this:

sed 's/:FILE/$f/g'
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
carlino70Author Commented:
The execution is correct, but the results are the same that the beginning.
The control files are generated, but with the line:
FILENAME CONSTANT "$f"

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Quick Google, try this:

sed "s/:FILE/$f/g"
0
 
carlino70Author Commented:
Yes, It works slightwv.

Now, I have to fight with sqlldr execution, in other question maybe

Excellent!
0
 
slightwv (䄆 Netminder) Commented:
Sorry it took so many attempts.  I miss my Unix days!!!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now