How to properly convert to UTF-8 inside JSON_OBJECT?

Question

JSON

Hello, I am fetching data in JSON_OBJECT (PL\SQL).
In the line
c_fullname' VALUE CONVERT(c.t_name, 'UTF8')
Full name of the client (c_fullname) = "Papov Vasya Vasilevia"

DECLARE
  v_test varchar2(32767);
BEGIN        
  
        SELECT JSON_OBJECT(
               'c_cardnum' VALUE c.t_cardnum, 
               'c_fullname' VALUE CONVERT(c.t_name, 'UTF8'),
               'c_startdate' VALUE c.t_start_date,
               'c_enddate' VALUE c.t_end_date,
               'c_phonenum' VALUE c.t_phonenum
                   )           
          INTO v_test
          FROM clients c
         WHERE c.t_id = '10'; 

         dbms_output.put_line(v_test);  

END;

If you write simply
'c_fullname' VALUE c.t_name),
without an envelope, then PL\SQL swears that only UTF-8 is allowed for JSON.

If you write using the convert function
'c_fullname' VALUE CONVERT(c.t_name, 'UTF8'),

As a result, some incomprehensible characters come out

"с_fullname":"¦ФTГTБ¦-¦-TВ¦-¦-¦- ¦Ь¦-TЕ¦-TГ¦-¦- ¦о¦-TГTБ¦-¦¬¦¬¦¦¦-¦-¦-"

Question, How can I correctly convert the fullname field to UTF-8 so that when outputting through Varchar2, the correct characters are displayed and not some kind of crackers ..?

Answer 1

I dug around and still couldn't find a way to convert Cyrillic to UTF-8 in PL\SQL.
As an option, I made a transliteration function from Cyrillic to Latin, so that any characters would not be displayed.
I share:

spoiler

DECLARE

v_str VARCHAR2(100) := 'Василий Пупкин';
v_res VARCHAR2(100) := '';

