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.
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.