OraFAQ Forum: Server Utilities » how to use Escape character for loading data via sql loader

Home » RDBMS Server » Server Utilities » how to use Escape character for loading data via sql loader (sql loader / oracle)

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

how to use Escape character for loading data via sql loader [message #546572]

Wed, 07 March 2012 08:24

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Hi,

I have to load a fixed width file using sql loader utility.
But the records have multiple special characters.
Can you please help me here writing/modifying the loader utility to load the data.

--Script to create the table
create table t1 (
ip1 varchar2(2),
ip2 number,
ip3 number);

--loader utility
LOAD DATA
INFILE 'c:\inputfile.dat'
BADFILE 'c:\badfile.bad'
REPLACE
INTO TABLE t1
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '°'
(
ip1 POSITION(1:2) CHAR,
ip2 POSITION(3:17) INTEGER EXTERNAL ":ip2/100",
ip3 POSITION(18:32) INTEGER EXTERNAL ":ip3/100",
)

--data file
9900000000000000000000059762160°
9900009694635473¶00009693856712-
99000024383898654000025664467904

--sql version i am using
SQL*Loader: Release 9.2.0.1.0 - Production on Wed Mar 7 18:32:33 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

In the above mentioned data file, records has multiple special characters like '°','¶' ,'-'.
All these special characters have some meaning.
eg: '°' specifies the above column needs to be multiplied by -1
'¶' specifies the above column needs to be multiplied by -0.1

Can please help here suggesting what changes need to be made in loader utility for the same?
Also, will there be any change in the utility if I am using higher version of oracle?

Thanks in advance for your reply.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546579 is a reply to message #546572]

Wed, 07 March 2012 08:52

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

reposting the correct loader utility

LOAD DATA
INFILE 'c:\inputfile.dat'
BADFILE 'c:\badfile.bad'
REPLACE
INTO TABLE t1
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '°'
(
ip1 POSITION(1:2) CHAR,
ip2 POSITION(3:17) INTEGER EXTERNAL ":ip2/100",
ip3 POSITION(18:32) INTEGER EXTERNAL ":ip3/100"
)

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546589 is a reply to message #546579]

Wed, 07 March 2012 09:17

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

Senior Member

I'd use an external table instead. Should make what you need to do a lot easier.
You can check for the special characters and do the appropriate calculation when selecting from it.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546598 is a reply to message #546589]

Wed, 07 March 2012 09:31

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

There are various ways that you could do this.

You could either use SQL*Loader to load it into a staging table or use an external table (if the data file is on your server), then insert from the staging or external table to the target table.

I would prefer to create a user-defined function to fix the columns and use that in your control file, so that you can do it all in one load, without a separate insert, as demonstrated below.

-- inputfile.dat:

9900000000000000000000059762160°
9900009694635473¶00009693856712 
99000024383898654000025664467904

-- test.ctl:

LOAD DATA
INFILE 'inputfile.dat'
BADFILE 'badfile.bad'
REPLACE
INTO TABLE t1
(
ip1 POSITION(1:2),
ip2 POSITION(3:17) "fix_col (:ip2)",
ip3 POSITION(18:32) "fix_col (:ip3)"
)

-- function to fix columns:

SCOTT@orcl_11gR2> create or replace function fix_col
  2    (p_ip	in varchar2)
  3    return	    number
  4  as
  5  begin
  6    return
  7  	 case when substr (p_ip, -1, 1) = '°'
  8  	      then to_number (substr (p_ip, 1, length (p_ip) -1 )) * -1
  9  	      when substr (p_ip, -1, 1) = '¶'
 10  	      then to_number (substr (p_ip, 1, length (p_ip) -1 )) * -0.1
 11  	      else to_number (p_ip)
 12  	 end / 100;
 13  end fix_col;
 14  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.

-- table:

SCOTT@orcl_11gR2> create table t1 (
  2  ip1 varchar2(2),
  3  ip2 number,
  4  ip3 number);

Table created.

-- load:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

-- results:

SCOTT@orcl_11gR2> select * from t1
  2  /

IP        IP2        IP3
-- ---------- ----------
99          0  -597621.6
99 -9694635.5 96938567.1
99  243838987  256644679

3 rows selected.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546718 is a reply to message #546598]

Thu, 08 March 2012 07:21

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Hi Barbara,

Thanks a ton for your help.....
I tried exactly the way you quoted above example.
But, I am still getting the following error(tried on oracle 9i and 10g).
Below is the snapshot of logfile.
Can you please help me here?

Thanks in advance for your help.

---log file snapshot------
SQL*Loader: Release 9.2.0.1.0 - Production on Thu Mar 8 17:20:11 2012

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: D:\test\dtbdwn_load.ctl
Data File: D:test\inputfile.dat
Bad File: D:\test\badfile.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table T1, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IP1 1:2 2 CHARACTER
IP2 3:17 15 CHARACTER
SQL string for column : "fix_col (:ip2)"
IP3 18:32 15 CHARACTER
SQL string for column : "fix_col (:ip3)"

Record 1: Rejected - Error on table T1, column IP3.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "DEV.FIX_COL", line 7

Record 2: Rejected - Error on table T1, column IP2.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "DEV.FIX_COL", line 7

Table T1:
1 Row successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 2560 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 2
Total logical records discarded: 0

Run began on Thu Mar 08 17:20:11 2012
Run ended on Thu Mar 08 17:20:19 2012

Elapsed time was: 00:00:08.34
CPU time was: 00:00:00.16

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546719 is a reply to message #546589]

Thu, 08 March 2012 07:22

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Thanks for your suggestion.
I Will try out your suggestion.

Thanks

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546720 is a reply to message #546589]

Thu, 08 March 2012 07:23

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Thanks for your suggestion cookiemonster.
I Will try out your suggestion.

Thanks

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546749 is a reply to message #546718]

Thu, 08 March 2012 09:21

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

I only have 11g to test with, but I seem to recall that in earlier versions it had problems when the data type wasn't the same. You might try the control file below and see if that solves the problem. Oracle 9i is unsupported. You should be using at least 10g.

LOAD DATA
INFILE 'inputfile.dat'
BADFILE 'badfile.bad'
REPLACE
INTO TABLE t1
(
ip1 POSITION(1:2),
ipa BOUNDFILLER POSITION(3:17),
ipb BOUNDFILLER POSITION(18:32),
ip2 POSITION(3:17) "fix_col (:ipa)",
ip3 POSITION(18:32) "fix_col (:ipb)"
)

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546753 is a reply to message #546749]

Thu, 08 March 2012 09:26

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

If the above doesn't work, then you can load into a staging table, then insert to the target table, by selecting from the staging table, as shown below.

-- inputfile.dat:

9900000000000000000000059762160°
9900009694635473¶00009693856712 
99000024383898654000025664467904

-- test.ctl:

LOAD DATA
INFILE 'inputfile.dat'
BADFILE 'badfile.bad'
REPLACE
INTO TABLE staging
(
ip1 POSITION(1:2) CHAR,
ip2 POSITION(3:17) CHAR,
ip3 POSITION(18:32) CHAR
)

-- staging table:

SCOTT@orcl_11gR2> create table staging (
  2  ip1 varchar2(2),
  3  ip2 varchar2(16),
  4  ip3 varchar2(16));

Table created.

-- load into staging table and results:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

SCOTT@orcl_11gR2> select * from staging
  2  /

IP IP2              IP3
-- ---------------- ----------------
99 000000000000000  00000059762160°
99 00009694635473¶  00009693856712
99 000024383898654  000025664467904

3 rows selected.

-- target table:

SCOTT@orcl_11gR2> create table t1 (
  2  ip1 varchar2(2),
  3  ip2 number,
  4  ip3 number);

Table created.

-- insert from staging table to target table and results:

SCOTT@orcl_11gR2> insert into t1 (ip1, ip2, ip3)
  2  select ip1,
  3  	    case when substr (ip2, -1, 1) = '°'
  4  		 then to_number (substr (ip2, 1, length (ip2) -1 )) * -1
  5  		 when substr (ip2, -1, 1) = '¶'
  6  		 then to_number (substr (ip2, 1, length (ip2) -1 )) * -0.1
  7  		 else to_number (ip2)
  8  	    end / 100,
  9  	    case when substr (ip3, -1, 1) = '°'
 10  		 then to_number (substr (ip3, 1, length (ip3) -1 )) * -1
 11  		 when substr (ip3, -1, 1) = '¶'
 12  		 then to_number (substr (ip3, 1, length (ip3) -1 )) * -0.1
 13  		 else to_number (ip3)
 14  	    end / 100
 15  from   staging
 16  /

3 rows created.

SCOTT@orcl_11gR2> select * from t1
  2  /

IP        IP2        IP3
-- ---------- ----------
99          0  -597621.6
99 -9694635.5 96938567.1
99  243838987  256644679

3 rows selected.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546799 is a reply to message #546753]

Fri, 09 March 2012 01:30

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Hi Barbara,

Once again thanks for your help and quick response.
I tried using BOUNDFILLER...
but still getting the following error.
This time i used oracle 10g for testing the loader utility.So the loader utility is not working for me.

Thanks once agin.

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 9 12:03:49 2012

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

Control File: dtbdwn_load.ctl
Data File: inputfile.dat
Bad File: inputfile.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table T1, loaded from every logical record.
Insert option in effect for this table: REPLACE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
IP1 1:2 2 CHARACTER
IPA 3:17 15 CHARACTER
(BOUNDFILLER FIELD)
IPB 18:32 15 CHARACTER
(BOUNDFILLER FIELD)
IP2 3:17 15 CHARACTER
SQL string for column : "fix_col (:ipa)"
IP3 18:32 15 CHARACTER
SQL string for column : "fix_col (:ipb)"

Record 1: Rejected - Error on table T1, column IP3.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "DEV.FIX_COL", line 9

Record 2: Rejected - Error on table T1, column IP2.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "DEV.FIX_COL", line 9

Table T1:
1 Row successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 4864 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 2
Total logical records discarded: 0

Run began on Fri Mar 09 12:03:49 2012
Run ended on Fri Mar 09 12:03:56 2012

Elapsed time was: 00:00:07.26
CPU time was: 00:00:00.07

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546803 is a reply to message #546799]

Fri, 09 March 2012 01:35

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

Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Align the columns in result.
Use the "Preview Message" button to verify.

Don't you see Barbara's posts are more readable than yours?

Regards
Michel

[Updated on: Fri, 09 March 2012 01:35]

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546804 is a reply to message #546799]

Fri, 09 March 2012 01:37

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Hi Barbara,

I also tried using staging table to load the data.
Below is the snap shot of how data is loaded into staging table.

SQL> select * from staging;

IP IP2 IP3
-- ---------------- ----------------
99 000000000000000 00000059762160?
99 00009694635473? 000096938567120
99 000024383898654 000025664467904

? is appearing ins taed of special characters in the ip2 and ip3 fields of staging table.

I feel this must be related to database character set.
Which character set is used in your database?
You can use this query to check.

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

If you feel the issue is different please share your thoughts.

Thanks in advance for your help.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546871 is a reply to message #546804]

Fri, 09 March 2012 07:30

kool.bird9
Messages: 8
Registered: March 2012

Junior Member

Hi Barbara,

I spotted the difference.
I was running the loader utility earlier on the database where character set parameter was set as
NLS_CHARACTERSET='US7ASCII'.
But then I executed on separate database where character set parameter was set as
NLS_CHARACTERSET='WE8MSWIN1252' where I was able to load data using both staging table and loader utility.

Thanks for your support and quick responses.

Report message to a moderator

Re: how to use Escape character for loading data via sql loader [message #546884 is a reply to message #546871]

Fri, 09 March 2012 11:52

Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA

Senior Member

I'm glad you solved your problem. Thanks for letting us know what it was. My character set is AL32UTF8.

Report message to a moderator

Previous Topic:	CSALTER finished unsuccessfully. (merged 3)
Next Topic:	Nested Table query optimization

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 01:20:51 CST 2025