Home » RDBMS Server » Server Administration » SQL "to_integer" conversion
SQL "to_integer" conversion [message #370706] Fri, 21 January 2000 05:59 Go to next message
Messages: 3
Registered: January 2000
Junior Member
I have a special problem in creating a view:
An existing database column has the datatype varchar2(8). This column is filled with integer values; the datatype of this column cannot be changed.

In an 3'rd party client (via ODBC) i need this value as integer (that means number(8)) and not as double (that is number without scale).

The SQL-function to_number always returns number without scale; an user extended SQL function declared with return datatype integer also returns number without scale.

How can i force a number-expression to have the datatype number(8) ??
Re: SQL "to_integer" conversion [message #370714 is a reply to message #370706] Fri, 21 January 2000 16:09 Go to previous messageGo to next message
Mark E Kane
Messages: 7
Registered: January 2000
Junior Member

That's a tough one. I'll do some light research, but the best I can suggest is to use a procedure to convert it to a scalable number. It would not be so hard to write, but I'd suggest looking through some of the other web sites with files of PL/SQL tips so you would not have to reinvent the wheel.

If you do figure this one out, please post it or email me. My boss and I have a bet going on who will figure it out.

Re: SQL "to_integer" conversion [message #370728 is a reply to message #370714] Mon, 24 January 2000 05:53 Go to previous message
Messages: 3
Registered: January 2000
Junior Member
Now i've found a solution for this problem:
By defining an object type and a view on this type, subtypes of number can be defined as column types in the view.

-- SQL-Plus script to demonstrate this:

create or replace type int_type as object
(ival number(8)

create or replace view int_view
of int_type with object oid (ival)
as select to_number(cid) from char_tab;

describe int_view
Name Null? Type
--------------------- -------- ----

The subtype is not implemented very sharp, since a conversion of a string with more than 8 digits does not raise an exception.
Possibly the external data type is more restrictive than the internal one, but no check is performed.
Previous Topic: Syntax question SEQUENCES
Next Topic: Challenging One...
Goto Forum:

Current Time: Sat Oct 24 04:51:29 CDT 2020