1 ) I have to make json from oracle select query which has three approach i can follow .
SELECT JSON_ARRAY(json_object('id' VALUE employee_id,
'data_clob' VALUE data_clob
)) from tablename;
also i have tried with this approach
2) If you are unable to patch/work with that version there is an excellent package written by Lewis Cunningham and Jonas Krogsboell: PL/JSON * http://pljson.sourceforge.net/
It's an excellent package (I have used it in numerous database installations).
The examples included are good and cover most scenarios.
declare
ret json;
begin
ret := json_dyn.executeObject('select * from tab');
ret.print;
end;
/
Mention In this answer too but not work for such big clob. Return results of a sql query as JSON in oracle 12c
3) The other approach can be we can concatenate the string after the select query.
FOR rec IN (SELECT employee_id, data_clob
FROM tablename) LOOP
IF i <> 1 THEN
v_result := v_result || ',';
END IF;
v_result := v_result || '{"employee_id":' || to_char(rec.employee_id) || ',"data_clob": ' || rec.data_clob || '}';
i := i + 1;
END LOOP;
v_result := v_result || ']}';
3 approach solve my problem but i don't want to run for loop . Is there is any solution in oracle to handle this .
I check for solution but that don't work without for loop.
https://technology.amis.nl/2015/03/13/using-an-aggregation-function-to-query-a-json-string-straight-from-sql/
url has provide some solution , i tried this but not working .Same issue is coming.
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 57416, maximum: 4000)
Could you able to tell me how it can be done ?
In 12.2 json_* functions handle clobs fine. Use clause Returning clob
create table t( c clob, constraint t_chk check (c is json));
declare
v_clob clob;
begin
for i in 1..10000 loop
v_clob := v_clob || 'asdasdadasdasdasdasdasdasdasd';
end loop;
insert into t(c)
select
json_object
(
'body' value v_clob returning clob
)
from
dual;
end;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With