Forum Stats

  • 3,837,110 Users
  • 2,262,228 Discussions
  • 7,900,205 Comments

Discussions

Returning table rows as JSON

Hi,

I'm trying to select rows from the job_history table and returning them as a JSON array. I thought the following should have worked but it's giving me a ORA-01422 () error. What am I doing wrong?


declare

  v_response json_object_t;

  v_dataclob clob;

begin

  SELECT json_object('empid' VALUE employee_id,

          'startdate' VALUE start_date,

          'enddate'  VALUE end_date,

          'job_id'   VALUE job_id,

          'dept'    VALUE department_id ) 

  into v_dataclob

  FROM job_history;

  owa_util.mime_header ('application/json', true); 

  v_response := json_object_t(v_dataclob);

  htp.p(v_response.stringify);

END;

Best Answer

Answers