ORDS, SODA & JSON in the Database

Hi,
We require the same: returning raw json from pl/sql procedure. Did you find a solution?

Hope an amateur like me can help......
To POST json to a clob column we do the following:

INSERT INTO js_table
 (js_column)
VALUES
 (blob_to_clob(:body));

oracle-base.com has a version of the blob_to_clob sql
If you just want to return the json clob, the SELECT-statement User_Z87BQ works for us.
We have pl/sql that build the json by using something like the following:

BEGIN
  APEX_JSON.OPEN_OBJECT;
  APEX_JSON.WRITE('result','success');
  APEX_JSON.WRITE('message','hello world');
  APEX_JSON.CLOSE_OBJECT;
END;

just print it with htp.p

That's brilliant Jeff, thank you!
I'm using:
OWA_UTIL.mime_header('application/json', TRUE);
in order to set the correct content-type. Is this the correct way to go?

For anyone having the same issue: you could simply return the column which holds JSON as a plain JSON type by using a column name like "{}columnname", see:

SELECT id,
       json "{}json"
  FROM table_with_json

ref: https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

For anyone having the same issue: you could simply return the column which holds JSON as a plain JSON type by using a column name like "{}columnname", see:

SELECT id,
       json "{}json"
  FROM table_with_json

ref: https://www.thatjeffsmith.com/archive/2017/09/ords-returning-raw-json/

Create GET handler, type PL/SQL
Some examples of creating output:

begin
  owa_util.status_line(201, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"status": "Item created successfully"}');
end;

--

begin
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"items":[');
  for i in (select rownum as rn, object_Type, object_name, status from all_objects where owner = 'ORDS_METADATA' fetch next 10 rows only) loop
    if i.rn > 1 then
      htp.prn(',');
    end if;
    htp.prn('{"type": "' || i.object_type || '"');
    htp.prn(',"name": "' || i.object_name || '"');
    htp.prn(',"status": "' || i.status || '"}');
  end loop;
  htp.prn(']}');
end;

--

declare
  l_arr json_array_t := json_array_t();
begin
  for i in (select object_Type, object_name, status from all_objects where owner = 'ORDS_METADATA' fetch next 10 rows only) loop
    declare
      l_obj json_object_t := json_object_t();
    begin
      l_obj.put('type', i.object_type);
      l_obj.put('name', i.object_name);
      l_obj.put('status', i.status);
      l_arr.append(l_obj);
    end;
  end loop;
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  apex_util.prn(l_arr.to_clob);
end;

Regards
Oli

And for posting your CLOB, Jeff has already shown how to do a GET media resource.
This is an alternative way to do POST in PL/SQL, I'm assuming it's an array, change as needed:

 begin
  owa_util.status_line(200, '', false);
  owa_util.mime_header('application/json', true);
  htp.prn('{"items":');
  for i in (select jsonclob from mytable where id = :id) loop
    apex_util.prn(i.jsonclob);
  end loop; 
end;

of course under production, this would be a select into with a no_data_found handler