Home » SQL & PL/SQL » SQL & PL/SQL » Integer Representation in oracle
Integer Representation in oracle [message #205124] Thu, 23 November 2006 06:51 Go to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi All,

I have a requirement. I have one function f(a varchar2)which accepts varchar value and have to return integer always.Integer because the system which will catch this value van only take integer value.

This function f('55555555')searches the database and when hit is found returns the product id correponding to the passed value.each product is represented by 2 digit integer.

So when 5 proucts are supported the 10 digit integer is returned like below.

SQL> select f('55555555') from dual;

FUNCTION_SYMPOSIUM_RETURN('55555555')
-------------------------------------
1112161920<--- 10 means product a
11 means product b like that


now when i entered another product corresponding to this pin ,means now 6 products are supported. then the output which comes is an exponential representaion of number.

SQL> select f('55555555') from dual;

FUNCTION_SYMPOSIUM_RETURN('55555555')
-------------------------------------
1.1121E+11
we need this value like 111213161920<--- for 6 product and so on.

we cant use varchar for returning the value.

Any advice .thanks a lot

Rahul

Re: Integer Representation in oracle [message #205126 is a reply to message #205124] Thu, 23 November 2006 07:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How about this?
Can you use like this?
select to_char(f('55555555')) from dual;

By
Vamsi
Re: Integer Representation in oracle [message #205138 is a reply to message #205124] Thu, 23 November 2006 07:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your fears are groundless - just because SQL*Plus shows this in an exponential format doesn't mean that Oracle has thrown away some of your data.

Try this and you'll see what I mean:

SQL> create or replace function return_large_number return integer as
  2  begin
  3   return 10000000000000000000000000001;
  4  end;
  5  /

Function created.

SQL> set numf ''
SQL> set num 10
SQL> select return_large_number from dual;

RETURN_LARGE_NUMBER
-------------------
         1.0000E+28
This looks like what you're getting
SQL> select mod(return_large_number,2) from dual;

MOD(RETURN_LARGE_NUMBER,2)
--------------------------
                         1
But see - all the data is still there
SQL> set num 30
SQL> select return_large_number from dual;

           RETURN_LARGE_NUMBER
------------------------------
 10000000000000000000000000001
And if you change the default numeric column width in SQL*Plus then everything is fine.
Re: Integer Representation in oracle [message #205145 is a reply to message #205126] Thu, 23 November 2006 08:17 Go to previous messageGo to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi ,

No we cant use varchar data type even in return type of function or even when function will be called.
Re: Integer Representation in oracle [message #205146 is a reply to message #205138] Thu, 23 November 2006 08:19 Go to previous message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
thanks a lot for the solution.

Regards
Rahul Priyadarshy
Previous Topic: Triggers Timings
Next Topic: Solved: Merge Error
Goto Forum:
  


Current Time: Tue Dec 06 04:33:35 CST 2016

Total time taken to generate the page: 0.06733 seconds