Home » SQL & PL/SQL » SQL & PL/SQL » Oracle number(*,0) [merged]
Oracle number(*,0) [merged] [message #306974] Mon, 17 March 2008 07:09 Go to next message
GaneshRamadoss
Messages: 5
Registered: February 2008
Junior Member
We have created a new table with below statement

create table TEST11 (COL1 NUMBER(*,0));

After creation of the table we did a edit and inserted a value
(9999999999999999)

The output in Toad looks like

SELECT COL1 FROM TEST11;

1E16


But we want the output as such as we inserted, so we did a to_char conversion, but this time the output is

SELECT to_char(COL1) FROM TEST11;

10000000000000000


Why it is ? Can somebody help on this to get the value as such as inserted.




Re: SQL Output Display [message #306980 is a reply to message #306974] Mon, 17 March 2008 07:17 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Works for me :

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table TEST11 (COL1 NUMBER(*,0));

Table created.

SQL>
SQL> INSERT INTO test11 VALUES (9999999999999999);

1 row created.

SQL>
SQL> SELECT COL1 FROM TEST11;

      COL1
----------
1.0000E+16

SQL>
SQL> SELECT to_char(COL1) FROM TEST11;

TO_CHAR(COL1)
----------------------------------------
9999999999999999

SQL> column col1 format 999999999999999999999
SQL> SELECT COL1 FROM TEST11;

                  COL1
----------------------
      9999999999999999

SQL>


How did you actually insert the value? What does "did a edit" mean?

Some kind of graphical front-end?

Then the problem is most likely at that point.

Re: SQL Output Display [message #306981 is a reply to message #306974] Mon, 17 March 2008 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you posted in Expert forum, you are an expert and an expert does not need our help to get the answer of this question.

Regards
Michel

[Updated on: Mon, 17 March 2008 07:18]

Report message to a moderator

ORACLE NUMBER(*,0) field problem [message #306985 is a reply to message #306974] Mon, 17 March 2008 07:27 Go to previous messageGo to next message
stank
Messages: 1
Registered: March 2008
Junior Member
Hello ,

I am facing problem with Oracle INTEGER/NUMBER(*,0) field.

I am trying to edit and enter the value 9999999999999999 (16 digits) in the number(*,0) field using toad, after adding and
committing the value it is converted to 10000000000000000 (17 digits).

Whereas If I use an insert statement with value 9999999999999999 (16) it works properly.Meaning it stores 9999999999999999( 16 digits)
ex : INSERT INTO TESTTABLE('TESTFIELD') VALUES(9999999999999999)

In my delphi code I am using dataset edit and post , so I am won't be using insert statement .
Is there any way which will solve my problem without using INSERT statement (As I told you , in delphi I am using edit and post on dataset which is similar to the adding and committing the value)
Can any one help me solving my problem.

Thanks in advance
Tanveer


Re: ORACLE NUMBER(*,0) field problem [message #306986 is a reply to message #306985] Mon, 17 March 2008 07:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Do you by any chance now this guy, which asked the same question a few minutes ago?
Re: ORACLE NUMBER(*,0) field problem [message #306987 is a reply to message #306986] Mon, 17 March 2008 07:37 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As he has the same IP it is likely the same one or someone close.
And he deserves the same answer:
Michel Cadot wrote on Mon, 17 March 2008 13:18
As you posted in Expert forum, you are an expert and an expert does not need our help to get the answer of this question.

Regards
Michel


Previous Topic: Transaction control in plsql
Next Topic: Advance Shipment Notice Import program
Goto Forum:
  


Current Time: Fri Dec 09 06:06:43 CST 2016

Total time taken to generate the page: 0.12765 seconds