oracleplsqloracle-apexoracle-rest-data-services

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


select descp, id from table1

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

enter image description here

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.


Solution

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