Solved

# Round functino in oracle

Posted on 2011-09-21
268 Views
Hello experts,

I have created a table like below:

SQL> create table gunti (col1 number) ;

And inserted one record as below:

SQL> insert into gunti values('-2.1005799412757564E16') ;
SQL> commit;

I want to round the value like -2.10
0
• 5
• 3

LVL 76

Expert Comment

select round(col1,2) from gunti;
0

LVL 76

Expert Comment

I just noticed the scientific notation in the 'string'.  That appears to cause a problem.

Let me see what I can find.
0

LVL 76

Expert Comment

Now that I've played with this a little, I'm not following the question.

-2.1005799412757564E16 equates to -21005799412757564

How do you expect to get -2.10?
0

LVL 16

Author Comment

@slightwv :
0

LVL 76

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points

This one:  -2.1005799412757564E16 equates to -21005799412757564

2.10E16 is scientific notation.  It is a way to show large numbers in a condensed format.  It is not a  number with decimal places.

http://en.wikipedia.org/wiki/Scientific_notation
0

LVL 16

Author Comment

Can you tell me the calculation for conversion based on my input?

How to find the exact conversion numbers for such input like mine.
As far as looked into the logic the notation should be on bases of 10.

0

LVL 76

Expert Comment

I'm not understanding what you want/need to do with the number.

To display the number in it's complete form in sqlplus just use: set numwidth 20

Below is an example
``````SQL> drop table gunti purge;

Table dropped.

SQL> create table gunti (col1 number) ;

Table created.

SQL> insert into gunti values('-2.1005799412757564E16') ;

1 row created.

SQL> commit;

Commit complete.

SQL> set numwidth 20
SQL> select col1 from gunti;

COL1
--------------------
-21005799412757564

SQL>
``````
0

LVL 16

Author Closing Comment

Thanks .. for detail explanation ...
0

## Featured Post

### Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function