OraFAQ Forum: Server Utilities » How to covert the scientific format value into number or text in oracle

Home » RDBMS Server » Server Utilities » How to covert the scientific format value into number or text in oracle (oracle 9i)

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

How to covert the scientific format value into number or text in oracle [message #341708]

Wed, 20 August 2008 01:30

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Hi Forum,

I used SQL loader to import an csv file into an Oracle database. One of the columns in the csv file is SNO, which is a text format in csv and VCHAR2(50) in Oracle DB.

The source (CSV file), is holding the correct data. But, adter importing into the oracle DB, the some of the serial numbers are converting into scientific format.

eg:

SERIAL_NUMBER
--------------------------------------------------
5.57E+11

But, the actual value of that SNO in the source CSV file is : 557000100816

Is there any way to change the format to normal text / number in excel or in the database itself.

Cheers,

KK

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341713 is a reply to message #341708]

Wed, 20 August 2008 01:36

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

Senior Member

The data is stored correctly as numeric data. How it is displayed is dependent on things like the deafult numformat and/or numwidth and column formats. If there is enough space to display the whole number, it does so. If not, then it uses scientific notation. Please see the demonstration below.


SCOTT@orcl_11g> set numwidth 10
SCOTT@orcl_11g> select 557000100816 as sno from dual
  2  /

       SNO
----------
5.5700E+11

SCOTT@orcl_11g> set numwidth 20
SCOTT@orcl_11g> select 557000100816 as sno from dual
  2  /

                 SNO
--------------------
        557000100816

SCOTT@orcl_11g>

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341725 is a reply to message #341713]

Wed, 20 August 2008 02:14

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Thanks Barbara for your response.

I tried the way you suggested by changing the set numwidth 40. But, no luck

SQL> set numwidth 20
SQL> select serial_number from anzo_base where instance_id = '142';

SERIAL_NUMBER
--------------------------------------------------
5.57E+11

SQL> set numwidth 40
SQL> select serial_number from anzo_base where instance_id = '142';

SERIAL_NUMBER
--------------------------------------------------
5.57E+11

SQL>

Cheers,

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341734 is a reply to message #341725]

Wed, 20 August 2008 02:23

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

desc anzo_base.
It seems your column is VARCHAR2 and not NUMBER.

Regards
Michel

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341737 is a reply to message #341734]

Wed, 20 August 2008 02:27

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Yes it is Varchar2 Michel. The reason being is I am having some more serial number, which has the combination of alphanumeric

SQL> desc anzo_base;
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_ID NOT NULL NUMBER(15)
INSTANCE_NUMBER NOT NULL NUMBER(15)
SERIAL_NUMBER NOT NULL VARCHAR2(50)
INSTANCE_STATUS_ID NOT NULL VARCHAR2(15)
ACTIVE_START_DATE NOT NULL VARCHAR2(15)
ACTIVE_END_DATE NOT NULL VARCHAR2(15)

Thanks

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341741 is a reply to message #341737]

Wed, 20 August 2008 02:48

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So you can just display what you have inserted in your column.
You inserted '5.57E+11' so it is displayed '5.57E+11', check your inserting program/script.

Regards
Michel

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341746 is a reply to message #341741]

Wed, 20 August 2008 02:53

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

NO, the actual value of that SNO in the source CSV file is : 557000100816.

It is changed in the Oracle DB after importing, and displaying as

SERIAL_NUMBER
--------------------------------------------------
5.57E+11

Cheers,

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341752 is a reply to message #341746]

Wed, 20 August 2008 03:31

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Maybe this is the actual value in your CSV file but it is NOT the actual value in the database.

Verify your load program, it is wrong.

Regards
Michel

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341801 is a reply to message #341752]

Wed, 20 August 2008 06:16

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Michel,

I've pasted the control file, which I am using to populate the Database based on the CSV file.

CONTROL FILE:

load data
infile 'D:\Documents and Settings\Administrator\Desktop\ANZO Install Base\ANZO_Install_Base\part1.csv'
into table load_anzo_base
fields terminated by "," optionally enclosed by '"'
( Instance_Id, Instance_Number, Serial_Number, Instance_Status_Id, Active_Start_Date, Active_End_Date, Owner_Party_Id,
Owner_Party_Account_Id, Owner_Customer_Name, Owner_Party_Number, Global_Customer_Name, Global_Party_Number, Install_Address1
)

Could, you please advice me, where about the changes are required.

Cheers,

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341805 is a reply to message #341801]

Wed, 20 August 2008 06:35

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post a FULL test we can reproduce:
- create table statement
- control file (done)
- data file (just few lines)

I move the topic to Utilities forum as it seems to be a loader issue not SQL one.

Regards
Michel

[Updated on: Wed, 20 August 2008 06:36]

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341915 is a reply to message #341801]

Wed, 20 August 2008 14:17

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

Senior Member

Your describe is of anzo_base and your select is from anzo_base, but your control file is loading into load_anzo_base. What is the structure of load_anzo_base? If you select from load_anzo_base with numwidth=20 is the data displayed as you expect? What process do you use to transfer the data from load_anzo_base to anzo_base?

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #341917 is a reply to message #341915]

Wed, 20 August 2008 14:22

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Good eye!

/forum/fa/1950/0/

Regards
Michel

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #342022 is a reply to message #341915]

Thu, 21 August 2008 01:51

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Michel, Barbara,

I fixed the issue. What I did is, I've changed the serial_number column format to TEXT in the csv file and imported it into the oracle DB.

Coming to your question.

I am loading my CSV file into a temp_table called (Load_anzo_base) and from there I am using

insert into anzo_base (select * from load_anzo_base); to load the actual table (anzo_base).

The reason being is I am having multiple CSV files, which I need to load it into one table. So, I used the load_anzo_base as a buffer.

Thanks all for you valuable assistance.

Cheers,

Krishna Kanigelpula.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #342152 is a reply to message #342022]

Thu, 21 August 2008 09:40

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

Senior Member

krishna.kanigelpula wrote on Wed, 20 August 2008 23:51

I fixed the issue. What I did is, I've changed the serial_number column format to TEXT in the csv file and imported it into the oracle DB.

I am not sure what you mean. Everything in a csv file is text. Perhaps you are referring to how it was defined when it was exported from excel or some such thing.

krishna.kanigelpula wrote on Wed, 20 August 2008 23:51

I am loading my CSV file into a temp_table called (Load_anzo_base) and from there I am using insert into anzo_base (select * from load_anzo_base); to load the actual table (anzo_base). The reason being is I am having multiple CSV files, which I need to load it into one table. So, I used the load_anzo_base as a buffer.

There is no need for the extra step. You can just use the INSERT option in your SQL*Loader control file to insert directly into the anzo_base table.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #342276 is a reply to message #342152]

Thu, 21 August 2008 19:28

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Barbara,

Yes, you are right. There is no need for an extra step. Thanks for advsing me. It saves a bit of time to me.

Is there any way, where I can use export multiple CSV's into a table at a time using the SQL loader.

Coming to the changing the column into text, I have surrounded the actual serial_number with the ("4235098324508934"), to make it as a text and saving as a CSV file and importing it into the DB. This worked well.

Cheers,

Krishna.

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #342345 is a reply to message #342276]

Fri, 22 August 2008 01:44

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

Senior Member

krishna.kanigelpula wrote on Thu, 21 August 2008 17:28

Is there any way, where I can use export multiple CSV's into a table at a time using the SQL loader.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/ldr_control_file.htm#sthref883

[Updated on: Fri, 22 August 2008 01:45]

Report message to a moderator

Re: How to covert the scientific format value into number or text in oracle [message #342355 is a reply to message #342345]

Fri, 22 August 2008 02:02

krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney

Junior Member

Thanks Barbara. I will go throgh the URL.

Report message to a moderator

Previous Topic:	datapump impdb error
Next Topic:	importing data into 10g from 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 15:05:12 CDT 2024