Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Consultation on dynamic control file, Oracle SQLLDR

Posted on 2014-11-20
7
Medium Priority
?
403 Views
Last Modified: 2014-11-20
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
Comment
Question by:carlino70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40455312
>>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
 

Author Comment

by:carlino70
ID: 40455401
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40455427
Again, going from old memory try this:

sed 's/:FILE/$f/g'
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:carlino70
ID: 40455463
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40455476
Quick Google, try this:

sed "s/:FILE/$f/g"
0
 

Author Comment

by:carlino70
ID: 40455537
Yes, It works slightwv.

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

Excellent!
0
 
LVL 77

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question