How to write pl/sql source in get restful service handler in oracle apex?

Question

Above one is working perfectly in oracle apex. But I want to execute different query based on the id.

Please suggest me the correct way. I think the screenshots are enough to present my requirements. If need any information please let me know. Thanks in advance.

Answer 1

with pl/sql as source type, the output needs to be generated by the pl/sql procedure (using htp or apex_json). with any "Query" source type, ORDS converts the resultset to json automatically. However, looking at your example, I doubt you want pl/sql. It can be done with sql and that will be a lot easier to implement. Here is an example of the source of GET handler on the EMP sample table. For KING (id 7839) another output is generated:

select 'The Boss' as nickname, ename from emp where empno = :id AND ename = 'KING'
union
select 'Regular employee' as nickname, ename from emp where empno = :id AND ename != 'KING'

If you need pl/sql then here is a simple example:

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "empno",
           e.ename AS "employee_name",
           e.job AS "job",
           e.mgr AS "mgr",
           TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
           e.sal AS "sal",
           e.comm  AS "comm",
           e.deptno AS "deptno"
    FROM   emp e;

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;
END;

Make sure to set the handler "Pagination Size" attribute to an integer value.

There should be plenty of more complex examples available in the web on this. Check the oracle-base site, Jon Dixons' blog and of course Jeff Smith.