0

I am trying to pass a list of IDs to a REST api (apex ords).

I have a url like this : 

https://apex.oracle.com/***/apex/anime_keeper/ak/getAnimeList/:ids

when I do :

https://apex.oracle.com/***/apex/anime_keeper/ak/getAnimeList/1

I get the item with id = 1 but if I do :

https://apex.oracle.com/***/apex/anime_keeper/ak/getAnimeList/1,2,3

I get a 500 Internal Server Error

How should I format my url so I can use the 1,2,3 list in a where id in (ids) in apex ords?

this is a screenshot of ords if it can help :

enter image description here

Kris Rice
  • 3,300
  • 15
  • 33
fire frost
  • 427
  • 7
  • 23

2 Answers2

3

That SQL won't work becase ORDS does not split the csv values out. So that sql as-is will be checking for id in ( '1,2,3') not id in ( 1,2,3)

There's multiple ways to accomplish what the intent is.

For example, using XMLTABLE

SELECT rownum,object_id
  FROM user_objects
 WHERE rownum IN (
   select (column_value).getstringval() csv_values
        FROM   
     xmltable(('"' || REPLACE(:ids, ',', '","')|| '"'))
 )

There are other ways mentioned here: Using the "IN" clause with a comma delimited string from the output of a replace() function in Oracle SQL

Here's an ORDS REST API doing exactly what you intend. enter image description here

Kris Rice
  • 3,300
  • 15
  • 33
  • I know I shouldn't be asking that but can you help me on that question : https://stackoverflow.com/questions/50455756/how-to-make-a-post-request-with-ords ? – fire frost May 21 '18 at 20:01
0

In a URL, comma ',' has special meaning/purpose. It is to separate query arguments in url e.g

https://test.me/mypage?firstname=jon,lastname=doe,gender=m

So server is throwing 500 error as it finds corrupted or incomplete key/value pairs. It expects key=value pair after each comma. To get around this we need to urlencode value e.g

https://apex.oracle.com/***/apex/anime_keeper/ak/getAnimeList/1%2C2%2C3
Kamran Syed
  • 439
  • 3
  • 7
  • thanks for the answer, I now understand URL encryption, but I still have a 500 error with that new URL, got any idea on the problem? – fire frost May 21 '18 at 16:55