BEGIN
    -- Строчные буквы
    FOR i IN 1..length( v_str ) LOOP 
    IF SUBSTR( v_str, i, 1 ) = 'а' THEN 
      v_res := v_res || 'a';
    ELSIF SUBSTR( v_str, i, 1 ) = 'б' THEN
      v_res := v_res || 'b';
    ELSIF SUBSTR( v_str, i, 1 ) = 'в' THEN
      v_res := v_res || 'v';
    ELSIF SUBSTR( v_str, i, 1 ) = 'г' THEN
      v_res := v_res || 'g';
    ELSIF SUBSTR( v_str, i, 1 ) = 'д' THEN
      v_res := v_res || 'd';
    ELSIF SUBSTR( v_str, i, 1 ) = 'е' THEN
      v_res := v_res || 'e';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ё' THEN
      v_res := v_res || 'yo';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ж' THEN
      v_res := v_res || 'j';
    ELSIF SUBSTR( v_str, i, 1 ) = 'з' THEN
      v_res := v_res || 'z';
    ELSIF SUBSTR( v_str, i, 1 ) = 'и' THEN
      v_res := v_res || 'i';
    ELSIF SUBSTR( v_str, i, 1 ) = 'й' THEN
      v_res := v_res || 'y';
    ELSIF SUBSTR( v_str, i, 1 ) = 'к' THEN
      v_res := v_res || 'k';
    ELSIF SUBSTR( v_str, i, 1 ) = 'л' THEN
      v_res := v_res || 'l';
    ELSIF SUBSTR( v_str, i, 1 ) = 'м' THEN
      v_res := v_res || 'm';
    ELSIF SUBSTR( v_str, i, 1 ) = 'н' THEN
      v_res := v_res || 'n';
    ELSIF SUBSTR( v_str, i, 1 ) = 'о' THEN
      v_res := v_res || 'o';
    ELSIF SUBSTR( v_str, i, 1 ) = 'п' THEN
      v_res := v_res || 'p'; 
    ELSIF SUBSTR( v_str, i, 1 ) = 'р' THEN
      v_res := v_res || 'r';
    ELSIF SUBSTR( v_str, i, 1 ) = 'с' THEN
      v_res := v_res || 's';
    ELSIF SUBSTR( v_str, i, 1 ) = 'т' THEN
      v_res := v_res || 't';
    ELSIF SUBSTR( v_str, i, 1 ) = 'у' THEN
      v_res := v_res || 'u';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ф' THEN
      v_res := v_res || 'f';
    ELSIF SUBSTR( v_str, i, 1 ) = 'х' THEN
      v_res := v_res || 'x';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ц' THEN
      v_res := v_res || 'c';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ш' THEN
      v_res := v_res || 'sh'; 
    ELSIF SUBSTR( v_str, i, 1 ) = 'щ' THEN
      v_res := v_res || 'sh';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ъ' THEN
      v_res := v_res || '`';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ы' THEN
      v_res := v_res || 'i';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ь' THEN
      v_res := v_res || '`';
    ELSIF SUBSTR( v_str, i, 1 ) = 'э' THEN
      v_res := v_res || 'e';
    ELSIF SUBSTR( v_str, i, 1 ) = 'ю' THEN
      v_res := v_res || 'yu';
    ELSIF SUBSTR( v_str, i, 1 ) = 'я' THEN
      v_res := v_res || 'ya';
    -- Прописные буквы
    ELSIF SUBSTR( v_str, i, 1 ) = 'А' THEN 
      v_res := v_res || 'A';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Б' THEN
      v_res := v_res || 'B';
    ELSIF SUBSTR( v_str, i, 1 ) = 'В' THEN
      v_res := v_res || 'V';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Г' THEN
      v_res := v_res || 'G';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Д' THEN
      v_res := v_res || 'D';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Е' THEN
      v_res := v_res || 'E';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ё' THEN
      v_res := v_res || 'Yo';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ж' THEN
      v_res := v_res || 'J';
    ELSIF SUBSTR( v_str, i, 1 ) = 'З' THEN
      v_res := v_res || 'Z';
    ELSIF SUBSTR( v_str, i, 1 ) = 'И' THEN
      v_res := v_res || 'I';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Й' THEN
      v_res := v_res || 'Y';
    ELSIF SUBSTR( v_str, i, 1 ) = 'К' THEN
      v_res := v_res || 'K';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Л' THEN
      v_res := v_res || 'L';
    ELSIF SUBSTR( v_str, i, 1 ) = 'М' THEN
      v_res := v_res || 'M';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Н' THEN
      v_res := v_res || 'N';
    ELSIF SUBSTR( v_str, i, 1 ) = 'О' THEN
      v_res := v_res || 'O';
    ELSIF SUBSTR( v_str, i, 1 ) = 'П' THEN
      v_res := v_res || 'P'; 
    ELSIF SUBSTR( v_str, i, 1 ) = 'Р' THEN
      v_res := v_res || 'R';
    ELSIF SUBSTR( v_str, i, 1 ) = 'С' THEN
      v_res := v_res || 'S';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Т' THEN
      v_res := v_res || 'T';
    ELSIF SUBSTR( v_str, i, 1 ) = 'У' THEN
      v_res := v_res || 'U';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ф' THEN
      v_res := v_res || 'F';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Х' THEN
      v_res := v_res || 'X';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ц' THEN
      v_res := v_res || 'C';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ш' THEN
      v_res := v_res || 'Sh'; 
    ELSIF SUBSTR( v_str, i, 1 ) = 'Щ' THEN
      v_res := v_res || 'Sh';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ъ' THEN
      v_res := v_res || '`';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ы' THEN
      v_res := v_res || 'I';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ь' THEN
      v_res := v_res || '`';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Э' THEN
      v_res := v_res || 'E';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Ю' THEN
      v_res := v_res || 'Yu';
    ELSIF SUBSTR( v_str, i, 1 ) = 'Я' THEN
      v_res := v_res || 'Ya';
    ELSE
      v_res := v_res || SUBSTR(v_str, i, 1);

    END IF;
    END LOOP;
    
    dbms_output.put_line(v_res);

END;

You can even use Pl\SQL's standard TRANSLATE() function if you need to translate just a few words.