is_valid_number bug in plsql compiler setting 3

OraOpenSource / oos-utils

Common PL/SQL utility scripts

MIT License

203 stars 73 forks source link

is_valid_number bug in plsql compiler setting 3 #186

Open martindsouza opened 6 years ago

martindsouza commented 6 years ago

Told to me by @ragFOEX. Peter can you please provide more information?

Issue seems to be that since we're not using the variable the compiler ignores it.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3
ALTER SESSION SET PLSQL_CODE_TYPE      = 'NATIVE'

ragFOEX commented 6 years ago

I reported this on AskTom a while ago: https://asktom.oracle.com/pls/apex/asktom.search?tag=plsql-optimize-level-3-produces-wrong-function-result

Basically PLSQL_OPTIMIZE_LEVEL = 3 strips away the is_valid_x function and returns always TRUE, because the converted result (the number/date variable) is never used.