Home » RDBMS Server » Server Utilities » How to covert the scientific format value into number or text in oracle (oracle 9i)
How to covert the scientific format value into number or text in oracle [message #341708] Wed, 20 August 2008 01:30 Go to next message
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.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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>

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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good eye! ./fa/1950/0/

Regards
Michel
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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.


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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
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 Go to previous message
krishna.kanigelpula
Messages: 12
Registered: August 2008
Location: Sydney
Junior Member
Thanks Barbara. I will go throgh the URL.
Previous Topic: datapump impdb error
Next Topic: importing data into 10g from 9i
Goto Forum:
  


Current Time: Fri Dec 09 02:11:17 CST 2016

Total time taken to generate the page: 0.14481 seconds