Home » RDBMS Server » Server Utilities » SQLLDR (Oracle 11.2.0.1.0, Windows 10)
SQLLDR [message #642987] Thu, 24 September 2015 03:02 Go to next message
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


Re: SQLLDR [message #642988 is a reply to message #642987] Thu, 24 September 2015 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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?
Re: SQLLDR [message #642989 is a reply to message #642988] Thu, 24 September 2015 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also post the control file used
Re: SQLLDR [message #642990 is a reply to message #642987] Thu, 24 September 2015 03:40 Go to previous messageGo to next message
Littlefoot
Messages: 21592
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>
Re: SQLLDR [message #642998 is a reply to message #642988] Thu, 24 September 2015 12:05 Go to previous messageGo to next message
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.
Re: SQLLDR [message #643000 is a reply to message #642998] Thu, 24 September 2015 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: SQLLDR [message #643083 is a reply to message #643000] Mon, 28 September 2015 15:57 Go to previous message
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
Member
Thanks a lot to all
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:
  


Current Time: Sun Sep 20 21:33:04 CDT 2020