Home » RDBMS Server » Server Utilities » SQL Loader REG - Number values
| SQL Loader REG - Number values [message #487814] |
Sun, 02 January 2011 08:36  |
 |
jensfr
Messages: 4
Registered: January 2011
|
Junior Member |
|
|
Hi,
we have to load some serious amount of data into an oracle database. We have extracted data out of another database and the number format for negative values is e.g. 50.00- / Positive values doesn't have a plus in e.g. its 50.00.
Is there a way to format the 50.00- to like -50.00 during the load with SQL loader? I was thinking of a regular expression?
Any help is highly appreciated. Thanks a lot.
Jens
|
|
|
|
|
|
| Re: SQL Loader REG - Number values [message #487818 is a reply to message #487816] |
Sun, 02 January 2011 08:48  |
|
jensfr
Messages: 4
Registered: January 2011
|
Junior Member |
|
|
Thanks, I will look into the guidelines.
Comeing back to my question.
e.g. i have record lines in a txt file like this. Like 60 Million records in txt file which needs to be uploaded. Do you have maybe an example how that would work?
USD,USD,ST,134.99-,1.74-,1.000-,004,
USD,USD,ST,28.37,3.61,1.000,004,
Thank you.
|
|
|
|
|
|
| Re: SQL Loader REG - Number values [message #487821 is a reply to message #487820] |
Sun, 02 January 2011 08:56 |
|
jensfr
Messages: 4
Registered: January 2011
|
Junior Member |
|
|
And we were looking into that and tried some to_number formats here download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34570
Realy tried some ways but nothing realy helps thats why I am here and hopped to get some help...
The problem is clearly that the - is at the end 50- and not in front -50...
[Updated on: Sun, 02 January 2011 08:58] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SQL Loader REG - Number values [message #487829 is a reply to message #487825] |
Sun, 02 January 2011 09:38 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Michel Cadot wrote on Sun, 02 January 2011 07:26
SQL> select to_number('134.99-','9999999.99S') from dual;
TO_NUMBER('134.99-','9999999.99S')
----------------------------------
-134.99
1 row selected.
The problem with that and other formats, is that it won't work for the positive numbers in the same column, unless they have a trailing + or space, as shown below.
SCOTT@orcl_11gR2> select to_number('134.99','9999999.99S') from dual;
select to_number('134.99','9999999.99S') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SCOTT@orcl_11gR2> select to_number('134.99','9999999.99MI') from dual;
select to_number('134.99','9999999.99MI') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SCOTT@orcl_11gR2>
The best way to go is probably to create a user-defined function, which just moves the minus sign, so that you don't have to worry about the rest of the format. Please see the demonstration below.
-- test.dat:
USD,USD,ST,134.99-,1.74-,1.000-,004,
USD,USD,ST,28.37,3.61,1.000,004,
-- test.ctl:
load data
infile test.dat
into table test_tab
fields terminated by ','
(col1, col2, col3,
col4 "my_to_num (:col4)",
col5 "my_to_num (:col5)",
col6 "my_to_num (:col6)",
col7 "my_to_num (:col7)")
-- table, function, load, and results:
SCOTT@orcl_11gR2> create table test_tab
2 (col1 varchar2 (4),
3 col2 varchar2 (4),
4 col3 varchar2 (4),
5 col4 number,
6 col5 number,
7 col6 number,
8 col7 number)
9 /
Table created.
SCOTT@orcl_11gR2> create or replace function my_to_num
2 (p_string in varchar2)
3 return number
4 as
5 begin
6 if substr (p_string, length (p_string), 1) = '-' then
7 return to_number ('-' || substr (p_string, 1, length (p_string) - 1));
8 else
9 return to_number (p_string);
10 end if;
11 end my_to_num;
12 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> select * from test_tab
2 /
COL1 COL2 COL3 COL4 COL5 COL6 COL7
---- ---- ---- ---------- ---------- ---------- ----------
USD USD ST -134.99 -1.74 -1 4
USD USD ST 28.37 3.61 1 4
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Dec 23 10:55:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
