Home » RDBMS Server » Server Utilities » sqlldr - datatype
sqlldr - datatype [message #540841] Thu, 26 January 2012 03:05 Go to next message
nago
Messages: 10
Registered: January 2012
Junior Member
hi,

there is no problem as such. But i just want to make sure that i am in right direction. please suggest me.

my oracle table having 2 fields.
filed1 VARCHAR(500)
field2 NUMBER.

i load data to this table from a file using sqlldr.

what is the proper data type should i use in control-file for both the fields.? could you suggest ?


rightnow, i dont mention any datatye in ctl file which is working fine with given dataset.

thank you
nago
Re: sqlldr - datatype [message #540845 is a reply to message #540841] Thu, 26 January 2012 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66803
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CHAR(500) and CHAR

Regards
Michel

[Updated on: Thu, 26 January 2012 03:23]

Report message to a moderator

Re: sqlldr - datatype [message #540846 is a reply to message #540845] Thu, 26 January 2012 03:30 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
i think, CHAR is the default one for SQLLDR. am i right ?

-nago
Re: sqlldr - datatype [message #540849 is a reply to message #540846] Thu, 26 January 2012 04:40 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
how should i ignore the entire record if any of the column having null values ?
i want sqlldr to ignore that record and proceed with rest of records into the tables.

-nago
Re: sqlldr - datatype [message #540857 is a reply to message #540849] Thu, 26 January 2012 05:37 Go to previous messageGo to next message
nago
Messages: 10
Registered: January 2012
Junior Member
ERROR=9999999
this is the solution.

-nago
Re: sqlldr - datatype [message #540943 is a reply to message #540841] Thu, 26 January 2012 11:48 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
nago wrote on Thu, 26 January 2012 01:05
hi,

there is no problem as such. But i just want to make sure that i am in right direction. please suggest me.

my oracle table having 2 fields.
filed1 VARCHAR(500)
field2 NUMBER.

i load data to this table from a file using sqlldr.

what is the proper data type should i use in control-file for both the fields.? could you suggest ?


rightnow, i dont mention any datatye in ctl file which is working fine with given dataset.

thank you
nago


In most cases, Oracle will attempt to match the data to the data type of the column that you are loading into and do an implicit datatype conversion. However, if you do not specify a length, the default is 255. So, for your first field, you need CHAR(500). For the second field, if you want to be more exact, then you could use FLOAT EXTERNAL. It might be slightly more efficient with big loads.


Re: sqlldr - datatype [message #540945 is a reply to message #540849] Thu, 26 January 2012 12:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
nago wrote on Thu, 26 January 2012 02:40
how should i ignore the entire record if any of the column having null values ?
i want sqlldr to ignore that record and proceed with rest of records into the tables.

-nago


Use when field1 != '' and field2 != '' after your into table clause. Rejected rows will go to the discard file if there is one, so make sure you set the discardmax high enough.

Alternatively, you can put not null constraints on your fields and set the errors to a number high enough that it will not be exceeded. Rejected rows will go to the bad file if there is one.




[Updated on: Thu, 26 January 2012 12:10]

Report message to a moderator

Re: sqlldr - datatype [message #547133 is a reply to message #540945] Wed, 12 March 2008 13:11 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Hi all,
I'll continue upon this discussion because my problem is likely the same as this one.
I have a performance issue : In the .ctl file, when I don't precise the datatype for varchar2 column, time for loading the data is acceptable but I have some records that are rejected (because, I suppose, that they exceed 255 characters - As Barbara precised it- ).

In the other hand, when I precise datatype (say char(500)) all records are loaded by loading time is multiplied by a factor of three.

So for 1500000 rows
Don't precise datatype -> 3:02 minutes -> about 20 rejected records.
Precise datatype -> 10:50 minutes -> No row rejected.

Do I have to deal with this or is there any better recommendation ?

(We are going to change the DB character set from ISOP1 to UTF8 to deal with arabic characters. So we have to write all the tables is CSV file and reload them using SQL*Loader to the UTF8 DB).


Thanks in advance, and waiting especially for Barbara suggestions Smile

Amine

Re: sqlldr - datatype [message #547134 is a reply to message #547133] Wed, 12 March 2008 13:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Can you post some sample data, control file, and table structure?
Re: sqlldr - datatype [message #547135 is a reply to message #540945] Wed, 12 March 2008 13:36 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Sample Data :
100;"Amine\n\n\n";
101;"Enfant";
102;"sdfds\ndjgzetrekjtgehjgete   \n ""isnot"" for";


Sample control file :
load data
characterset AR8MSWIN1256
infile 'F_ENF.dat'
badfile 'F_ENF.bad' 
into table F_ENF
TRUNCATE
fields terminated by X'3b' optionally enclosed by X'22' 
(
ID_ENF		,
NOM_ENF		"replace(:NOM_ENF,'\n',chr(10))"
)


Table structure :
desc F_ENF

id_enf     number(12)  ,
nom_enf    varchar2(100)
Re: sqlldr - datatype [message #547140 is a reply to message #547135] Wed, 12 March 2008 15:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
If your nom_enf is varchar2(100), then you do not need to use char(500).

If the following line is intended to replace \n with a chr(10) linefeed:

"replace(:NOM_ENF,'\n',chr(10))"

then you need to add an extra \ to escape and treat the \ as a character:

"replace(:NOM_ENF,'\\n',chr(10))"

Can you post some rows that get rejected without specifying char(500)? It might also help to have the row before and after that row. If the problem is length, then it may not be perceiving the end of the field or record where it should. I noticed that, although you are using " as the enclosure character, you also have " within your data. Although doubling the quotes seems to allow it to be processed correctly in the sample data that you provided, that may not be the case for all rows. It might be causing it to try to combine multiple fields or rows, thus exceeding the expected length. You might need to load the rows as is, without the optionally enclosed by, then trim the leading and trailing quotes and replace the doubled ones, like so:

NOM_ENF "replace (rtrim (rtrim (ltrim (replace(:NOM_ENF,'\\n',chr(10)), '\"'), chr(13)), '\"'), '\"\"', '\"')"

It might help to specify your linefeed character for your datafile, to clarify where the record ends, for example:

infile 'F_ENF.dat' "str X'0A'"

So, I would suggest the following for your control file:

load data
characterset AR8MSWIN1256
infile 'F_ENF.dat' "str X'0A'"
badfile 'F_ENF.bad'
into table F_ENF
TRUNCATE
fields terminated by X'3b'
(
ID_ENF		,
NOM_ENF "replace (rtrim (rtrim (ltrim
(replace(:NOM_ENF,'\\n',chr(10)),
'\"'), chr(13)), '\"'), '\"\"', '\"')"
)







Re: sqlldr - datatype [message #547147 is a reply to message #540945] Wed, 12 March 2008 15:46 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Thanks for the quick reply. I will get the information you asked for once at work.
Re: sqlldr - datatype [message #547260 is a reply to message #540945] Tue, 13 March 2012 11:08 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Here is the environement to test :
F_ENF.sql
drop table F_ENF
/

create table F_ENF
(
	ID_ENF		number(3)	,
	NOM_ENF		varchar2(500)
)
/


F_ENF.dat
198;pppppp pp ppppp pppppp pppp ppp (01)¡ pp pppp ppp ppppp pppp ppppp pppp ooooooo oooooo mmmmmmmmm mmmmmmm zzzzzzz/zzzzz¡ zzzz uuuuuu  yyy 2010/350/pop/ii/Ó ertyui az 2010.07.29.\\n- azert rtyu ertyui poiuyt azer tyu (01) poiuyt er 2011.09.19 ¡ tyui poiuyt  azer 2011/549/apo/i1/Ó polgtr po 2011.10.03.;|
200;\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n;|



F_ENF.ctl
load data
characterset AR8MSWIN1256
infile 'F_ENF.dat' "str x'7C0A'"
badfile 'F_ENF.bad' 
into table F_ENF
TRUNCATE
fields terminated by X'3b'
(
id_enf		,
nom_enf		"replace (rtrim (rtrim (ltrim (replace(:nom_enf,'\\n',chr(10)), '\"'), chr(13)), '\"'), '\"\"', '\"')"
)



F_ENF.bat
sqlldr my_user/pwd@utf8 control=F_ENF.ctl log=F_ENF_log_insert.log errors=9999999 rows=10000


I've changed the .ctl file as you mentioned.
- The "optionally enclosed by" was cut,

I've changed the .dat file as you mentioned.
- I've specified "|" to be the linefeed character,

The results are the same I've got.
Try to specify the data type for NOM_ENF for example to char(500) and the rows will be loaded.

You can check the NOM_ENF length for the two rows : it's 300 and 435 respectively.
The data to be inserted seem to be non valid (multiple chr(10)) but we don't care about the validity of the data, we insert it as it is.

Thank you in advance,

Amine
Re: sqlldr - datatype [message #547282 is a reply to message #547260] Tue, 13 March 2012 11:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Since your column length is 500 and some of your data is that long, then you do need the char(500). I don't see why that should be slowing things down, so I don't know of a way to speed that up. The only things that I can suggest are general things, such as concurrent conventional path loads or direct path load.
Re: sqlldr - datatype [message #547297 is a reply to message #547282] Tue, 13 March 2012 12:16 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Thank you. I've tried with direct=true, but lot of data was rejected. I've, of course, run the %ORACLE_HOME/rdbms/admin/catldr.sql on the DB.
Any other script to run before using direct path ?
Re: sqlldr - datatype [message #547303 is a reply to message #547297] Tue, 13 March 2012 12:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Amine wrote on Tue, 13 March 2012 10:16
Thank you. I've tried with direct=true, but lot of data was rejected. I've, of course, run the %ORACLE_HOME/rdbms/admin/catldr.sql on the DB.
Any other script to run before using direct path ?


As far as I know there aren't any other scripts that you need to run. There are various restrictions on using direct path, so you may not be able to use that. I would try the concurrent conventional path loads.


Re: sqlldr - datatype [message #547310 is a reply to message #547303] Tue, 13 March 2012 12:39 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

Could you provide me a good link that helps us to use direct path correctly ?
Re: sqlldr - datatype [message #547316 is a reply to message #547310] Tue, 13 March 2012 12:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_modes.htm
Re: sqlldr - datatype [message #547317 is a reply to message #547316] Tue, 13 March 2012 12:53 Go to previous messageGo to next message
Amine
Messages: 312
Registered: March 2010
Senior Member

thank you
Re: sqlldr - datatype [message #549461 is a reply to message #547317] Fri, 30 March 2012 12:26 Go to previous message
Sticker Printing
Messages: 7
Registered: March 2012
Location: USA
Junior Member
Since your column length is 250 and some of your data is that long, then you do need the char. I don't see why that should be slowing things down, so I don't know of a way to speed that up. The only things that I can suggest are general things
Previous Topic: SQL loader - import is having issue in new line \n
Next Topic: Loading Multiples excel files to multiple tables
Goto Forum:
  


Current Time: Tue Jan 28 07:11:12 CST 2020