Newbee and I'm stuck

2012-04-10
2013-05-28
  • 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
    James Sumners
    2012-04-11

    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;
    /