?
Solved

Can a SQL* Loader CTL file accept parameter input?

Posted on 2004-07-06
16
Medium Priority
?
4,465 Views
Last Modified: 2010-06-22
A co-worker of mine asked this question, and I've researched this, and I'm pretty sure (99%) that this is not possible.  But maybe someone has figured out a way to do it?  They want to be able to change the CONSTANT value assigned to a column during the loading phase.  The other way to do it would be to load the data into the table with that column being set to NULL, then calling a SQL* Plus script and doing an UPDATE on that column for the whole table.

Thanks,
Gio
0
Comment
Question by:jaramill
  • 6
  • 5
  • 2
  • +3
16 Comments
 
LVL 8

Accepted Solution

by:
Pierrick LOUBIER earned 500 total points
ID: 11486295
Similar unsolved question
http://www.experts-exchange.com/Databases/Oracle/Q_20955052.html

You could also generate the CTL file with UTL_FILE...
0
 
LVL 13

Expert Comment

by:riazpk
ID: 11489407
There are at least two ways for doing this:

suppose you want to assigan a constant value of 50 to the column col1 of table t1. You will simply do:

(1) alter table t1 modify(col1 default 50);

This will cause a value of 50 to be assigned to col1.

(2) on before insert trigger on table t1:

if :new.col1 is null then :new.col:=50;
end if;
0
 
LVL 6

Expert Comment

by:morphman
ID: 11490422
I think SQL*Loader direct path inserts bypoass triggers, so  if you are using direct path loading, it wont work.

We need more info.

Do you need to parameter to be constant for each time you call the ctl file, or will the parameter be different for each record loaded?

If you declare the file as an external table, and load that way, you can use standard sql/plsql techniques to apply the variable for each record.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 35

Expert Comment

by:johnsone
ID: 11492893
The way I do it is to load a known constant value that cannot match existing data.  Then use a SQL*Plus script to update the column to the correct value.  I do not use NULL because you cannot use an index.  If the table is small enough a full table scan may not be as costly, as I am currently using this method with tables that contain over 6 million records.
0
 
LVL 3

Author Comment

by:jaramill
ID: 11495050
Thanks for the comments guys.  To answer "morphman's" question, YES the parameter will be different each time I call the CTL file.  Let me explain.  The DAT file that will be used by the CTL file to load will be several files with different names.  What I want to do is for each filename, parse the filename and load THAT as the constant for that particular DAT file.  I already have a way to get that filename.  Then when I load the next file (DAT), I change the constant again.

So far "ploubier's" link to an existing thread using the Unix utility "sed" seems like it could be the way to go.  Still tinkering.  My question is,...is this only available in SQL* Loader 9.2.0.4?  I'm using 8.0.6 (but loading into an 8.1.6 database).

Thanks,
Gio
0
 
LVL 6

Expert Comment

by:morphman
ID: 11495502
SED will work with any version of sqlloader as its a unix command.

just place FILENAME as a constant in the ctl file, and use sed to generate a new file to run

sed -e "s+FILENAME+$FILENAME+" original.ctl >> new.ctl

Then run sqlloader on the new CTL file...
0
 
LVL 3

Author Comment

by:jaramill
ID: 11496137
Okay here's what I have in my shell script

input=$1
echo $input
sed -e 's/COL3_VALUE/$input/' mytest2.ctl > mytest.ctl

And here's what is in the output of mytest.ctl:

LOAD DATA
APPEND INTO TABLE mytest
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
 COL1 INTEGER EXTERNAL,
 COL2 INTEGER EXTERNAL,
 COL3 CONSTANT $input
)

I can't seem to have the input actually be taken in by the SED command.

Gio
0
 
LVL 6

Expert Comment

by:morphman
ID: 11497097
Try this

input=$1
echo $input
sed -e 's/COL3_VALUE/$input/' mytest2.ctl > mytest.ctl

And here's what is in the output of mytest.ctl:

LOAD DATA
APPEND INTO TABLE mytest
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
 COL1 INTEGER EXTERNAL,
 COL2 INTEGER EXTERNAL,
 COL3 CONSTANT COL3_VALUE
)
0
 
LVL 3

Author Comment

by:jaramill
ID: 11497136
Huh??  I think you must hav cut-n-paste my text from my previous post.  I have tried that and what gets populated is

LOAD DATA
APPEND INTO TABLE mytest
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
 COL1 INTEGER EXTERNAL,
 COL2 INTEGER EXTERNAL,
 COL3 CONSTANT $input
)
0
 
LVL 6

Assisted Solution

by:morphman
morphman earned 500 total points
ID: 11497203
have you tried putting the sed command in double quotes, and using curly braces around the variable?

ie.

input=$1
echo $input
sed -e "s+COL3_VALUE+${input}+" mytest2.ctl > mytest.ctl

Which flavour of unix are u using?
0
 
LVL 3

Author Comment

by:jaramill
ID: 11497305
The flavour of Unix I'm using is:

SunOS harley 5.8 Generic_108528-22 sun4u sparc SUNW,Ultra-Enterprise

I'm logged into a "K-Shell" yet my script name is mytest.sh

When I run it, the double quotes did work in putting in the parameter, but for some reason, the file is temporarily created.  I see it in the filesystem when I do an "ls" but can't "cat" or "rm" it.  But in Windows Explorer, the filename shows up as MYTEST~1.CTL.  Strange behaviour.  Didn't need the curly braces by the way but did need the double quotes.  Also why do you use a  "+" sign?  That didn't seem to work for me.

Here's what I'm using:

input=$1
echo $input
sed -e "s/COL3_VALUE/$input/" mytest2.ctl > mytest.ctl

Gio
0
 
LVL 6

Expert Comment

by:morphman
ID: 11499237
The plus sign is just what I use and it works.

I run on HPUX11 at work, it does the same thing, and plusses work for me. Im not a UNIX expert, although we do a lot of shell scripting. I have found that different flavours of UNIX have slightly diffferent characteristics for certain programs.

Not sure why its coming up with a temp file, that sounds odd.
0
 
LVL 3

Author Comment

by:jaramill
ID: 11504666
Hey morphman, thanks for your help.  It seems Unix in all its varieties does have quirks.  It turns out I had to create a master shell script which calls two separate shell scripts.  The first one executes the sed utility to replace the constant value.  The second script then calls SQL* Loader.  Weird.  I was banging my head all day yesterday until I split them out.  Don't know why it doesn't work in one script but oh well.

Thanks for your help, and you get the points.

Gio
0
 
LVL 3

Author Comment

by:jaramill
ID: 11504694
Along with "ploubier" who also helped me out.

Thanks,
Gio
0
 

Expert Comment

by:lb2020
ID: 33047749
Hi jaramil,

I have the same issue and iam new to unix and shell scripting could you please share the code to do that

thxs,
lb
0
 

Expert Comment

by:lb2020
ID: 33047764
i need to give the file name and dynamic value to the sqlloader through unix please help me out
0

Featured Post

Technology Partners: 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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

850 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