ORA-40474: INVALID UTF-8 BYTE SEQUENCE IN JSON DATA (Doc ID 2642071.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On a 12.2.0.1 database

When attempting to process json formatted data in a file named json.in.txt 

declare
  l_json json_object_t;
  function read_blob(p_directory in varchar2, p_file_name in varchar2) return blob is
  l_bf bfile;
  l_bc blob;
  l_o1 number := 1;
  l_o2 number := 1;
  begin
  l_bf := bfilename(p_directory, p_file_name);
  dbms_lob.fileopen(l_bf, dbms_lob.file_readonly);
  dbms_lob.createtemporary(l_bc, true, dbms_lob.call);
  dbms_lob.loadblobfromfile(l_bc, l_bf, sys.dbms_lob.lobmaxsize, l_o1, l_o2);
  dbms_lob.fileclose(l_bf);
  return l_bc;
  end;
  procedure write_blob(p_file in blob, p_directory in varchar2, p_file_name in varchar2) is
  l_file sys.utl_file.file_type;
  l_buffer raw(8192);
  l_amount pls_integer := 8192;
  l_pos pls_integer := 1;
  l_file_length pls_integer;
  begin
  l_file_length := sys.dbms_lob.getlength(p_file);
  l_file := sys.utl_file.fopen(p_directory, p_file_name, 'wb', l_amount);
  while l_pos < l_file_length loop
  sys.dbms_lob.read(p_file, l_amount, l_pos, l_buffer);
  sys.utl_file.put_raw(l_file, l_buffer, true);
  l_pos := l_pos + l_amount;
  end loop;
  sys.utl_file.fclose(l_file);
  exception when others then
  if sys.utl_file.is_open(l_file) then
  sys.utl_file.fclose(l_file);
  end if;
  raise;
  end;
begin
  l_json := json_object_t.parse(read_blob('EXP_DMP', 'json.in.txt'));
  write_blob(l_json.to_blob, 'EXP_DMP', 'json.out.txt');
end;
/

the following error occurs.

ERROR
-----------------------
ORA-40474: invalid UTF-8 byte sequence in JSON data
ORA-06512: at "SYS.JDOM_T", line 53
ORA-06512: at "SYS.JSON_ELEMENT_T", line 76
ORA-06512: at line 39

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.