OraFAQ Forum: Server Administration » SQL "to

Home » RDBMS Server » Server Administration » SQL "to_integer" conversion

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL "to_integer" conversion [message #370706]

Fri, 21 January 2000 05:59

schnizer
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) ??

Report message to a moderator

Re: SQL "to_integer" conversion [message #370714 is a reply to message #370706]

Fri, 21 January 2000 16:09

Mark E Kane
Messages: 7
Registered: January 2000

Junior Member

Schnizer?

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.

Mark

Report message to a moderator

Re: SQL "to_integer" conversion [message #370728 is a reply to message #370714]

Mon, 24 January 2000 05:53

schnizer
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
--------------------- -------- ----
IVAL NUMBER(8)

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.

Report message to a moderator

Previous Topic:	Syntax question SEQUENCES
Next Topic:	Challenging One...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 06:32:21 CDT 2025