Home » RDBMS Server » Server Utilities » input variable in sqlloader ctl (oracle 9i)
icon5.gif  input variable in sqlloader ctl [message #473379] Fri, 27 August 2010 11:06 Go to next message
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')" )
Re: input variable in sqlloader ctl [message #473380 is a reply to message #473379] Fri, 27 August 2010 11:12 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
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
Re: input variable in sqlloader ctl [message #473381 is a reply to message #473379] Fri, 27 August 2010 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: input variable in sqlloader ctl [message #473533 is a reply to message #473380] Mon, 30 August 2010 01:41 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473542 is a reply to message #473533] Mon, 30 August 2010 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you read what I posted?

Regards
Michel
Re: input variable in sqlloader ctl [message #473580 is a reply to message #473542] Mon, 30 August 2010 07:47 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473582 is a reply to message #473580] Mon, 30 August 2010 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: input variable in sqlloader ctl [message #473870 is a reply to message #473582] Wed, 01 September 2010 05:16 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473874 is a reply to message #473870] Wed, 01 September 2010 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: input variable in sqlloader ctl [message #473882 is a reply to message #473874] Wed, 01 September 2010 06:58 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473888 is a reply to message #473882] Wed, 01 September 2010 08:02 Go to previous messageGo to next message
ThomasG
Messages: 3099
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.
Re: input variable in sqlloader ctl [message #473889 is a reply to message #473888] Wed, 01 September 2010 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: input variable in sqlloader ctl [message #473895 is a reply to message #473889] Wed, 01 September 2010 08:36 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473896 is a reply to message #473895] Wed, 01 September 2010 08:40 Go to previous messageGo to next message
cookiemonster
Messages: 10960
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.
Re: input variable in sqlloader ctl [message #473898 is a reply to message #473896] Wed, 01 September 2010 08:42 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473900 is a reply to message #473896] Wed, 01 September 2010 08:47 Go to previous messageGo to next message
ThomasG
Messages: 3099
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.
Re: input variable in sqlloader ctl [message #473915 is a reply to message #473900] Wed, 01 September 2010 09:22 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473935 is a reply to message #473915] Wed, 01 September 2010 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: input variable in sqlloader ctl [message #473956 is a reply to message #473935] Wed, 01 September 2010 11:03 Go to previous messageGo to next message
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
Re: input variable in sqlloader ctl [message #473960 is a reply to message #473956] Wed, 01 September 2010 11:07 Go to previous message
Michel Cadot
Messages: 59105
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: SQL loader
Next Topic: Reading from Oracle Dump
Goto Forum:
  


Current Time: Wed Sep 17 06:54:12 CDT 2014

Total time taken to generate the page: 0.15749 seconds