SQL input parameters throw ORA-01722: invalid number

0

I am introducing two input parameters "TYPE_CODE" and "KEY" to my SQL query.

But when I add the following where clause, it pops out the ORA-01722: invalid number error.

WHERE
 
(($P{TYPE_CODE} = 'C' AND table1.key IN (SELECT key2 FROM table2 WHERE key2 = $P{KEY}))
 
OR
 
($P{TYPE_CODE} = 'P' AND table1.key IN (SELECT key3 FROM table3 WHERE key3 = $P{KEY}))

The SQL works fine without the input parameters in the where clause.

Thank you everyone in advance.

cyyung1023's picture
Joined: Aug 2 2020 - 7:56pm
Last seen: 2 days 4 hours ago

2 Answers:

0

Hi cyyung1023,

 

If it is ok without the parameters did you rechecked the type of your parameter $P{KEY} ?

It looks like the $P{KEY} parameter is not passing a number value with this Oracle error.

Let us know,

Regards,

Yann

Yann22300's picture
Joined: Apr 29 2015 - 1:10am
Last seen: 1 week 3 days ago
0

Thanks, Yann22300. The parameter $P{KEY} was indeed not a numerical value.

cyyung1023's picture
Joined: Aug 2 2020 - 7:56pm
Last seen: 2 days 4 hours ago
Feedback
randomness