Returning table rows as JSON

user9037110

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;

thatJeffSmith-Oracle

My blog has a search feature and a resource page for REST APIs, but here you go

https://www.thatjeffsmith.com/archive/2021/03/four-options-for-working-with-ords-plsql-refcursors/

thatJeffSmith-Oracle

why would you need to do this?

Just create a SQL handler that runs select * From job_history, ords builds the json array for you.

user9037110

I created the handler using SQL Developer so I thought I had to fill something in the worksheet tab:

user9037110

Also - I wanted to just understand the basics. In my 'real' procedure I'll be processing the incoming JSON payload and performing varying selects/joins based on what's received.

thatJeffSmith-Oracle

if your procedure returns a refcursor, ORDS will auto turn that into a JSON doc as well...

user9037110

are there any examples of a handler doing this?

thatJeffSmith-Oracle

On my blog, yes (https://www.thatjeffsmith.com)

user9037110

can you point me to a specific one please.

thatJeffSmith-Oracle

My blog has a search feature and a resource page for REST APIs, but here you go

https://www.thatjeffsmith.com/archive/2021/03/four-options-for-working-with-ords-plsql-refcursors/

user9037110

Thanks Jeff!