Tom,

Excellent demostration ... thanks for making me understand this ..but again (sorry for my ignorance) i have a question. i do understand that the query is executed (before your correction) as

SELECT TO_NUMBER(TRIM(CITY_ZIP_START_CD)) SCD,TO_NUMBER(TRIM(CITY_ZIP_END_CD)) ECD FROM TB_CMA086_US_CITY
WHERE DECODE((REPLACE(TRANSLATE(TRIM(CITY_ZIP_START_CD),'0123456789','00000000000'),'0',NULL)),NULL, -9876121254,-12345 ) = -9876121254
AND 681 >= TO_NUMBER(TRIM(CITY_ZIP_START_CD))
AND 681 <= TO_NUMBER(TRIM(CITY_ZIP_END_CD))

but the person who posted this looks like he had intended to get the result out of inline view and use that scd,ecd in the predicate.

to my little knowledge on sql, i understand that inline query could execute first and then the other..is that not so ? ( hope u hate this to hear !! ) ..please correct me.

Thanks for your willingness to share.

Hi Tom,

Thank you so much for your solution. My problem got resolved using your query.

In ZiP_code field we have data of both number and characters as well, like P01, PA12.

Thanks,
Venkat

</code> http://www.dbdebunk.com/page/page/1351381.htm <code>

Sorry ..
mistakenly I put my e_mail address at wrong place.

Back just a bit, the reply titled "this is an optimizer problem", points to Chris Date's article on this topic:

</code> http://www.dbdebunk.com/page/page/1351381.htm

I just want to point out that no one is asking Oracle to *materialize* the subquery's result set before evaluating the outer query. It is fair however, to hold up a specific SQL implementation to the standard, and to question why the implementation does not match the standard.

There are at least two ways in which the optimizer could merge the queries while preserving the original semantics. One is to follow the method described in Dan Tow's article:

http://www.onlamp.com/pub/a/onlamp/2004/09/02/wrongerrorbugs.html <code>

Another approach is for the *optimizer* to evaluate predicates from the subquery first, before those from the main query. This can be done without materializing the subquery, and it's perfectly fair for the optimizer (but not for us) to decide on the order.

Regardless of what side of the fence you fall on with respect to this issue, it's important, I believe, to understand precisely what the SQL standard says, to understand the underlying relational operations, and to understand how a given vendor has chosen to implement the functionality under discussion.

Chris is planning a fascinating follow-up that goes into the underlying relational operations. I'll post a link when his follow-up goes live. What he has to say, frankly, just plain rocks (i.e. it's illuminating). He's an exceptionally clear thinker.

select flag, to_number(x) from
(SELECT flag, num x
FROM subtest
WHERE flag IN ('A', 'C')
)
where X>'0'

Output should be same?

<b>if you stuff numbers into a string, you've made a classic mistake (same with stuffing a date into a string, or a date into a number).  period. 

No, where x > '0' does not work</b>


ops$tkyte@ORA9IR2> select x,
  2         case when x > '0' then 'x > 0' end,
  3         case when x > 0 then 'to_number(x) > 0' end
  4    from t
  5  /
 
X                         CASEW CASEWHENX>0THEN'
------------------------- ----- ----------------
0
   0
00.00                     x > 0
-0
+1                              to_number(x) > 0
-1
.1                              to_number(x) > 0
0e-10                     x > 0

8 rows selected.
 

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  CHAR(1)
 V                                                  VARCHAR2(10)

SQL> select * from t;

C V
- ----------
A 100
B +100
C .100abc
D +100-200
E 0000+200
F +0.200.2
G +0.200

7 rows selected.

SQL> @bug2

C          N
- ----------
A        100
B        100
G         .2

SQL> l
  1  select * from
  2  (select c, to_number(
  3    case when translate(v,'+-.1234567890','XXXXXXXXXXXXX')=lpad('X',length(v),'X') then
  4      (case when instr(ltrim(translate(v,'+-','XX')),'X')>1 then NULL
  5       else (case when instr(ltrim(translate(v,'+-','XX')),'X',
  6                             instr(ltrim(translate(v,'+-','XX')),'X')+1
  7                            )>1 then NULL
  8             else (case when instr(translate(v,'.','X'),'X',
  9                                   instr(translate(v,'.','X'),'X')+1)>0 then NULL
 10                   else v
 11                   end
 12                  )
 13             end
 14            )
 15       end
 16      )
 17    else NULL
 18    end) n
 19   from t
 20  )
 21* where n>0
SQL> 

Thank you 

