Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Use a shell variable value in a control file via sqlloader

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Use a shell variable value in a control file via sqlloader
# 1  
Old 01-14-2010
Use a shell variable value in a control file via sqlloader
Dear,

I must load a file in a table via sqlloader. I have also a column in this table which must contain the same value for all rows in this table. And this value is get from a shell script variable.

Can you give me a sort example on how to use a shell script variable as a paramter in a CTL file?

Thanks for your help.
See here a shortscript shel code and a CTL file example.

Code:
#!/bin/ksh
#set -x 
Var1=XE
sqlldr xe/xe@dbxe data=/test/data/test.csv control=/test/ctl_file.ctl  log=/test/log/test.log
bad=/test/test.bad

Code:
-- CTL File description

OPTIONS ( ERRORS=0, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
  CHARACTERSET WE8MSWIN1252  
  INTO TABLE XXE
  TRUNCATE
  REENABLE DISABLED_CONSTRAINTS
  
  FIELDS    (
              TT    -- must take value from variable describe Var1=XE 
              TE  POSITION (1:12) CHAR 
              TX  POSITION (13:23) CHAR 
    )

# 2  
Old 01-14-2010
one way:
you can edit the ctl file ( you must have that comment or any other unique comment to search for that particular line )

Code:
Var1=XE

sed "s/.*-- must take value from variable describe Var1=XE/              $Var1 -- must take value from variable describe Var1=XE/g" /test/ctl_file.ctl > tmp.ctl
mv tmp.ctl /test/ctl_file.ctl

sqlldr xe/xe@dbxe data=/test/data/test.csv control=/test/ctl_file.ctl  log=/test/log/test.log
bad=/test/test.bad

There must be some other way also.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Read in shell variable values from a file

Hello, I have a simple script that runs an application, # these arguments have the same value for all splits ARCH=12.11.1 BATCHES=50 EPOCHS=5000 LEARN_MODE=ONLINE LEARN_RATE=0.25 PROJ=02_BT_12.11.1.proj echo "processing split A on hex" cd A/ DATA_SET=S2A_v1_12.1.1_1... (4 Replies)
Discussion started by: LMHmedchem
4 Replies

2. UNIX for Dummies Questions & Answers

Deleting carriage control from shell script variable

I have a shell script variable called batch_id which contains the following value export BTCH_ID=`cat /TEMPDATA/jelg0100_batchid_sorted.dat` echo "BTCH_ID " = $BTCH_ID BTCH_ID = 1389428^ This variable can be 7, 8 or 9 digits long, so I must capture only the true numerical value. I am... (8 Replies)
Discussion started by: dgreene
8 Replies

3. Shell Programming and Scripting

wish to use shell variable in xml file, is it possible?

greetings, i have an executable that reads its config from an xml file. this executable needs to be started as the current user. there is a line in the xml file that contains the following: <option name="USER_ID" value="myuserid"/>the current value is an actual user id (mine) as it was... (3 Replies)
Discussion started by: crimso
3 Replies

4. Programming

eliminate duplicate rows - sqlloader

Hi , I have a data file in this format. p1 p2 p3 10 0 10 0 1000 I am using a sqlloader script to load the data into the database table.There is a unique constraint on the columns p1 and p2. So, sqlldr cannot load both the records. This eliminates duplicate records from being... (1 Reply)
Discussion started by: megha2525
1 Replies

5. Shell Programming and Scripting

Error sqlloader

Hello I am having a problem with a script. I try to run a store procedure from unix with sqlldr and shows me the following error. begin * ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "RNP.SP_ARCH_SALIDA", line 64 ORA-06512: at line 2 The sp... (1 Reply)
Discussion started by: Rodrih92
1 Replies

6. Shell Programming and Scripting

How to access variable from one file to another in shell script?

Hi, I had written shell script to access config file as input parameter. Contents of ConfFile.cfg are USER=ora PASSWORD=ora Shell script is DBConnect.ksh #!/bin/sh usage="`basename $0` <configuration_file_name>" DB_Connect() { sqlplus -s ora/ora << EOF CREATE TABLE TBL1... (10 Replies)
Discussion started by: Poonamol
10 Replies

7. Programming

How to call sqlloader from stored procedure!!! Advise

Hi , I have a dellimited .dat file and a sqlloader. I want to call the sqlloader from a oracle stored procedure. The procedure should return the result of sqlloader's execution. (3 Replies)
Discussion started by: Haque123
3 Replies

8. Shell Programming and Scripting

runing shell with java and sqlloader issue

Hello there, I'm writing a shellscript and it uses sqlloader, it works good coz load the data into the DB. But I'm runing the shell from a java program with Runtime(). It does execute the shell but the sqlloader doesn't seem to be working!! Does anybody out there knows why?? (0 Replies)
Discussion started by: ying_dav
0 Replies

9. Shell Programming and Scripting

sqlloader issue

Hi All, I am using sqlloader in my shellscript.pastimg my code below sqlldr XXXXXX/XXXXXXX@10.58.111.121:1221/XXXX control=/root/tracking/load.ctl log=sqlloaderlogs.log I have 3 questions .. 1) how can i avoid printing the sqlloader operations messages in my screen while running. 2) How... (1 Reply)
Discussion started by: subin_bala
1 Replies

10. Shell Programming and Scripting

Shell script to read file into variable

the script i am trying to write will allow my server to give itself an ip address. So far i am up to the following but i'm stuck. tracert -m 1 > traceroute.txt 1 routername (ipaddr) 2.094 ms 1.789 ms 1.243 ms i want to get ipaddr as a variable and use it to write the ifcfg-eth... (7 Replies)
Discussion started by: aspect_p
7 Replies
Login or Register to Ask a Question