I m executing my procedure on version of 9.2.0.4 on IBM-AIX server sometimes it is giving me ora-01722 invalid number while bulk collect. I have gone through all work arounds it shows me cause of this error because of conversion of character to number but I am not doing any type conversion.
I have attached my procedure with this thread.
Please help me regard this issue. Thanks in advance
I am saying your data contains characters, are you sure your data comes clean?
Mr. Pando in my temp table i am storing data in varchar2 format whether it comes number or string but error comes when it goes for bulk collection, whatever datatype of my procedure's variables has been selected in that query is same as table's datatype.
i.e. DUAL_IMSI_FLAG varchar2(20) contains numeric value.In my procedure i have declare it like below.
TYPE s19 IS TABLE OF customer_rec.DUAL_IMSI_FLAG%TYPE INDEX BY BINARY_INTEGER;
V_DUAL_IMSI_FLAG S19;
if it is issue of numeric value to be inserted/selected in varchar2 value than it comes when it is inserting record. but it is showing me when i m selecting it.i m not changing its datatype in my procedure.
are you sure all the data you have is correct and there are no mixture of varchar and number?
Look
Code:
create table x (a varchar2(4))
Table created.
insert into x values(1);
1 row created.
insert into x values(2);
1 row created.
create table y (a number);
Table created.
insert into y values(1);
1 row created.
insert into y values(2);
1 row created.
commit;
Commit complete.
select *
from x, y
where x.a = y.a;
A A
---- ----------
1 1
2 2
insert into x values('a');
1 row created.
select *
from x, y
where x.a = y.a;
where x.a = y.a
*
ERROR at line 3:
ORA-01722: invalid number
select sum(a) from x;
select sum(a) from x
*
ERROR at line 1:
ORA-01722: invalid number