If you REALLY REALLY have to store numbers and varchars in the same column, then you can use the SYS.ANYDATA datatype to properly control it.
That means you get a built-in (and therefore supported) function to determine if the value is numeric, which can be included in a CASE or DECODE so that it is guaranteed that only numeric values will be exposed.

SQL> create table test (col_a anydata);

Table created.

SQL> insert into test values (anydata.convertnumber(10));

1 row created.

SQL> insert into test values (anydata.convertvarchar2('abc'));

1 row created.

SQL> select anydata.gettypename(col_a) from test;

ANYDATA.GETTYPENAME(COL_A)
---------------------------------------------------------

SYS.NUMBER
SYS.VARCHAR2

SQL> select case when anydata.gettypename(col_a) = 'SYS.NUMBER' then
  2      anydata.accessnumber(col_a) end col_a_val, rownum
  3  from test;

 COL_A_VAL     ROWNUM
---------- ----------
        10          1
                    2

 

drop table t1;
create table t1 (
parameter varchar2(30),
value varchar2(30));

insert into t1 values ('object_name','DBMS_JOB');
insert into t1 values ('sort','1');
insert into t1 values ('sort','2');
commit;

drop table t2;
create table t2 (
object_name varchar2(30),
sort number);

insert into t2
select object_name, round(dbms_random.value(0,9))
from all_objects
where rownum <= 1000;

select count(*) from t2
where object_name in (select value
from t1
where parameter = 'object_name')
or sort in (select value
from t1
where parameter = 'sort');

select count(*) from t2
where object_name in (select value
from t1
where parameter = 'object_name');

select count(*) from t2
where sort in (select value
from t1
where parameter = 'sort');

in some cases - you simply will find that numerics and chars have to be mixed. for example a house number can be

1
1a
2
2a
2b

What data type should this be.

I need to know that how TO_NUMBER works with NULL values, 
I am getting the error(ORA-01722) if I am trying to apply TO_NUMBER to VARCHAR2 column having NULL in it.
Pls look at the structure
SQL> desc letter_requests
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LRE_PERSON_ID                                      VARCHAR2(8)
 LRE_ITEM_SEQ                              NOT NULL NUMBER(8)
 LRE_LETTER_CODE                           NOT NULL VARCHAR2(10)
 LRE_USER_ID                                        VARCHAR2(8)
 LRE_DATE_REQUESTED                                 DATE
 LRE_STATUS_CODE                                    VARCHAR2(3)
 LRE_UPDATE_HISTORY                                 VARCHAR2(1)
 LRE_STATUS_DATE                                    DATE
 LRE_BATCH_NO                                       NUMBER(8)
 FIELD1                                             VARCHAR2(30)
 LRE_CERT_EXPIRY_DATE                               DATE
 LRE_VRQ_SEQ_ID                                     NUMBER(8)
SQL>
SQL> SELECT FIELD1 FROM  letter_requests
  2  WHERE LRE_ITEM_SEQ = 205537;
FIELD1
------------------------------

SQL> SELECT TO_NUMBER(FIELD1) FROM  letter_requests
  2  WHERE LRE_ITEM_SEQ = 205537;
SELECT TO_NUMBER(FIELD1) FROM  letter_requests
       *
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT TO_NUMBER(FIELD1) FROM letter_requests WHERE LRE_ITEM_SEQ = 185796;
TO_NUMBER(FIELD1)
-----------------
            35491
Can you please suggest me how to get rid of this error ?, beacuse I am using this select in my code and there can be few rows with null values.
Thanks & Regards
Ravi Kumar
Delhi, India 

Actualy There was a blank space in the column. Which I thought is a NULL..

I am really sorry about that I should have checked it before i sent.

Thanks & regards
Ravi Kumar

Hi Tom,

We have Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit Production. The client is also 10g.

When I am executing the following query,

select to_number('99.50') from dual;

I got Oracle error ORA-01722: invalid number

If I will connect using 8i client then it is Okay.

Help me please!!!

Faisal

Nothing to do with client version.

Everything to do with CLIENTS NLS SETTINGS THEY CHOSE.  They decided they were in Europe (the 10g installation did :)


 
ops$tkyte@ORA9IR2> select value from v$nls_parameters where parameter = 'NLS_NUMERIC_CHARACTERS';                                                                                                      
VALUE
----------------------------------------------------------------
.,
 
ops$tkyte@ORA9IR2> select to_number( '9.9' ) from dual;
 
TO_NUMBER('9.9')
----------------
             9.9
 
