Solved

Consultation on dynamic control file, Oracle SQLLDR

Posted on 2014-11-20
7
389 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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.

734 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