Hello.
Recently, I had to built an ORDS with a GET request. That request was built by a query with a cursor expression as a column. Something like this:
SELECT
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
CURSOR(
SELECT
JOB_ID,
JOB_TITLE
FROM
HR.JOBS
) JOBS
FROM
HR.EMPLOYEES
I'm using the HR schema, to keep the security of my data. I hope you can understand.
This is my handler defined in ORDS
ORDS.DEFINE_HANDLER(
p_module_name => 'hr',
p_pattern => 'employees',
p_method => 'GET',
p_source_type => 'json/collection',
p_items_per_page => 0,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'SELECT
EMPLOYEE_ID,
FIRST_NAME,
LAST_NAME,
CURSOR(
SELECT
JOB_ID,
JOB_TITLE
FROM
HR.JOBS
) JOBS
FROM
HR.EMPLOYEES'
);
At the beginning, the parameter p_items_per_page was setted at 25 as default and the service didn't work, the server code reponse was 500.
When I saw the debug trace, the error was ORA-22902 . So I searched for a solution in the Oracle forums and the solution was set the p_items_per_page , but the user who posted the solution didn't explain the reason for this.
I really need to understand why I have to set the pagination size to zero.
Is an ORDS bug with the CURSOR_EXPRESSION ?
Thank you, for advanced.