Solved

Consultation on dynamic control file, Oracle SQLLDR

Posted on 2014-11-20
7
386 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle date format checking 7 27
Fill Date time Field 12 25
minium over 4 numeric columns for each row in oracle 2 29
Oracle cursor lifecycle inside procedure. 2 13
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

821 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