RE: Create view using CAST to change datatype

  • From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 19 Mar 2004 10:54:58 -0500

The only correction here is: you "return null" (not return ' ') in
exception handler.

Igor Neyman, OCP DBA
ineyman@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F
Sent: Friday, March 19, 2004 10:46 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Create view using CAST to change datatype

Well,

the first problem you have is that you obviously have a character value
stored in that column.  This is causing the ORA-01722 - invalid number
error.  This means that you will not be able to do this using just a
view.
If you still need to do this, you will need to create a function to
attempt
to convert the value to a number and trap the error.  Something like:

Create or replace function convert_it(in_str in varchar2) return number
is

begin

  return to_number(nvl(rtrim(in_str,' '),0));
  exception
    when others then
        return '';
end;
/

CREATE OR REPLACE VIEW tomview 
AS 
SELECT 
 col1, col2,
 convert_it(col1) col1_numb
FROM tomtest 
/

This will get rid of the ORA-1722 error, as it will return a null value
for
all non-numeric columns.


As for the ORA-1002 error, I', not sure how you get that - I would fix
the
first one first and then see about the secnd one.

good luck


Tom Mercadante
Oracle Certified Professional


-----Original Message-----
From: Saira Somani-Mendelin [mailto:saira_somani@xxxxxxxxx]
Sent: Friday, March 19, 2004 10:41 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Create view using CAST to change datatype


I should also mention that along with the ORA-01722 error, I am also
receiving the ORA-01002: Fetch out of sequence error.

FYI, we are using version 8.1.7.4 and I am using TOAD on w2k pro.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Saira
Somani-Mendelin
Sent: March 19, 2004 10:35 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Create view using CAST to change datatype

I'm not sure why I'm leaning towards CAST...(I claim ignorance!)

In any case, I tried methods, both yours and Igor's, and received the
same error:

ORA-01722: invalid number

Somewhere in this table, there is a strange value. I was trying to make
life simpler without having to create multiple views to deal with these
data value issues (filter the view by some criteria). 

So here's another question (just out of curiosity), how can I determine
which value is causing me problems?

Thanks for all your help so far! I appreciate the time.
Saira

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mercadante, Thomas F
Sent: March 19, 2004 10:10 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Create view using CAST to change datatype

Saira,

This worked:

CREATE OR REPLACE VIEW tomview 
AS 
SELECT 
 col1, col2,
 cast(NVL(RTRIM(col1),0) AS NUMBER) col1_numb
FROM tomtest 

Values for the TOMTEST table were (' ',1);

SELECT * FROM tomview
returns

values of (' ', 1, 0)

And I agree wih Igor - why are you using CAST rather than to_number?


Tom Mercadante
Oracle Certified Professional

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: