OraFAQ Forum: Server Utilities » input variable in sqlloader ctl

Home » RDBMS Server » Server Utilities » input variable in sqlloader ctl (oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

input variable in sqlloader ctl [message #473379]

Fri, 27 August 2010 11:06

massocchi
Messages: 9
Registered: August 2010

Junior Member

I want to insert an input value in datalet
Have ideas ?
thanks gabriele

OPTIONS (SKIP=4)
load data
INFILE 'c:\temp\EnergyAneg.csv'
APPEND INTO TABLE UTE.ASM1_ARCADIAANEG
FIELDS TERMINATED BY ','
TRAILING NULLCOLS

(datalet filler, ----------------

matcon "substr(:MATRICON,4,10)" ,
MATRICON char,
TOT DECIMAL EXTERNAL(11) "to_number(:TOT,'99999999.999')" ,
L1 DECIMAL EXTERNAL(11) "to_number(:L1,'99999999.999')" ,
C1 DECIMAL EXTERNAL(11) "to_number(:C1,'99999999.999')" ,
L2 DECIMAL EXTERNAL(11) "to_number(:L2,'99999999.999')" ,
C2 DECIMAL EXTERNAL(11) "to_number(:C2,'99999999.999')" ,
L3 DECIMAL EXTERNAL(11) "to_number(:L3,'99999999.999')" ,
C3 DECIMAL EXTERNAL(11) "to_number(:C3,'99999999.999')" ,
L4 DECIMAL EXTERNAL(11) "to_number(:L4,'99999999.999')" ,
C4 DECIMAL EXTERNAL(11) "to_number(:C4,'99999999.999')" )

Report message to a moderator

Re: input variable in sqlloader ctl [message #473380 is a reply to message #473379]

Fri, 27 August 2010 11:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

when all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch03.htm#1656

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
I am not clear on what ":TOT" & similar are supposed to be, but SQL*loader does not utilize any type of variables, bind or otherwise

Report message to a moderator

Re: input variable in sqlloader ctl [message #473381 is a reply to message #473379]

Fri, 27 August 2010 11:17

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I want to insert an input value in datalet

datalet CONSTANT '#' and replace # by the variable value before loading in your shell script.

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473533 is a reply to message #473380]

Mon, 30 August 2010 01:41

massocchi
Messages: 9
Registered: August 2010

Junior Member

I have read your link but I don't understand how pass a value es: 31-7-2010 to datalet.
The value changes at each execution.
thanks
gabriele

Report message to a moderator

Re: input variable in sqlloader ctl [message #473542 is a reply to message #473533]

Mon, 30 August 2010 02:31

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And did you read what I posted?

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473580 is a reply to message #473542]

Mon, 30 August 2010 07:47

massocchi
Messages: 9
Registered: August 2010

Junior Member

I read your solution but requires me to do a program to replace the # character for each run with the date that I enter.
If it were possible acqusire directly the value of the variable would be more elegant.
thanks
gabriele

Report message to a moderator

Re: input variable in sqlloader ctl [message #473582 is a reply to message #473580]

Mon, 30 August 2010 08:10

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is NOT possible.
It is EASY in shell.
And awnyway you have to get the value from somewhere and wall SQL*Loader with it what is the difference with calling a shell script passing this value, script which call SQL*Lodaer? Explain.

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473870 is a reply to message #473582]

Wed, 01 September 2010 05:16

massocchi
Messages: 9
Registered: August 2010

Junior Member

the script is:
OPTIONS (SKIP=4)
load data
INFILE 'c:\temp\EnergyAneg.csv'
APPEND INTO TABLE UTE.ASM_ARCADIAANEG
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(datalet CONSTANT 'to_date('31/07/2010','DD/MM/YYYY')',
matcon "substr(:MATRICON,4,10)" ,
MATRICON char,
TOT DECIMAL EXTERNAL(11) "to_number(:TOT,'99999999.999')" ,
L1 DECIMAL EXTERNAL(11) "to_number(:L1,'99999999.999')" ,
C1 DECIMAL EXTERNAL(11) "to_number(:C1,'99999999.999')" ,
L2 DECIMAL EXTERNAL(11) "to_number(:L2,'99999999.999')" ,
C2 DECIMAL EXTERNAL(11) "to_number(:C2,'99999999.999')" ,
L3 DECIMAL EXTERNAL(11) "to_number(:L3,'99999999.999')" ,
C3 DECIMAL EXTERNAL(11) "to_number(:C3,'99999999.999')" ,
L4 DECIMAL EXTERNAL(11) "to_number(:L4,'99999999.999')" ,
C4 DECIMAL EXTERNAL(11) "to_number(:C4,'99999999.999')" )

if replace with '31/07/2010' i have this log error :

SQL*Loader: Release 9.2.0.8.0 - Production on Mer Set 1 12:10:07 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL*Loader-350: Errore di sintassi in riga 7.
Previsti "," o ")", trovato "31".
(datalet CONSTANT 'to_date('31/07/2010','DD/MM/YYYY')',

have idea ?
thanks

Report message to a moderator

Re: input variable in sqlloader ctl [message #473874 is a reply to message #473870]

Wed, 01 September 2010 05:43

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is NOT a script, this is a control file for SQL*Loader. How do you call the loader, you always type the command on your keyboard?

For your error, use " and not ' to enclose the expression like you do for the other ones.

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473882 is a reply to message #473874]

Wed, 01 September 2010 06:58

massocchi
Messages: 9
Registered: August 2010

Junior Member

sorry.
The problem is this,I have file:
CountAneg,textbox34,textbox36
22116,2010,7

Nome_Cabina,SerialNumber_CCS,SerialNumber_Lennt,Tot_Aneg,Fascia_T1,Consumo_T1,Fascia_T2,Consumo_T2,Fascia_T3,Consumo_T3,Fascia_T4,Con sumo_T4
Cabina Virtuale,0508530013028,0506400211865,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506400214525,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506420301572,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506500039772,0,0,0,0,0,0,0,0,0
.....
and this table

CREATE TABLE ASM_ARCADIAANEG
(
DATALET DATE,
MATCON NUMBER(10),
MATRICON VARCHAR2(25 BYTE) NOT NULL,
TOT NUMBER(11,3),
L1 NUMBER(11,3),
C1 NUMBER(11,3),
L2 NUMBER(11,3),
C2 NUMBER(11,3),
L3 NUMBER(11,3),
C3 NUMBER(11,3),
L4 NUMBER(11,3),
C4 NUMBER(11,3)
)

in datalet of this table I want insert the last day of the mouth example 31/07/2010 for 2010,7 present in second rows of the file. The others data is OK.
help me
thanks

Report message to a moderator

Re: input variable in sqlloader ctl [message #473888 is a reply to message #473882]

Wed, 01 September 2010 08:02

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

That still isn't the script you use.
You still haven't posted your operating system.
You still haven't formatted your data.

So at the moment it's impossible to help you.

One possible solution would be Unix shell scripting to extract the values and re-format them before you run SQL*Loader.

Report message to a moderator

Re: input variable in sqlloader ctl [message #473889 is a reply to message #473888]

Wed, 01 September 2010 08:07

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ir seems OP is unable to read our answers and post what he is actually doing ALWAYS hide something and waste our time.
Or maybe he is reading the first file each time and see what is the value and then interactively call SQL*Loader each time, maybe, who knows...

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473895 is a reply to message #473889]

Wed, 01 September 2010 08:36

massocchi
Messages: 9
Registered: August 2010

Junior Member

I will not hide anything.
The situation here is complete. I hope
import this file into table ASM_ARCADIAANEG:

CountAneg,textbox34,textbox36
22116,2010,7

Nome_Cabina,SerialNumber_CCS,SerialNumber_Lennt,Tot_Aneg,Fascia_T1,Consumo_T1,Fascia_T2,Consumo_T2,Fascia_T3,Consumo_T3,Fascia_T4,Con sumo_T4
Cabina Virtuale,0508530013028,0506400211865,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506400214525,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506420301572,0,0,0,0,0,0,0,0,0
Cabina Virtuale,0508530013028,0506500039772,0,0,0,0,0,0,0,0,0
.....

TABLE ASM_ARCADIAANEG
(
DATALET DATE,
MATCON NUMBER(10),
MATRICON VARCHAR2(25 BYTE) NOT NULL,
TOT NUMBER(11,3),
L1 NUMBER(11,3),
C1 NUMBER(11,3),
L2 NUMBER(11,3),
C2 NUMBER(11,3),
L3 NUMBER(11,3),
C3 NUMBER(11,3),
L4 NUMBER(11,3),
C4 NUMBER(11,3)
)

sqlloader current control file:

OPTIONS (SKIP=4)
load data
INFILE 'c:\temp\EnergyAneg.csv'
APPEND INTO TABLE UTE.ASM_ARCADIAANEG
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(datalet FILLER,
matcon "substr(:MATRICON,4,10)" ,
MATRICON char,
TOT DECIMAL EXTERNAL(11) "to_number(:TOT,'99999999.999')" ,
L1 DECIMAL EXTERNAL(11) "to_number(:L1,'99999999.999')" ,
C1 DECIMAL EXTERNAL(11) "to_number(:C1,'99999999.999')" ,
L2 DECIMAL EXTERNAL(11) "to_number(:L2,'99999999.999')" ,
C2 DECIMAL EXTERNAL(11) "to_number(:C2,'99999999.999')" ,
L3 DECIMAL EXTERNAL(11) "to_number(:L3,'99999999.999')" ,
C3 DECIMAL EXTERNAL(11) "to_number(:C3,'99999999.999')" ,
L4 DECIMAL EXTERNAL(11) "to_number(:L4,'99999999.999')" ,
C4 DECIMAL EXTERNAL(11) "to_number(:C4,'99999999.999')" )

I would like to import the corresponding day in the month-end reading the data present in the second row of the example file "2010.7" then 31/07/2010.
sorry if you do lose time.
thanks

Report message to a moderator

Re: input variable in sqlloader ctl [message #473896 is a reply to message #473895]

Wed, 01 September 2010 08:40

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

And you still haven't answered the basic question of how you call sqlloader in the first place.

Report message to a moderator

Re: input variable in sqlloader ctl [message #473898 is a reply to message #473896]

Wed, 01 September 2010 08:42

massocchi
Messages: 9
Registered: August 2010

Junior Member

sqlldr control=c:\temp\neg.ctl userid=xxxxxx/zzzzz@yyyyyyyy log=c:\temp\neg.log Bad=c:\temp\neg.bad

Report message to a moderator

Re: input variable in sqlloader ctl [message #473900 is a reply to message #473896]

Wed, 01 September 2010 08:47

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

You still haven't mentioned how you call SQL*Loader (Do you type that line you posted every time you want to run it?)
You still haven't formatted it, so that we can actually see how the data looks exactly.
You still haven't mentioned our OS. Although the c:\ in INFILE might be a hint, there are vastly different scripting capabilities between NT4 and Windows 7.

Perhaps have a look at using VBScript to parse the file before starting SQL*Loader on the prepared data.

Report message to a moderator

Re: input variable in sqlloader ctl [message #473915 is a reply to message #473900]

Wed, 01 September 2010 09:22

massocchi
Messages: 9
Registered: August 2010

Junior Member

SQL*Loader: Release 9.2.0.8.0
OS winXP
if it were possible I would not preformat file

Report message to a moderator

Re: input variable in sqlloader ctl [message #473935 is a reply to message #473915]

Wed, 01 September 2010 10:14

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

if it were possible I would not preformat file

If it were directly possible we would ALREADY give you the solution don't you think?
As I said since my first answer, it is easy to do it in shell (or cmd for you) script.

Quote:

sqlldr control=c:\temp\neg.ctl userid=xxxxxx/zzzzz@yyyyyyyy log=c:\temp\neg.log Bad=c:\temp\neg.bad

And you ALWAYS type this on your keyboard each time you want to execute it?

Regards
Michel

Report message to a moderator

Re: input variable in sqlloader ctl [message #473956 is a reply to message #473935]

Wed, 01 September 2010 11:03

massocchi
Messages: 9
Registered: August 2010

Junior Member

I overcame this problem by having a sql update script datalet.
Thanks for your patience and given me excuse for my English
thanks
Gabriele

Report message to a moderator

Re: input variable in sqlloader ctl [message #473960 is a reply to message #473956]

Wed, 01 September 2010 11:07

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem is NOT your english it is your lack or reluctance to post the actual issue in its complete form.

Regards
Michel

Report message to a moderator

Previous Topic:	SQL loader
Next Topic:	Reading from Oracle Dump

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 01:47:08 CST 2025