FLOAT(126)
A reader, December 17, 2001 - 9:27 am UTC
Tom,
Why doesn't oracle store and display the datatype as number rather than float(126) ? What does the 126 mean ?
Thanks in advance, chris
December 17, 2001 - 9:29 am UTC
They are stored as NUMBER, as I said:
We sometimes fake a float for you (but not ALWAYS -- see above, b float(5)) in
the data dictionary views -- if you look at the text of USER_TAB_COLUMNS, you
would find:
decode(c.scale,
null, decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
so, floats are fake, integers are fake -- they are ALL numbers.
Clunky Date&Time to string?
A reader, December 17, 2001 - 3:08 pm UTC
Tom
Java developers I worked with had trouble handling DATE datatypes as in and out parms to and from my procs/functions. My workaround was to convert my Date&Time to strings for them (as you do in </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:494221681963, <code>). It seems a bit clunky. I read that SQLJ hasn't taken off widely although it simplifies JDBC/Oracle interface. Can Date&Time be left in it's native datatype by using SQLJ?
December 17, 2001 - 8:02 pm UTC
they will work seamlessly with java.sql.Timestamp types as well.
FLOAT
SANDEEP, August 25, 2003 - 3:29 pm UTC
Tom,
I converted a 8.1.7.4 database to 9.2.0.3 (Both on HP Unix Box)
After converting I noticed that....when I did describe on tables that had NUMBER data type....showed as FLOAT(0) in
SQLwORKSHEET (ORACLE enterprise manager 1.6.0)
but showed as NUMBER in SQL *PLUS worksheet (ORACLE enterprise manager 2.2.0.0.0)
Is everything O.K. with the conversion..or is it just an old version of OEM that's causing it.
Thanks
August 26, 2003 - 7:44 am UTC
it is OK
Is this datatype conversion necessary
A reader, October 04, 2003 - 5:43 pm UTC
I'm modifying some existing code.
I see that when concatanating some text to database columns of number datatype, the columns are being converted to char.
Example:
ln_amt is a database column with number datatype.
ln_amt number(8);
'Loan amount' ||to_char(ln_amt)
I dont think it is necessary to convert ln_amt to char before concatanating to the text string. In my opinion oracle will take care of the internal conversion.
What are the pros and cons , do doing and not doing the conversions of the above type.
October 04, 2003 - 5:58 pm UTC
Explicit is good.
Implicit is not as good.
It is 100% clear what is happening above -- ln_amt is some non-string being converted to a string to be concatenated on.
While not technically necessary - a nice touch. It can help in some cases:
x:= 'x' || a-b || 'y';
there, you need it (the to_char()) on a-b due to the order of operator precendence.
operator precedence ?
A reader, October 04, 2003 - 6:17 pm UTC
I understand your point
(SQL> var x varchar2(20);
SQL>
SQL> exec :x := 'a' || 5-4 || 'b' ;
BEGIN :x := 'a' || 5-4 || 'b' ; END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 1
SQL> exec :x := 'a' || to_char(5-4) || 'b' ;
PL/SQL procedure successfully completed.
SQL> print :x ;
X
--------------------------------
a1b
)
But what do you mean by "due to the order of operator
precendence" what is operator precedence.
October 04, 2003 - 7:22 pm UTC
the || happens before the - without a function call (or parens, we could go "implicit" again with
'x' || (a-b) || 'y'
but the to_char is "nice"
Please explain the basics
A reader, October 04, 2003 - 7:39 pm UTC
Why is (a-b) , the parentehsis making the difference?
October 04, 2003 - 7:58 pm UTC
a || b-c || d
is like
(a||b) - (c||d)
because the - happens "before" the ||
using
a || (b-c) || d
makes the - happens so we have:
a || BC || d
where BC is the result of (b-c). it changes the order of operation
like the difference between
a*b-c*d
and
a*(b-c)*d
Conversion?
Vinnie, January 12, 2004 - 9:39 am UTC
Tom,
What if I have a attribute that is VARCHAR2 type that
stores both characters & numbers but wish to somehow test to see if what is stored is a number? Can this be done?
January 12, 2004 - 1:25 pm UTC
there are many ways. easiest
create or replace function isnum( pstr in varchar2 ) return number
is
x number;
begin
x:= pstr;
return 1;
exception
when others then return 0;
end;
/
to_char(20)
A reader, March 19, 2004 - 2:42 pm UTC
hi tom,
if i do
select * from t
where to_char(substr(code,2)) < to_char(20)
is it gurenteed that it will give me all the
substr(code,2) < number 20 ?
will it both get transfer in ascii and compare ?
will it produce right result ?
March 19, 2004 - 2:49 pm UTC
ugh, no
there is no number 20 there, you have a string '20'
you will get the set of rows such that:
substr(code,2) < '20'
the to_char(substr( is redundant (substr returns a string already).
ugh, mess -- don't even know if code is a number or what.
If you want to compare to the "number" 20, there is but one thing to do. compare a NUMBER to the number 20.
to_char
A reader, March 19, 2004 - 6:04 pm UTC
sorry tom.
it is
select * from t
where substr(code,2) < to_char(20)
i have codes
01-abc
02-bca
03-cdx
04-drf
12-dde
09-okd
aa-kdn ***
10-bbc
my client has this data. I have to query his data to develop his small applicaiton. I need to find out all the codes where substr(code,2) < number 20
what is the best way ?
March 20, 2004 - 9:56 am UTC
but you see, is "a" the string less than 20 the number.
it is not but is that "right"
none of those codes mean ANYTHING when compared to "20"
1-abc < 20???? what does that even mean?
Number to Number(8)
Ravi, April 08, 2004 - 2:35 pm UTC
Hi Tom,
When a column in a table is defined as NUMBER, an external loader tool converts it to VARCHAR(214). If the datatype is defined as NUMBER(p, s) then it converts it to DECIMAL(p, s). To avoid the number being converted to VARCHAR, I need to convert the NUMBER to NUMBER(p, s).
Can I convert the data type in a view? I tried to use CAST(number_col_name as NUMBER(10)) but, it doesn't work. Can you please help?
April 08, 2004 - 3:42 pm UTC
what external loader tool? and have you asked them to correct their erroneous behaviour?
no, cast in a view won't work.
Number to Number(p, s)
Ravi, April 19, 2004 - 12:51 pm UTC
Tom,
Teradata's loading tools (Fastload, Multiload, etc) convert NUMBER to VARCHAR(214).
Thanks
Ravi
April 19, 2004 - 3:05 pm UTC
seems they have a bug? not sure what to tell you here -- I cannot fix their load tool, a view won't be useful here.
Java Dates and Oracle Dates
Mike, June 01, 2005 - 8:04 am UTC
I've been requested to move all of our DATE columns over to timestamps as the Java date object does not have a time element, when a query is thrown to the DB and this selects a date column, the time section is automaticallly lost via JDBC.
This sounds like a bag of crock to me, I'm surprised that the easiest option is to needlessly convert an awful lot of columns to timestamps, just so that JDBC can get at the time portion. I'm also concerned that the implication of this is more than it just allowing us to get date / times into Java. What are the storage implications? or are Dates / Timestamps synonymous at an internal level like INTEGERS, FLOATS etc. above?
Thanks,
Mike,
It's amazing what a bit of digging unearths...
Mike, June 01, 2005 - 12:51 pm UTC
A very good point, and well made, after asking for an example it becomes clear that is seems to be to do with Weblogic and it converting an object to a string... It seems it does a...
Object value = results.getObject(1);
System.out.println("Class we got back is " + value.getClass().toString());
System.out.println("Sysdate value = " + value.toString());
Which yields...
Class we got back is class java.sql.Date Sysdate value = 2005-06-01
and this loses the time element...
So as it's stored in a date in the DB the type of object back is a date, and so that means it has no time element...
I guess that's progress for you!!
Implicit Vs Explicit Conversion
Rajeshwaran, Jeyabal, December 18, 2010 - 11:07 am UTC
rajesh@11GR2>
rajesh@11GR2> declare
2 l_char varchar2(30) ;
3 l_time number;
4 begin
5 l_time := dbms_utility.get_time;
6 for i in 1..10000000
7 loop
8 l_char := i;
9 end loop;
10 dbms_output.put_line(' Total Time = '|| ( dbms_utility.get_time - l_time ) );
11
12 l_time := dbms_utility.get_time;
13 for i in 1..10000000
14 loop
15 l_char := to_char(i);
16 end loop;
17 dbms_output.put_line(' Total Time = '|| ( dbms_utility.get_time - l_time ) );
18 end;
19 /
Total Time = 414
Total Time = 498
PL/SQL procedure successfully completed.
Elapsed: 00:00:09.17
rajesh@11GR2>Tom:
I know the datatypes in Table column will have a huge impact in plans. What is the significant in having Explicit data type conversions. Do you think
to_char(i); is really needed here?
December 20, 2010 - 7:39 am UTC
it sure makes for SAFER, more clear, better code - doesn't it?
rhetorical question - yes, of course it does.
It is not always about "performance", it is about good code, clear code, CORRECT code.
and use CPU time (get_cpu_time), not elapsed time, elapsed time is affected by millions of things - background processes, anything.
But in any case - explicit conversions lead to correct code, code that does what you expect it to do, more clear code - more understandable code
and in many cases - it leads to much more performant code - as the proper indexes can be used (whereby an inappropriate implicit conversion would obviate indexes)