Home » RDBMS Server » Server Utilities » SQL Loader REG - Number values
SQL Loader REG - Number values [message #487814] Sun, 02 January 2011 08:36 Go to next message
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 #487816 is a reply to message #487814] Sun, 02 January 2011 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do NOT confuse data presentation with data storage.
Where the minus sign is displayed, has NOTHING to do with how this data is stored.
Just use correct mask to load the data, then use different mask when data is presented to your users.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader REG - Number values [message #487818 is a reply to message #487816] Sun, 02 January 2011 08:48 Go to previous messageGo to next message
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 #487820 is a reply to message #487818] Sun, 02 January 2011 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+sqlldr+negative+numbers
Re: SQL Loader REG - Number values [message #487821 is a reply to message #487820] Sun, 02 January 2011 08:56 Go to previous messageGo to next message
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 #487823 is a reply to message #487821] Sun, 02 January 2011 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please realize that we do NOT have your table so we can't load the sample data.
so post DDL for table to be loaded.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader REG - Number values [message #487825 is a reply to message #487818] Sun, 02 January 2011 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_number('134.99-','9999999.99S') from dual;
TO_NUMBER('134.99-','9999999.99S')
----------------------------------
                           -134.99

1 row selected.

Regards
Michel
Re: SQL Loader REG - Number values [message #487829 is a reply to message #487825] Sun, 02 January 2011 09:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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>




Re: SQL Loader REG - Number values [message #487851 is a reply to message #487829] Sun, 02 January 2011 21:01 Go to previous message
jensfr
Messages: 4
Registered: January 2011
Junior Member
Thank you so much Barbara. You saved our Sunday and reduced our headache for the next couple of days.
Really appreciate that.

Thanks, Jens
Previous Topic: Oracle10g imp tables containing LOB
Next Topic: TKPROF-Cannot open trace file
Goto Forum:
  


Current Time: Thu Mar 28 08:06:16 CDT 2024