From oracle-l-bounce@freelists.org Fri Mar 19 11:50:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JHomI17279 for ; Fri, 19 Mar 2004 11:50:49 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i2JHomo17274 for ; Fri, 19 Mar 2004 11:50:48 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5DDFA390BE3; Fri, 19 Mar 2004 12:49:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 12:47:55 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mx10.radisys.com (mx10.radisys.com [206.102.10.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D37073903D0 for ; Fri, 19 Mar 2004 12:47:51 -0500 (EST) Received: by mx10.radisys.com (Postfix, from userid 5) id 981C9142DE6; Fri, 19 Mar 2004 09:54:15 -0800 (PST) Received: from UNKNOWN(206.103.52.194), claiming to be "dcblackberry.radisys.com" via SMTP by mx10, id smtpdAAA0O0DBS; Fri Mar 19 09:54:13 2004 To: oracle-l@freelists.org Subject: RE: Create view using CAST to change datatype MIME-Version: 1.0 X-Mailer: Lotus Notes Release 5.0.10 March 22, 2002 Message-ID: From: Jared.Still@radisys.com Date: Fri, 19 Mar 2004 09:54:10 -0800 X-MIMETrack: Serialize by Router on DCBlackBerry/Radisys_Corporation/US(Release 6.0.3|September 26, 2003) at 03/19/2004 09:50:41 AM, Serialize complete at 03/19/2004 09:50:41 AM Content-Type: multipart/alternative; boundary="=_alternative 0062413C88256E5C_=" X-archive-position: 1273 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jared.Still@radisys.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --=_alternative 0062413C88256E5C_= Content-Type: text/plain; charset="us-ascii" If you are receiving ORA-1002 I suspect that you are using ODBC, and if so, you need to update your ODBC driver. The standard 8.1.7.4 ODBC driver does not allow you to control the autocommit nature of ODBC. Go to otn.oracle.com and click on Downloads. Under the 'Drivers' section click on ODBC. There you will find the 8.1.7.8.10 ODBC driver which will probably correct your ORA-1002 error. Jared "Saira Somani-Mendelin" Sent by: oracle-l-bounce@freelists.org 03/19/2004 07:40 AM Please respond to oracle-l To: cc: 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Saira Somani-Mendelin Sent: March 19, 2004 10:35 AM To: oracle-l@freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Mercadante, Thomas F Sent: March 19, 2004 10:10 AM To: 'oracle-l@freelists.org' 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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- --=_alternative 0062413C88256E5C_= Content-Type: text/html; charset="us-ascii"
If you are receiving ORA-1002 I suspect that you are using ODBC, and
if so, you need to update your ODBC driver.

The standard 8.1.7.4 ODBC driver does not allow you to control the
autocommit nature of ODBC.  

Go to otn.oracle.com and click on Downloads.

Under the 'Drivers' section click on ODBC.   There you will find
the 8.1.7.8.10 ODBC driver which will probably correct your ORA-1002 error.

Jared



"Saira Somani-Mendelin" <saira_somani@yahoo.com>
Sent by: oracle-l-bounce@freelists.org

 03/19/2004 07:40 AM
 Please respond to oracle-l

       
        To:        <oracle-l@freelists.org>
        cc:        
        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@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Saira
Somani-Mendelin
Sent: March 19, 2004 10:35 AM
To: oracle-l@freelists.org
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@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Mercadante, Thomas F
Sent: March 19, 2004 10:10 AM
To: 'oracle-l@freelists.org'
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@freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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@freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

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