ops$tkyte@ORA9IR2> alter session set nls_numeric_characters = ',.';
 
Session altered.
 
ops$tkyte@ORA9IR2> select to_number( '9.9' ) from dual;
select to_number( '9.9' ) from dual
                  *
ERROR at line 1:
ORA-01722: invalid number
 

Yes Tom, you are right. I changed the NLS setting from ENGLISH.CANADA to AMERICAN.AMERICAN in registry and it is working now. Thanks for the tip.

Faisal

Hi Tom

I am getting the same invalid number error, altough I am using a character field to store numeric information but this was working when I deployed the system in April. I have checked with my DBA no change was made to the DBMS after deployment. What could be the problem?

I have to agree with Tom.

Numbers don't belong in strings. Strings don't belong in numbers. Converting with to_string, etc takes one heck of a lot of processing time over large recordsets. Why not just store it correctly in the first place? You save overhead, and don't get random errors like these.

Cheers!

Hi Tom

Thanks for your reply, but I have already the data and it doesnt contain any "not numeric" data. Spaces are there but they have always been there.

Keeping a char field to store numeric data is stupidity in my opinion too but now its too late for that. It is not one instance I am worried about, its what this issue can do to the rest of my applications

Hi Tom,

Thank you for all your previous response and postings which were really useful to us.  
Could you please help us with the below sql.

we have tables

SQL> desc serv_req_si
 Name                  Null?    Type
 ------------------------------------
 DOCUMENT_NUMBER       NOT NULL NUMBER(9)
 SERV_ITEM_ID          NOT NULL NUMBER(9)
 ITEM_ALIAS                     VARCHAR2(75)
 SPEC_GRP_ID                    NUMBER(9)
 ACTIVITY_CD           NOT NULL CHAR(1)
 QTY                            NUMBER(10)
 STATUS                         CHAR(1)
 TRUNK_SEG                   VARCHAR2(4)   

SQL> DESC SERVICE_REQUEST_CIRCUIT
 Name                    Null?    Type
 ----------------------- -------- -------------
 DOCUMENT_NUMBER         NOT NULL NUMBER(9)
 CIRCUIT_DESIGN_ID       NOT NULL NUMBER(9)
FACILITY_ASSIGNMENT_INDICATOR     VARCHAR2(4)
ABS_EXTRACT_DATE                  DATE
COMPLETION_DATE                   DATE
SECONDARY_LOCATION                VARCHAR2(25)
 ASR_FORM_TYPE           NOT NULL VARCHAR2(3)
 CABS_EXTRACT_IND        NOT NULL CHAR(1)
 LAST_MODIFIED_USERID    NOT NULL VARCHAR2(8)

I have not described the whole big table here, I assume the problem lies in the document number column(I may be wrong again).

Here is the query that access this and whole lot of other tables around.
select distinct
 a.exchange_carrier_circuit_id,
 a.tg_number,
 b.GLARE_ACTION,
 b.DIRECTION_IND,
 b.START_SIGNALLING_TYPE_IN,
 b.START_SIGNALLING_TYPE_OUT,
 b.SUPERVISION_SIGNALLING_TYPE_IN,
 b.SUPERVISION_SIGNALLING_TYPE_OU,
 b.INPULSE_TYPE,
 b.OUTPULSE_TYPE,
 b.SELECTION_SEQUENCE
 from
 (select distinct
   circuit.exchange_carrier_circuit_id,
   max(circuit.circuit_design_id) as circuit_design_id,
   max(serv_item.serv_item_id) as serv_item_id,
   max (SERV_REQ.ORDER_NUMBER) as document_number,
   CIRCUIT_XREF.CIRCUIT_XREF_ECCKT as tg_number
   from
   circuit,
   serv_item,
   serv_req_si,
   circuit_xref,
   ACCESS_SERVICE_REQUEST,
   SERV_REQ,
   SERVICE_REQUEST_CIRCUIT
   where
   (circuit.circuit_design_id = serv_item.circuit_design_id)
   and ( SERV_REQ.DOCUMENT_NUMBER=ACCESS_SERVICE_REQUEST.DOCUMENT_NUMBER(+) )
   AND  ( SERV_REQ.DOCUMENT_NUMBER=SERVICE_REQUEST_CIRCUIT.DOCUMENT_NUMBER )
   AND  ( SERVICE_REQUEST_CIRCUIT.CIRCUIT_DESIGN_ID=CIRCUIT.CIRCUIT_DESIGN_ID )
   AND  ( SERV_REQ.DOCUMENT_NUMBER=SERV_REQ_SI.DOCUMENT_NUMBER )
   AND  ( CIRCUIT.CIRCUIT_DESIGN_ID=CIRCUIT_XREF.CIRCUIT_DESIGN_ID (+))
   and (serv_item.item_alias like 'Dedicated Access Ckt%' or serv_item.item_alias like 'ISDN Access Circuit%')
   and circuit_xref.ACCESS_PROVIDER_SERV_CTR_CODE = 'WM02'
   group by
   circuit.exchange_carrier_circuit_id,
   circuit_xref.circuit_xref_ecckt) a,
 (select distinct
 special_trunk_group.GLARE_ACTION,
 special_trunk_group.DIRECTION_IND,
 special_trunk_group.START_SIGNALLING_TYPE_IN,
 special_trunk_group.START_SIGNALLING_TYPE_OUT,
 special_trunk_group.SUPERVISION_SIGNALLING_TYPE_IN,
 special_trunk_group.SUPERVISION_SIGNALLING_TYPE_OU,
 special_trunk_group.INPULSE_TYPE,
 special_trunk_group.OUTPULSE_TYPE,
 special_trunk_group.SELECTION_SEQUENCE,
 serv_req_si.DOCUMENT_NUMBER,
 serv_item.serv_item_id
 from
 serv_item,
 special_trunk_group,
 circuit,
 serv_req_si,
 service_request_circuit
 where
 (serv_item.trunk_group_design_id = special_trunk_group.trunk_group_design_id)
 and (serv_item.circuit_design_id = circuit.circuit_design_id)
 and (service_request_circuit.CIRCUIT_DESIGN_ID = circuit.circuit_design_id)
 and (serv_item.serv_item_id = serv_req_si.serv_item_id)) b
 where
 a.document_number = b.document_number(+)
