0

I have an odd scenario. On an oracle 11.2 db there is one value that when selected into a table type causes and invalid data type error when the table type is used. I have validated that when the row is excluded everything else works fine.

Pseudo code;

type my_nums is table of number;

select num bulk collect into my_nums from tableA;

select t.my_col from tableB t where t.my_col IN (select column_value from table(my_nums));

I have checked this one key from tableA is a numeric using;

with t as (select to_char(num) as txt from tableA where num = 33)
select txt, case when regexp_like(txt, '^-?[[:digit:],.]*$') then 'Numeric' else 'Non-Numeric' end as type
FROM t;

Taken from How to check if a field is numeric. Is there something else I can look at to find out why this is happening?

To be clear, using the following, all is well in my procedure.

select num bulk collect into my_nums from tableA where num != 33;

Thanks in advance.

APC
  • 144,005
  • 19
  • 170
  • 281
cbm64
  • 1,059
  • 2
  • 12
  • 24
  • Seems odd, but what is the type of the num column in tableA? If it is not number you should use to_number(num) for an explicit conversion when you copy to my_nums. What happens if you do that? – ewramner Apr 01 '19 at 10:38
  • The answerer in that link clearly mentions that it may match a string such as `1,3,4,5,6.23 ` numeric. Use [this](https://stackoverflow.com/a/28819814/7998591) method instead – Kaushik Nayak Apr 01 '19 at 10:45
  • 1
    my_nums is type, you use it as a variable. – Seyran Apr 01 '19 at 10:47
  • Also, what is the datatype of tableb.my_col? If that's not a number, I could see you might get that error, since Oracle converts more general datatypes to more specific datatypes, e.g. comparing a string to a number, Oracle would attempt to convert the string into a number, rather than the number into a string. If there are non-numbers in that string, you'd get the invalid data type error – Boneist Apr 01 '19 at 10:47
  • According the schema it is a number and every other row in the table works perfectly well. I put an explicit to_number is but that does not work either. I checked again with the to_number excluding the value 33 and again it works. But either explicitly select 33 or included with other numbers it fails. – cbm64 Apr 01 '19 at 10:48
  • @Boneist interesting I didn't consider that tableB might be the issue. – cbm64 Apr 01 '19 at 10:55
  • tableB has a column type of Number – cbm64 Apr 01 '19 at 11:01
  • And one more adding to my comment, if your `my_nums` table type wasn't declared at schema level you will get an error in the second statement where you trying to use it in SQL `in` statement, there must a variable also. – Seyran Apr 01 '19 at 11:09
  • 2
    Can you post actual code and data that causes the error (is it really ORA-00902?) - i.e. an MCVE? Your pseudocode is slightly confusing and might be simplified so much it hides something important. And can you also add the result of `select dump(num) from tablea where num = 33`, just for fun...? – Alex Poole Apr 01 '19 at 11:09
  • I can't post the actual code...it is closed source. This code is in a package, so it should not need schema level objects. Like I say it works if this one value is excluded. I did the dump on both columns from both tables and they are the same Typ=2 Len=2: 193,34 – cbm64 Apr 01 '19 at 11:39
  • 4
    @cbm64 - the dump rules out data corruption then. But this is still confusing - you say that "it should not need schema level objects" but in 11.2 your code will get an "ORA-22905: cannot access rows from a non-nested table item" compilation error because of the `from table(my_nums)` part. An MCVE doesn't need to be your real code, just something that replicates the problem. – Alex Poole Apr 01 '19 at 11:49

0 Answers0