OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQLLDR (Oracle 11.2.0.1.0, Windows 10)

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

SQLLDR [message #642987]

Thu, 24 September 2015 03:02

chintu00
Messages: 91
Registered: February 2007
Location: NJ, US

Member

Kindly give me pointers to loading large urls into Oracle database and what would be a recommended columns for loading URLs.
I have data in a text file along with a serial number.

I have tried loading, it loads few rows and aborts as soon as it finds a row with special characters. Can't insert either

Example

144578,
http://506.xg4ken.com/media/redir.php?prof=21&camp=100950&affcode=cr688935&cid={creative}|2227550|{keyword}&mType={matchtype}&networkType={ifContent:content}{ifSearch:search}&kdv={device}&url[]=http%3A%2F%2Fdiscover.solarcity.com%2Fsemb%3Futm_source%3Dgoogle%26utm_medium%3Dcpc%26utm_content%3Dcov672982s%26utm_campaign%3DSEMGeneric

Report message to a moderator

Re: SQLLDR [message #642988 is a reply to message #642987]

Thu, 24 September 2015 03:37

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

Senior Member

For the column type - I can't see any reason to use anything other than varchar2.
As for why sqlloader isn't working - posting the log file with the error messages would help.
What problem do you have with insert?

Report message to a moderator

Re: SQLLDR [message #642989 is a reply to message #642988]

Thu, 24 September 2015 03:38

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

Senior Member

Also post the control file used

Report message to a moderator

Re: SQLLDR [message #642990 is a reply to message #642987]

Thu, 24 September 2015 03:40

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

When saying that you can't do it, you should SHOW us what you did and how Oracle responded. Because, I can do it.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 URL                                                VARCHAR2(1000)

load data
infile *
replace
into table test
fields terminated by ',' optionally enclosed by '"' trailing nullcols
(id,
 url char(1000)
)

begindata
144578,http://506.xg4ken.com/media/redir.php?prof=21&camp=100950&affcode=cr688935&cid={creative}|2227550|{keyword}&mType={matchtype}&networkType={ifContent:content}{ifSearch:search}&kdv={device}&url[]=http%3A%2F%2Fdiscover.solarcity.com%2Fsemb%3Futm_source%3Dgoogle%26utm_medium%3Dcpc%26utm_content%3Dcov672982s%26utm_campaign%3DSEMGeneric

SQL> $sqlldr scott/tiger@ora10 control=test16.ctl log=test16.log

SQL*Loader: Release 11.2.0.2.0 - Production on ╚et Ruj 24 10:39:38 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1

SQL> select * From test;

        ID
----------
URL
--------------------------------------------------------------------------------
    144578
http://506.xg4ken.com/media/redir.php?prof=21&camp=100950&affcode=cr688935&cid={
creative}|2227550|{keyword}&mType={matchtype}&networkType={ifContent:content}{if
Search:search}&kdv={device}&url[]=http%3A%2F%2Fdiscover.solarcity.com%2Fsemb%3Fu
tm_source%3Dgoogle%26utm_medium%3Dcpc%26utm_content%3Dcov672982s%26utm_campaign%
3DSEMGeneric


SQL>

Report message to a moderator

Re: SQLLDR [message #642998 is a reply to message #642988]

Thu, 24 September 2015 12:05

chintu00
Messages: 91
Registered: February 2007
Location: NJ, US

Member

The insert worked eventually by doing a set scan off on SQL*PLus
However the SQLLDR continued to fail, my control file was as below

load data
 infile 'C:\RCL\DDUrl.txt'
 append
 into table DDUrl
 fields terminated by "," optionally enclosed by '"' trailing nullcols
 (DestinationUrlKey, 
  DestinationUrl)

After I included char(1000) as below it worked, thanks to littlefoot.

load data
 infile 'C:\RCL\DDUrl.txt'
 append
 into table DDUrl
 fields terminated by "," optionally enclosed by '"' trailing nullcols
 (DestinationUrlKey, 
  DestinationUrl char(1000)
 )

What is the difference between including and not including char(1000) when I already have varchar2(1000) in my table definition.

Report message to a moderator

Re: SQLLDR [message #643000 is a reply to message #642998]

Thu, 24 September 2015 12:23

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

Senior Member
Account Moderator

The default size for CHAR in SQL*Loader is 255 bytes.
If it is greater you have to specify it.

https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1165

Report message to a moderator

Re: SQLLDR [message #643083 is a reply to message #643000]

Mon, 28 September 2015 15:57

chintu00
Messages: 91
Registered: February 2007
Location: NJ, US

Member

Thanks a lot to all

Report message to a moderator

Previous Topic:	ORA-31693: Table data object failed to load/unload and is being skipped due to error
Next Topic:	Initiate Expdp/impdp from oracle procedure.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:47:31 CST 2025