/
 a.document_number = b.document_number(+)
 *
ERROR at line 64:
ORA-01722: invalid number

Its similar the one that venkat's posting in this forum.  But I am not converting the number to string or vice versa.

Any help would be really appreciated Tom,

Thanks,
David.
                

tell you want, do an explain plan on the query and use dbms_xplan to display the resulting query plan:



ops$tkyte%ORA10GR2> create table t1 ( x int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x varchar2(10), y int );

Table created.

ops$tkyte%ORA10GR2> create table t3 ( y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> delete from plan_table;

7 rows deleted.

ops$tkyte%ORA10GR2> explain plan for
  2  select *
  3    from t1,
  4         t2,
  5             t3
  6   where t1.x = t2.x
  7     and t2.y = t3.y;

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2607615570

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    46 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    46 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    26 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1   |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T3   |     1 |    13 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T2   |     1 |    20 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."X"=TO_NUMBER("T2"."X") AND "T2"."Y"="T3"."Y")

Note
-----
   - dynamic sampling used for this statement



see how the implicit conversion is clearly called out in the predicate information.  Look for it. 

Thank you Tom,

I will follow up with the Explain plan statment and look into it.

Thank you for your time and patience.

Regards,
David.

HI TOM? I HAVE THIS PC GAME... AND IM UNABLE 2 PLAY IT BECAUSE IT SAY I NEED 2 INSERT THE ORIGINAL DISC INSTED OF BACK UP {%CODE%}... I TAP ON THE CODE AND THIS INVALID NUMBER COME UP.. WHATS THE GO LIKE THIS IS THE ORRIGINAL DISK..

Yes, I agree with that.
Unfortunately, Oracle Applications (eBS...) have these nice flexfields defined as VARCHAR2(240) (or 150, or 200, depending on the table), and if you want to have a number in a flexfield, you have no altenative other than stuff it in a varchar2.
A bug waiting to happen. And you won't wait long.

TO add to what Pablo schneiter has said, since we use flexfields heavily to store those information for which forms given by standard product (like AR, AP, GL) dont have a space. So we are forced to use this.

My question is when a script fails for with ORA-01722 error can we identify atleast which row caused this error to occur. Why i ask this is because we have a sql*plus report that prints few records and then throws this error. Not sure which record caused this error, moreover we use a plsql packaged function calls more than once. It will be easier to drill and identify the data that caused this issue, if we can locate which row caused this error.

Thanks
Mani

Hi Tom,

Please refer the below details for more information.

Thanks for your response and sorry for giving you trouble.
As you are saying the number & character should not be matched.

However, still the question arise why my first query gives the output with same number varchar comparision and as soon as I changed the order in where clause with same condition it fails. I wanted to know "Is there any rule follows while executing the query?"

