Newbee and I'm stuck

[Michael Woytowitz]

My code below.

I keep getting this error

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MICHAEL.SEARCH_COUNT", line 68
ORA-06512: at line 10

I can print the json array to the dms_output but when try to set the array to a property in a json object I get this error

Please help, thanks you in advance

CREATE OR REPLACE
PROCEDURE search_count (p_name_frag IN VARCHAR2, p_max_cnt IN NUMBER, p_res_cnt OUT NUMBER, p_json OUT VARCHAR2)
IS
v_exec_time NUMBER;
v_start_time NUMBER;
v_end_time NUMBER;
j_obj json;
j_inner_obj json;
l_obj json_list;

type cur_RESULT_SET is REF CURSOR;
cur_search cur_RESULT_SET;
row_search MICHAEL.PARTY_SEARCH%rowtype;

BEGIN
  v_start_time := dbms_utility.get_time;
  j_obj := json(); -an empty structure
 
  SELECT count(id) INTO p_res_cnt
  FROM PARTY_SEARCH
  WHERE FAMILY_NAME_SEARCH_HEX LIKE (substr(NLSSORT(p_name_frag, 'NLS_SORT=BINARY_AI'),1,length(NLSSORT(p_name_frag, 'NLS_SORT=BINARY_AI'))-2) || '%')
  AND ROWNUM <= p_max_cnt + 1
  ;  
 
  - if p_res_cnt > p_max_cnt then return json with limit prop = true
  if (p_res_cnt > p_max_cnt) then
    j_obj.put('success', true);
    j_obj.put('limit', true);
    j_obj.put('data', json_value.makenull);
    j_obj.put('message', json_value.makenull); 
  else
    OPEN cur_search FOR 
    SELECT *
    FROM PARTY_SEARCH
    WHERE FAMILY_NAME_SEARCH_HEX LIKE (substr(NLSSORT(p_name_frag, 'NLS_SORT=BINARY_AI'),1,length(NLSSORT(p_name_frag, 'NLS_SORT=BINARY_AI'))-2) || '%');

    l_obj := json_list();

    loop
      fetch cur_search into row_search;
      exit when cur_search%notfound;
      -dbms_output.put_line(row_search.family_name);
      j_inner_obj := json();
      j_inner_obj.put('lastName', row_search.family_name);
      j_inner_obj.put('firstName', row_search.given_name);
     
      l_obj.append(j_inner_obj.to_json_value());
     
    end loop;
    CLOSE cur_search;
   
    l_obj.print;
   
    j_obj.put('success', true);
    j_obj.put('limit', false);
    j_obj.put('message', json_value.makenull); 
    j_obj.put('data', l_obj.to_char);
 
 
  end if;
 
  p_json := j_obj.to_char;
  v_end_time := dbms_utility.get_time;
  v_exec_time := (v_end_time-v_start_time)/100;
  dbms_output.put_line( v_exec_time);
  j_obj.print;
 
END
;

[James Sumners]

You're trying to convert your JSON array object into a string and then trying to add that string as a value to a property on your object. Don't do that. Instead, add a JSON value to your object:

declare
  js_list       json_list := json_list();
  js_inner_obj  json;
  js_out_obj    json := json();
begin
  for i in 1..10
  loop
    js_inner_obj := json();
    js_inner_obj.put('obj_' || i, 'value_' || i);
    js_list.append(js_inner_obj.to_json_value);
  end loop;
  
  js_out_obj.put('foo', 'bar');
  js_out_obj.put('answer', 42);
  js_out_obj.put('myArr', js_list.to_json_value);
  
  js_out_obj.print;
end;
/