GET PL/SQL Example (Retrieve)

Description : Basic GET example with option for subsstring search
Method : GET
Source Type : PL/SQL
Returns : 200 for normal completion, ORDS generated messages if error

DECLARE
    c sys_refcursor;
BEGIN
  IF :SEARCH_NAME IS NOT NULL THEN
    -- Return Matching ID
    OPEN c FOR SELECT *
                 FROM EMP 
                WHERE UPPER(TRIM(EMP.ENAME)) LIKE '%'||UPPER(TRIM(:SEARCH_NAME))||'%';
    apex_json.write(c);
  ELSE
    -- No Search Criteria, Return All
    OPEN c FOR SELECT *
                 FROM EMP;
    apex_json.write(c);
  END IF;
END;

Testing

GET https://test.com/ords/apxdev/inventory/test/demo
[
  {
    "EMPNO": 7698,
    "ENAME": "BLAKE",
    "JOB": "MANAGER",
    "MGR": 7839,
    "HIREDATE": "1981-05-01T00:00:00Z",
    "SAL": 2850,
    "DEPTNO": 30
  },
  {
    "EMPNO": 7839,
    "ENAME": "KING",
    "JOB": "PRESIDENT",
    "HIREDATE": "1981-11-17T00:00:00Z",
    "SAL": 5000,
    "DEPTNO": 10
  }
]


GET https://test.com/ords/apxdev/inventory/test/demo?SEARCH_NAME=KING
[
  {
    "EMPNO": 7839,
    "ENAME": "KING",
    "JOB": "PRESIDENT",
    "HIREDATE": "1981-11-17T00:00:00Z",
    "SAL": 5000,
    "DEPTNO": 10
  }
]


GET https://test.com/ords/apxdev/inventory/test/demo?SEARCH_NAME=JUNKSTRING
[
]