SQL> SELECT *
2 FROM xyz
3 WHERE aab = 103 AND aac = 103
4 /

AAB AAC
--- ----------
103 103

SQL>
SQL> SELECT *
2 FROM xyz
3 WHERE aac = 103 AND aab = 103
4 /

From: Thomas Kyte [mailto:thomas.kyte@oracle.com]
Sent: Friday, July 06, 2007 5:13 PM
To: Bipin Ganar
Subject: Re: Does oracle follows any particular rule while executing the query?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504677087008

*never* compare a string to a number, compare strings to strings and numbers to numbers and dates to dates - NEVER rely on implicit conversions, always SPECIFY the correct datatype.

Bipin Ganar wrote:
Hi Tom,

Please check the details below:

Script to produce the error.

SQL>
SQL> CREATE TABLE xyz (aab VARCHAR2(3),aac VARCHAR2(10))
2 /

Table created.

SQL>
SQL> CREATE INDEX in_xyz ON xyz(aab)
2 /

Index created.

SQL>
SQL> INSERT INTO xyz
2 VALUES (100, 'A100')
3 /

1 row created.

SQL>
SQL> INSERT INTO xyz
2 VALUES (101, 'A101')
3 /

1 row created.

SQL>
SQL> INSERT INTO xyz
2 VALUES (102, 'A102')
3 /

1 row created.

SQL>
SQL> INSERT INTO xyz
2 VALUES (103, '103')
3 /

1 row created.

SQL>
SQL> SELECT *
2 FROM xyz
3 WHERE aab = 103 AND aac = 103
4 /

AAB AAC
--- ----------
103 103

SQL>
SQL> SELECT *
2 FROM xyz
3 WHERE aac = 103 AND aab = 103
4 /
WHERE aac = 103 AND aab = 103
*
ERROR at line 3:
ORA-01722: invalid number


SQL>

In the above execution when I switch the condition from 1st to 2nd then it gives me an error whereas same sql has given me the result with different order in where clause. Is there any specific rules that oracle follows for execution? What if more than 1 table is there? Does where condition follows rule like top to bottom or bottom to top?

ops$tkyte%ORA9IR2> create table t ( x varchar2(5), y varchar2(5) );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into t values ( '1', 'x' );

1 row created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from t where y = 2 and x = 2;

no rows selected

ops$tkyte%ORA9IR2> select * from t where x = 2 and y = 2;
select * from t where x = 2 and y = 2
                                *
ERROR at line 1:
ORA-01722: invalid number


ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from t where y = 2 and x = 2;
select * from t where y = 2 and x = 2
                      *
ERROR at line 1:
ORA-01722: invalid number


ops$tkyte%ORA9IR2> select * from t where x = 2 and y = 2;

no rows selected

declare sno number(5);
lname varchar2(20);
fname varchar2(20);
begin
sno:='&number';
select STUDENT_FIRSTNAME, STUDENT_LASTNAME into

fname,lname from students where STUDENT_NUMBER=sno;
dbms_output.put_line('Student name is');
dbms_output.put_line(fname);
dbms_output.put_line(lname);
end;

when i executing this i will get the error at line 1 as invalid number ..please give me more details about this error & how can i rectify it..

Hello Tom,
you wrote the following statement in response to the original question: "The query is being optimized in such a fashion so that it is really being processed as: ....."

The example from above contains only tables, no m-views.

Is it possible to trace such an optimization or how do you determine how oracle optimize the sql statement in the example?

Thanks and Regards
Stefan

Hello Tom,
maybe we have some misconception.
Your reply: "you can use dbms_xplan to see the explain plan (or autotrace), that is the output of the optimizer."

I don't mean the explain plan. I mean how can i determine how oracle transforms/rewrites the query "internally"?

In the example from above

