Skip to Main Content

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Returning table rows as JSON

user9037110Jun 30 2022

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;

This post has been answered by thatJeffSmith-Oracle on Jun 30 2022
Jump to Answer

Comments

Post Details

Added on Jun 30 2022
9 comments
363 views