I have a very long json-String with over 49k characters, so it doesn't fit in a clob. I have a procedure that has the json-String as a clob input. There I want to read out all values and insert them in my table. If I make the json-String smaller for testing everything works fine. But as I said, the json-String is over 49k charachters long.
The head of my Procedure looks like this:
PROCEDURE set_FDC_Data(in_json IN CLOB) IS
v_json elmos_opt.json;
v_json_keys elmos_opt.json_list;
v_json_else_obj elmos_opt.json;
v_elseNr VARCHAR(10);
v_pce VARCHAR(10);
v_channel1 elmos_opt.json;
v_protocolCh1 VARCHAR(10);
v_portCh1 NUMBER;
v_deviceIdCh1 NUMBER;
v_t3Ch1 NUMBER;
v_t1Ch1 NUMBER;
v_t2Ch1 NUMBER;
v_t4Ch1 NUMBER;
v_t5Ch1 NUMBER;
v_t6Ch1 NUMBER;
v_t7Ch1 NUMBER;
v_t8Ch1 NUMBER;
v_channel2 elmos_opt.json;
v_remoteHostCh2 VARCHAR(50);
v_protocolCh2 VARCHAR(10);
v_portCh2 NUMBER;
v_deviceIdCh2 NUMBER;
v_t3Ch2 NUMBER;
v_t1Ch2 NUMBER;
v_t2Ch2 NUMBER;
v_t4Ch2 NUMBER;
v_t5Ch2 NUMBER;
v_t6Ch2 NUMBER;
v_t7Ch2 NUMBER;
v_t8Ch2 NUMBER;
v_channel3 elmos_opt.json;
v_deviceIdCh3 NUMBER;
v_t3Ch3 NUMBER;
v_t1Ch3 NUMBER;
v_t2Ch3 NUMBER;
v_t4Ch3 NUMBER;
v_t5Ch3 NUMBER;
v_t6Ch3 NUMBER;
v_t7Ch3 NUMBER;
v_t8Ch3 NUMBER;
v_channel4 elmos_opt.json;
v_deviceIdCh4 NUMBER;
v_t3Ch4 NUMBER;
v_t1Ch4 NUMBER;
v_t2Ch4 NUMBER;
v_t4Ch4 NUMBER;
v_t5Ch4 NUMBER;
v_t6Ch4 NUMBER;
v_t7Ch4 NUMBER;
v_t8Ch4 NUMBER;
v_deviceIdHost NUMBER;
v_deviceIdEqp NUMBER;
v_deviceIdEqpBis NUMBER;
BEGIN
v_json := elmos_opt.json(in_json);
v_json_keys := v_json.get_keys();
FOR i IN 1 .. v_json_keys.count LOOP
v_elseNr := v_json_keys.get(i).get_string;
v_json_else_obj := elmos_opt.json(v_json.get(v_elseNr));
v_pce := v_json_else_obj.get('PCE').get_string;
v_channel1 := elmos_opt.json(v_json_else_obj.get('CHANNEL1'));
v_protocolCh1 := v_channel1.get('PROTOCOL').get_string;
v_portCh1 := v_channel1.get('PORT').get_number;
v_deviceIdCh1 := v_channel1.get('DEVICE_ID').get_number;
v_t3Ch1 := v_channel1.get('T3_TIMEOUT').get_number;
v_t1Ch1 := v_channel1.get('T1_TIMEOUT').get_number;
v_t2Ch1 := v_channel1.get('T2_TIMEOUT').get_number;
v_t4Ch1 := v_channel1.get('T4_TIMEOUT').get_number;
v_t5Ch1 := v_channel1.get('T5_TIMEOUT').get_number;
v_t6Ch1 := v_channel1.get('T6_TIMEOUT').get_number;
v_t7Ch1 := v_channel1.get('T7_TIMEOUT').get_number;
v_t8Ch1 := v_channel1.get('T8_TIMEOUT').get_number;
v_channel2 := elmos_opt.json(v_json_else_obj.get('CHANNEL2'));
v_remoteHostCh2 := v_channel2.get('REMOTE_HOST').get_string;
v_protocolCh2 := v_channel2.get('PROTOCOL').get_string;
v_portCh2 := v_channel2.get('PORT').get_number;
v_deviceIdCh2 := v_channel2.get('DEVICE_ID').get_number;
v_t3Ch2 := v_channel2.get('T3_TIMEOUT').get_number;
v_t1Ch2 := v_channel2.get('T1_TIMEOUT').get_number;
v_t2Ch2 := v_channel2.get('T2_TIMEOUT').get_number;
v_t4Ch2 := v_channel2.get('T4_TIMEOUT').get_number;
v_t5Ch2 := v_channel2.get('T5_TIMEOUT').get_number;
v_t6Ch2 := v_channel2.get('T6_TIMEOUT').get_number;
v_t7Ch2 := v_channel2.get('T7_TIMEOUT').get_number;
v_t8Ch2 := v_channel2.get('T8_TIMEOUT').get_number;
v_channel3 := elmos_opt.json(v_json_else_obj.get('CHANNEL3'));
v_deviceIdCh3 := v_channel3.get('DEVICE_ID').get_number;
v_t3Ch3 := v_channel3.get('T3_TIMEOUT').get_number;
v_t1Ch3 := v_channel3.get('T1_TIMEOUT').get_number;
v_t2Ch3 := v_channel3.get('T2_TIMEOUT').get_number;
v_t4Ch3 := v_channel3.get('T4_TIMEOUT').get_number;
v_t5Ch3 := v_channel3.get('T5_TIMEOUT').get_number;
v_t6Ch3 := v_channel3.get('T6_TIMEOUT').get_number;
v_t7Ch3 := v_channel3.get('T7_TIMEOUT').get_number;
v_t8Ch3 := v_channel3.get('T8_TIMEOUT').get_number;
v_channel4 := elmos_opt.json(v_json_else_obj.get('CHANNEL4'));
v_deviceIdCh4 := v_channel4.get('DEVICE_ID').get_number;
v_t3Ch4 := v_channel4.get('T3_TIMEOUT').get_number;
v_t1Ch4 := v_channel4.get('T1_TIMEOUT').get_number;
v_t2Ch4 := v_channel4.get('T2_TIMEOUT').get_number;
v_t4Ch4 := v_channel4.get('T4_TIMEOUT').get_number;
v_t5Ch4 := v_channel4.get('T5_TIMEOUT').get_number;
v_t6Ch4 := v_channel4.get('T6_TIMEOUT').get_number;
v_t7Ch4 := v_channel4.get('T7_TIMEOUT').get_number;
v_t8Ch4 := v_channel4.get('T8_TIMEOUT').get_number;
v_deviceIdHost := v_json_else_obj.get('DEVICE_ID_HOST').get_number;
v_deviceIdEqp := v_json_else_obj.get('DEVICE_ID_EQP').get_number;
IF v_json_else_obj.exist('DEVICE_ID_EQP_BIS') THEN
v_deviceIdEqpBis := v_json_else_obj.get('DEVICE_ID_EQP_BIS').get_number;
END IF;
END LOOP;
END setFDC_DATA;
There was a post where it was suggested that the clob should be divided in chunks, but how if the clob is an input parameter and how can I do it in a procedure?
The error I get is:
PLS-00172: string literal too long
This error is thrown after running my test script to test my procedure:
declare
in_json clob;
begin
-- Call the procedure
in_json := 'very long json String';
EA_JAMIE.set_FDC_Data(in_json => in_json);
end;
How do I split my input json in chunks ? I never worked with clobs and maybe the answers I found for this error are also applyable for my case but I can't figure out how. PL/SQL is also a fairly new thing for me.
The error you are getting is that your literal is too long. A literal can only contain 4000 characters. Literal here means the value you are setting in_json to before calling your procedure. You cannot have this:
in_json := 'some text that is longer than 4000 characters';
See the Oracle documentation about the max length for literals (and other fields) here.
You can cheat by appending small chunks of your literal together into a CLOB and then passing that into your procedure. Here is a dumb example. You would cut your 49k character JSON into 4k character chunks, maybe using NP++ or some text editor to insert a new-line every 4000 characters, then make a variable per chunk, and then append them together. You can probably even ditch the variables and put the literals directly into the to_clob(), but that's going to make this hack even worse (to look at).
DECLARE
v_s1 VARCHAR2(4000) := lpad('x',
4000,
'x');
v_s2 VARCHAR2(4000) := lpad('x',
4000,
'x');
v_s3 VARCHAR2(4000) := lpad('x',
4000,
'x');
v_clob CLOB;
BEGIN
dbms_lob.createtemporary(lob_loc => v_clob,
cache => FALSE);
dbms_lob.append(dest_lob => v_clob,
src_lob => to_clob(v_s1));
dbms_lob.append(dest_lob => v_clob,
src_lob => to_clob(v_s2));
dbms_lob.append(dest_lob => v_clob,
src_lob => to_clob(v_s3));
dbms_output.put_line(dbms_lob.getlength(lob_loc => v_clob));
END;
/
I am assuming (hoping) you are doing this as a unit test and that in_json will eventually come from somewhere else.
Edit: You also might want to pass your CLOB in as IN OUT NOCOPY instead of just IN. This will cause (hopefully) PL/SQL to pass the value by reference instead of making a by value copy of the data saving memory and overhead in your program. You can read about the details of NOCOPY in the Oracle documentation as there are exceptions where it can be ignored.
PROCEDURE set_FDC_Data(in_json IN OUT NOCOPY CLOB) IS