CODEWITHSUNDEEP
sqloracle11goracle-apex
Bishan
Bishan

Reputation: 15702

SQL IN Function Issue

I have created a page item as P16_X with values 998,785,523 in my oracle apex app. Then i have created a Classic Report in my apex page and added below code as Region Source.

  select 
   sale_date,
   invoice_number , 
   sale_amount from sale
    where sale_id in (:P16_X);

when i load my page i got below error.

  report error:
  ORA-01722: invalid number

How could i solve this ?

Upvotes: 3

Views: 169

Answers (3)

Mathias Magnusson
Mathias Magnusson

Reputation: 197

Concatenating user input directly into your SQL is asking for SQL injection issues. Take a look at apex_util.string_to_table instead, it allows you to convert a CSV to an array usable in an IN construct.

Upvotes: 0

valex
valex

Reputation: 24134

Try this 

  select 
   sale_date,
   invoice_number , 
   sale_amount from sale
    where ','||:P16_X||',' like '%,'||sale_id||',%'

PS: when you send this parameter into the query oracle takes it as a string "998,785,523" so your original query fails because of that. In this query we get:

','||'998,785,523'||',' like '%,'||sale_id||',%'

it is equivalent to:

',998,785,523,' like '%,'||sale_id||',%'

for example for sale_id=523 it is converted into:

',998,785,523,' like '%,523,%'

and it's TRUE for this sale_id.

Upvotes: 1

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

No idea about APEX but the issue is Oracle treats p16 as number but '998,785,523' looks for him as a string so by my opinion you should either add in any temporarty table these 3 values and use select from in your IN clause or parse '998,785,523' and use it inIN clause as well or create your select statement using dinamic sql. I vote for my first suggestion.

Upvotes: 0

Related Questions