Home » RDBMS Server » Server Utilities » SQL Loader with record Delimiter Issue
SQL Loader with record Delimiter Issue [message #384614] Wed, 04 February 2009 08:22 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

I have to load data into Oracle tables. The data file consists of '#' as a column delimiter and '^' as row delimiter.

Could any one help me how to write a control file with record delimiter.

Could any explain the difference between conventional and direct method and also the clause ' OPTIONALLY ENCLOSED BY '"'.


Any help really appreciated.

Thanks in advance.
Re: SQL Loader with record Delimiter Issue [message #384615 is a reply to message #384614] Wed, 04 February 2009 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
records delimited by '^' but I think it only works with external table not with SQL*Loader.

Regards
Michel

[Updated on: Wed, 04 February 2009 08:28]

Report message to a moderator

Re: SQL Loader with record Delimiter Issue [message #384671 is a reply to message #384614] Wed, 04 February 2009 14:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use utl_raw.cast_to_raw to get the hexidecimal representation of your record delimiter, then you can use that in your control file.

Optionally enclosed by means that the field may or may not begin and end with whatever the enclosure symbol is, but that if it is enclosed then that symbol should not be counted as part of the data and any delimiters within those enclosures should be considered part of the data, not delimiters.

You can read about the conventional and direct path loading methods in the following section of the online documentation:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_modes.htm#i1007501

The example below demonstrates usage of a feld terminator of # with an optional enclosure character of
~ and a record delimiter of ^.

SCOTT@orcl_11g> select utl_raw.cast_to_raw ('^') as "hexidecimal for ^" from dual
  2  /

hexidecimal for ^
--------------------------------------------------------------------------------
5E

SCOTT@orcl_11g>


-- test.dat:
1#2#3#4#^5#6#~7~#8#^9#10#11#12#^13#14#15#16#^


-- test.ctl:
load data
infile test.dat "str X'5E'"
into table test_tab
fields terminated by '#' optionally enclosed by '~'
(col1, col2, col3, col4)


-- test table:
SCOTT@orcl_11g> create table test_tab
  2    (col1  number,
  3  	col2  number,
  4  	col3  number,
  5  	col4  number)
  6  /

Table created.


-- load:
SCOTT@orcl_11g> host sqlldr scott/tiger control=test.ctl log=test.log


-- results:
SCOTT@orcl_11g> select * from test_tab
  2  /

      COL1       COL2       COL3       COL4
---------- ---------- ---------- ----------
         1          2          3          4
         5          6          7          8
         9         10         11         12
        13         14         15         16

SCOTT@orcl_11g> 

Re: SQL Loader with record Delimiter Issue [message #384787 is a reply to message #384671] Thu, 05 February 2009 03:40 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Tried with below eaxmple. But encoutered with following error.

Could anyopne help me in this.



options (skip =1)
load data
infile 'C:\dataload\data\backupprocess_ec1.txt "str X '5E'"  '
Replace into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)



error:

C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad,  USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 15:07:28 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 3.
Expecting keyword INTO, found "5".
infile 'C:\dataload\data\backupprocess_ec1.txt "str X '5E'"  '
                                                       ^


Any help really appreciated

Thanks in advance
Re: SQL Loader with record Delimiter Issue [message #384788 is a reply to message #384787] Thu, 05 February 2009 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
infile 'C:\dataload\data\backupprocess_ec1.txt "str X '5E'" '

Remove the red '.

Regards
Michel
Re: SQL Loader with record Delimiter Issue [message #384796 is a reply to message #384788] Thu, 05 February 2009 04:10 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Tried but encountered below error.

options (skip =1)
load data
infile C:\dataload\data\backupprocess_ec1.txt "str X '5E'"
into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)

C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad,  USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 15:25:15 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 3.
Expecting keyword INTO, found ":".
infile C:\dataload\data\backupprocess_ec1.txt "str X '5E'"


Thanks in advance
Re: SQL Loader with record Delimiter Issue [message #384800 is a reply to message #384796] Thu, 05 February 2009 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah! Windows stuff. Try:
infile 'C:\dataload\data\backupprocess_ec1.txt' "str X '5E'"

Regards
Michel
Re: SQL Loader with record Delimiter Issue [message #384809 is a reply to message #384800] Thu, 05 February 2009 04:50 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Again encountered with below error.

C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad,  USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 16:05:16 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\dataload\data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\dataload\data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.



Thanks in advance
Re: SQL Loader with record Delimiter Issue [message #384812 is a reply to message #384809] Thu, 05 February 2009 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
System error: The storage control block address is invalid.

Seems there is a problem on your server.
Is there not another program on the file? Did you close your editor?

Regards
Michel
Re: SQL Loader with record Delimiter Issue [message #384846 is a reply to message #384812] Thu, 05 February 2009 07:35 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,


I tried by removing the the line "str X '5E'". Its able to run without any errors. But records got rejected as record delimiter is '^'.

When i tried by including above line of code in control file.

I am encountered with below error.



C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad,  USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 19:01:46 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.


Could anyone help me on this.

Thanks in advance
Re: SQL Loader with record Delimiter Issue [message #384869 is a reply to message #384846] Thu, 05 February 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a small part of your file (that still generates the error) in order for us to be able to reproduce your case.

Regards
Michel
Re: SQL Loader with record Delimiter Issue [message #385001 is a reply to message #384869] Fri, 06 February 2009 01:07 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below attached is the datafile which is to be loaded into database tables.

Column Delimiter as "#"

Row Delimiter as "^"

Below are the table script, Control file and Errors encountered



Table Script
------------


  CREATE TABLE "BACKUPPROCESS_EC" 
   (	"BACKUP_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"CREATED" TIMESTAMP (6) NOT NULL ENABLE, 
	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"PROCESS_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"CONTENT" CLOB NOT NULL ENABLE, 
	"COMMENT" VARCHAR2(400 BYTE), 
	 PRIMARY KEY ("BACKUP_ID")
  ENABLE
   ) 



Control File
-------------

options (skip =1)
load data
infile 'C:\DataLoad\Data\backupprocess_ec.txt' "str X '5E'"
replace into table backupprocess_ec
fields terminated by "#"
trailing nullcols
(
BACKUP_ID,
CREATED "(to_timestamp(substr(:created,1,19), 'yyyy-mm-dd hh24:mi:ss'))",
USER_ID,
PROCESS_ID,
CONTENT Char(100000000),
"COMMENT" char(400)
)



Errors Encountered
---------------------
C:\Documents and Settings\200417>sqlldr control=C:\dataload\control\backupproces
s_ec.ctl, log=C:\dataload\log\backupprocess_ec.log, BAD=C:\dataload\Bad\backuppr
ocess_ec.bad,  USERID=temp/temp@eclipse_dev, Errors=999, Load=200000000, Discard
=C:\dataload\discard\backupprocess_ec.dsc, discardmax=5

SQL*Loader: Release 11.1.0.6.0 - Production on Thu Feb 5 19:01:46 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-555: unrecognized processing option
SQL*Loader-509: System error: The operation completed successfully.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-513: Unable to close file (C:\DataLoad\Data\backupprocess_ec1.txt)
SQL*Loader-559: error closing file
SQL*Loader-509: System error: The storage control block address is invalid.





Any Help really appreciated

Thanks in advance
Re: SQL Loader with record Delimiter Issue [message #385002 is a reply to message #385001] Fri, 06 February 2009 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I get the same error in 10.2.0.4.
I think you have to raise a SR in Metalink.

Regards
Michel
Re: SQL Loader with record Delimiter Issue [message #385066 is a reply to message #385001] Fri, 06 February 2009 05:47 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You have a space between X and '5E' that does not belong there. If you remove the space, 8 rows are loaded. 4 rows are not loaded because the comment data is too long. You could also use:

"str '^'"

instead of

"str X'5E'"

since the ^ is a visible character.

Previous Topic: Load .XML file using SQL*Loader
Next Topic: SQL loader and record limit
Goto Forum:
  


Current Time: Sun Dec 11 04:17:36 CST 2016

Total time taken to generate the page: 0.10423 seconds