-> Original Query:
============================================
SELECT a.* FROM (
SELECT
TO_NUMBER(TRIM(CITY_ZIP_START_CD)) SCD,TO_NUMBER(TRIM(CITY_ZIP_END_CD)) ECD
FROM TB_CMA086_US_CITY
WHERE
DECODE((REPLACE(TRANSLATE(TRIM(CITY_ZIP_START_CD),'0123456789','00000000000'),'0
',NULL)), NULL,-9876121254,-12345) = -9876121254) a
WHERE 681 >= SCD AND 681 <= ECD;
============================================

-> Optimized (rewritten) Query:
============================================
SELECT TO_NUMBER(TRIM(CITY_ZIP_START_CD)) SCD,
TO_NUMBER(TRIM(CITY_ZIP_END_CD)) ECD
FROM TB_CMA086_US_CITY
WHERE DECODE
( (REPLACE(TRANSLATE(TRIM CITY_ZIP_START_CD),'0123456789','00000000000'),'0'
,NULL)), NULL, -9876121254, -12345 ) = -9876121254
AND 681 >= TO_NUMBER(TRIM(CITY_ZIP_START_CD))
AND 681 <= TO_NUMBER(TRIM(CITY_ZIP_END_CD))
AND 681 <= TO_NUMBER(TRIM(CITY_ZIP_END_CD))
============================================

In the explain plan i see the execution plan and the predicates/filter, but not how the query is "rewritten/optimized" internally. That was my original question.

Thanks and Regards
Stefan

ops$tkyte%ORA10GR2> select * from scott.emp where to_char(empno) = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER(TO_CHAR("EMPNO"))=1)

ops$tkyte%ORA10GR2> select * from scott.emp where 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3896240783

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    37 |     0   (0)|
|*  1 |  FILTER            |      |       |       |            |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

PARSING IN CURSOR #8 len=143 dep=0 uid=21 oct=3 lid=21 tim=46910285378560 hv=3722569340 ad='18fd6610'
select PYMT_RATIO into :b0  from LOP_DET where CARD_NUM between (SUBSTR(:b1,1,(length(:b1)-2))||'00') and (SUBSTR(:b1,1,(length(:b1)-2))||'99')
END OF STMT
PARSE #8:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=46910285378560
BINDS #8:
 bind 0: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=128 offset=0
   bfp=0006faf8 bln=32 avl=18 flg=05
   value="36559002743006"
 bind 1: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=32
   bfp=0006fb18 bln=32 avl=18 flg=01
   value="36559002743006"
 bind 2: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=64
   bfp=0006fb38 bln=32 avl=18 flg=01
   value="36559002743006"
 bind 3: dty=1 mxl=32(18) mal=00 scl=00 pre=00 oacflg=20 oacfl2=1 size=0 offset=96
   bfp=0006fb58 bln=32 avl=18 flg=01
   value="36559002743006"
EXEC #8:c=1008,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=46910285379584
ERROR #8:err=1722 tim=1447529935

LOP_DET column Details
CARD_NUM NUMBER(16)
PYMT_RATIO VARCHAR2(40)

From the trace I am not able to find the rootcause for the "Invalid number" error. Table contains only valid data. Bind variable value also valid.

Implicit conversion happens but why we are getting "Invalid Number" error. Please help to identify the problem.

ops$tkyte%ORA9IR2> create table lop_det( pymt_ratio varchar2(40), card_num number(16) );

Table created.

ops$tkyte%ORA9IR2> insert into lop_det values ( 'x', 36559002743006 );

1 row created.

ops$tkyte%ORA9IR2> begin
  2  :b1 := '36559002743006';
  3  :b2 := '36559002743006';
  4  end;
  5  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> begin
  2  select PYMT_RATIO
  3    into :b0
  4    from LOP_DET where CARD_NUM between (SUBSTR(:b1,1,(length(:b1)-2))||'00') and (SUBSTR(:b1,1,(length(:b1)-2))||'99');
  5  end;
  6  /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at line 2


ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select to_number(PYMT_RATIO )
  2    from LOP_DET where CARD_NUM between (SUBSTR(:b1,1,(length(:b1)-2))||'00') and (SUBSTR(:b1,1,(length(:b1)-2))||'99');
select to_number(PYMT_RATIO )
       *
ERROR at line 1:
ORA-01722: invalid number

Thanks Tom for helping out to identify the problem.

The issue is not with PYMT_RATIO field (:b0) as it is declared as varchar in Pro*C program.

Problem is with bind variable :b1 which is declared as char[18] in the program whereas CARD_NUM in table is number(16)
We have 2 types of card number 1) with 14 digit and another with 16 digit. We faced this problem when input value is 14 digit.

Able to simulate in SQL Plus. Learned from another mistake on usage of char.

create table lop_det( pymt_ratio varchar2(40), card_num number(16) );

insert into lop_det values ( 'x', 36559002743007 );

var b0 varchar2(51);
var b1 char(18);
var b2 char(18);

begin
:b0 := null;
:b1 := '36559002743007';
:b2 := '36559002743007';
end;
/

begin
select PYMT_RATIO
into :b0
from LOP_DET where CARD_NUM between (SUBSTR(:b1,1,(length(:b1)-2))||'00') and 
(SUBSTR(:b1,1,(length(:b1)-2))||'99');
end;
/

ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 2

----------------------

var b0 varchar2(51);
var b1 varchar2(18);
var b2 varchar2(18);

begin
:b0 := null;
:b1 := '36559002743007';
:b2 := '36559002743007';
end;
/

begin
select PYMT_RATIO
into :b0
from LOP_DET where CARD_NUM between (SUBSTR(:b1,1,(length(:b1)-2))||'00') and 
(SUBSTR(:b1,1,(length(:b1)-2))||'99');
end;
/

PL/SQL procedure successfully completed.

If I change :b1 from CHAR to VARCHAR it works fine. But why in trace file bind variable is not showing the value with space?

I'm using Oracle XE on Linux.
I have the following query (the query is obviously not meaningful, it is derived from a more complex query returning the same error):

SELECT 1 p
FROM
(SELECT a.CURR3, a.P
FROM (SELECT 'kkk' P, 'USD' CURR3, 1 D FROM v#F_ACCOUNTANT_BILLS Bx) a
GROUP BY cube ( a.CURR3, a.P, a.D )
) q
WHERE q.p != 'FFF'

or

SELECT a.CURR3, a.P
FROM (SELECT 'kkk' P, 'USD' CURR3, 1 D FROM v#F_ACCOUNTANT_BILLS Bx) a
GROUP BY cube ( a.CURR3, a.P, a.d )
having a.p != 'FFF'

returns:
ORA-01722: invalid number
01722. 00000 - "invalid number"

but this:

SELECT 1 p
FROM
(SELECT a.CURR3, a.P
FROM (SELECT 'kkk' P, 'USD' CURR3, 1 D FROM v#F_ACCOUNTANT_BILLS Bx) a
GROUP BY cube ( a.CURR3, a.P, a.D )
) q

and this:

SELECT 1 p
FROM
(SELECT a.CURR3, a.P
FROM (SELECT 'kkk' P, 'USD' CURR3, 1 D FROM v#F_ACCOUNTANT_BILLS Bx) a
GROUP BY cube ( a.CURR3, a.P )
) q
WHERE q.p != 'FFF'

work.

If I replace v#F_ACCOUNTANT_BILLS with the base table, it works as well.

v#F_ACCOUNTANT_BILLS is a complex view, but "select * v#F_ACCOUNTANT_BILLS" runs without problem returning all the columns and rows.

I understand the problem of using string for number, and I do not think I do that.

Could you give me any advice on this, what this issue can be?

Thank you for your time,
Tibor

I have 3 env - "DEV", "STG", "PRD"
all three environments have same table structure and same data in master tables.
However i identify the line which gives me the "ORA-01722: invalid number" error. I also get to understand that its the datatype of the column which gives me the error.
I have a column which stores numbers and I have given the column data type as "varchar2". Which is wrong. I have changed the datatype and it doesn't give me the error anymore.
What I want to understand is, the data type is same in all the three environments. But why the error encountered only on PRD and not on DEV or STG.

Please find below the table structure which was there earlier

-- Create table
create table ODS_ADM.PIPELN_TIME_SECURE
(
time_to_secure_id NUMBER(10) not null,
time_to_secure VARCHAR2(30),
cre_dttm DATE,
cre_by_nm VARCHAR2(60),
updt_dttm DATE,
updt_by_nm VARCHAR2(60)
);

insert into table --
insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (1, '30', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (2, '60', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (3, '90', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (4, '120', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (5, '180', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

insert into ODS_ADM.PIPELN_TIME_SECURE (TIME_TO_SECURE_ID, TIME_TO_SECURE, CRE_DTTM, CRE_BY_NM, UPDT_DTTM, UPDT_BY_NM)
values (0, '-- Select --', to_date('08-08-2017 06:10:00', 'dd-mm-yyyy hh24:mi:ss'), 'APPS_ADMIN', null, null);

The query which I am running in three environments is -
select v.*,sp.dirtr_email_addr_ln,sp.vp_email_addr_ln,
CASE WHEN lkp.trff_mstr_flg = 'Y' THEN 'N'
WHEN lkp.sales_area IS NOT NULL THEN 'C'
WHEN lkp.cust_acct_src_cd IS NOT NULL THEN 'G'
ELSE
'P' END Account_type
from (select p.*,p.opportunity_start_date + (t.time_to_secure - 1) opportunity_end_dt,
p.opportunity_start_date + (t.time_to_secure - 1) + 30 grace_period,t.time_to_secure,
k.stage_name from ods_adm.pipeln_oprtnty_data p
INNER JOIN (SELECT * FROM (SELECT h.*, row_number() over (partition by h.opportunity_id order by h.opportunity_nxt_date asc) rn
FROM ODS_ADM.PIPELINE_FYI_LIST h
WHERE h.cre_by_nm = 'swetha.kotagandla@xyz.com' ) WHERE rn = 1
) z ON z.opportunity_id = p.opportunity_id and z.opportunity_nxt_date <= TRUNC(SYSDATE)
INNER JOIN ODS_ADM.PIPELN_STAGE_DATA k ON k.stage_id = p.stage_id
INNER JOIN ods_Adm.Pipeln_Time_Secure t on t.time_to_secure_id = p.time_to_secure_id
where p.cre_by_nm = 'swetha.kotagandla@xyz.com'
and p.service_id not in (2,3,7)
and p.account_cd is not null
and p.opportunity_start_date + (t.time_to_secure - 1) >= trunc(sysdate)
AND p.opportunity_id NOT IN (select g.opportunity_id from ods_adm.sales_pipeline_tasks g WHERE g.task_typ = 'R' and g.active_flg = 'Y')
)v
LEFT OUTER JOIN ODS_ADM.CRM_CUST_LOOKUP lkp ON trim(lkp.cust_acct_src_cd) = v.account_cd
left outer join ods_adm.sale_cntct_profile sp on sp.sale_cntct_email_addr_ln = v.cre_by_nm;



Issue in line -- and p.opportunity_start_date + (t.time_to_secure - 1) >= trunc(sysdate)


Please let me know if you require more information.

with nums as ( 
  select /*+ no_merge */* from PIPELN_TIME_SECURE
  where  regexp_like (time_to_secure, '^[0-9]*$' )
)
  select * from nums ...

1) the data is same
2) execution plan is different. The PRD takes the full table scan of this PIPELN_TIME_SECURE table first whereas the DEV and STG takes table acces by index rowid of pipeln_oprtnty_data table first.

Is it taking based on cost and cardinality?

The solution is pretty clear. I have already fixed this by removing the --Select-- from the table and modified the column to number.

My query is different behavior in databases..

Hello Team,

In our code, we have a dynamic IN-list which has number (datatype) values in it.
We want to insert the IN-list values in a GTT because if the number of values in the IN-list exceeds 1000, we get error:
ora-01795 maximum number of expressions in a list is 1000

We are currently debugging the following code

CREATE GLOBAL TEMPORARY TABLE in_list_data (
element VARCHAR2(4000)
);

Next, create a stored procedure to populate the temporary table.

CREATE OR REPLACE PROCEDURE setup_in_list (p_in_list IN VARCHAR2) AS
l_text VARCHAR2(32767) := p_in_list || ',';
l_idx NUMBER;
l_element VARCHAR2(32767);
BEGIN
DELETE FROM in_list_data;

LOOP
l_idx := INSTR(l_text, ',');
EXIT WHEN NVL(l_idx, 0) = 0;
l_element := TRIM(SUBSTR(l_text, 1, l_idx - 1));
l_text := SUBSTR(l_text, l_idx + 1);

INSERT INTO in_list_data (element)
VALUES (l_element);
END LOOP;
END;
/

With this code, we are able to insert our data into GTT in_list_data.element1 column which is of type VARCHAR.

However, in the actual code, we have IN-list with NUMBER values.

If we try to convert VARCHAR to NUMBER using to_number function as follows:
INSERT INTO in_list_data (element)
VALUES to_number(l_element);

Using to_number or CAST in above statement gives us error:
ora 03001 unimplemented feature
while compiling procedure

If we try to use to_number or CAST in our code to convert VARCHAR to NUMBER then we get error:
ora-01722 invalid number

Can you please suggest any suitable workaround for this problem?

Thank you.

Sincerely,
Durga

SQL> create table t ( x number );

Table created.

SQL> variable c varchar2(10)
SQL> insert into t values to_number(:c);
insert into t values to_number(:c)
                     *
ERROR at line 1:
ORA-03001: unimplemented feature


SQL> insert into t values ( to_number(:c) ) ;

1 row created.

Thank you very much for pointing that out.
I really appreciate the great help you are providing to all Oracle users.

Thanks again Sir.

On C# to avoid the error invalid number put after the open command

comando.Connection.Open();
OracleGlobalization info = comando.Connection.GetSessionInfo();
info.NumericCharacters = ".,";
comando.Connection.SetSessionInfo(info);
comando.ExecuteNonQuery();
comando.Connection.Close();