CODEWITHSUNDEEP
jsonoracle-databaseplsqloracle12c
Rox C
Rox C

Reputation: 1

Cannot coerce between type 49 and type 31

I have a problem to append a json_array_t to another json_array_t in Pl/SQL 12c. I have the following code:

v_method_resp_arr.append(JSON_OBJECT_T(JSON_OBJECT('NAME' VALUE v_method, 'DETAIL' VALUE v_method_det_arr)));

Where v_method_resp_arr and v_method_det_arr are json_array_t. I receive the following error code:

Error: PLS-00801: internal error [*** ASSERT at file pdw4.c, line 909;
Cannot coerce between type 49 and type 31;

Another method is to define a new variable v_method_resp_obj JSON_OBJECT_T and store the info there and then append this object to array, but I want to avoid using an auxiliary variable.

v_method_resp_obj.put('NAME',v_method); v_method_resp_obj.put('DETAIL',v_method_det_arr); v_method_resp_arr.append(v_method_resp_obj);

Upvotes: 0

Views: 439

Answers (1)

R. Genaro
R. Genaro

Reputation: 146

I don't know if this has changed in newer releases, but on 12c you cannot use JSON_OBJECT in PL/SQL.

Also, JSON_OBJECT_T "put" is a procedure, so it can't be used to add key/value pairs without being instantiated first as a variable.

That leaves 3 ways to do what you need, as far as I know:

  1. As you already said in your original post, create another variable to be used as the append argument:
declare
  ja json_array_t;
  jo json_object_t;
  v_method varchar2(15) := 'method name';
  v_method_det_arr varchar2( 30 ) := 'some details here';
begin
  ja := json_array_t;
  jo := json_object_t;
  jo.put( 'name', v_method );
  jo.put( 'detail', v_method_det_arr );
  ja.append( jo );
  dbms_output.put_line(ja.to_string);
end;
  1. Build the json object using JSON_OBJECT_T's parse constructor, who will return the object back, allowing it's use without instantiating a new variable:

two problems with this approach though: It is probably the worst performing and most prone to errors solution, since you will have to concatenate the parameters together as a JSON string to be parsed and returned as a JSON_OBJECT_T.

declare
  ja json_array_t;
  v_method varchar2(15) := 'method name';
  v_method_det_arr varchar2( 30 ) := 'some details here';
begin
  ja := json_array_t;
  ja.append( json_object_t( '{name:"' || v_method  || '", detail:"' || v_method_det_arr  || '"}' ) );
  dbms_output.put_line(ja.to_string);
end;
  1. Build the JSON string in SQL:
declare
  v_method varchar2(15) := 'method name';
  v_method_det_arr varchar2( 30 ) := 'some details here';
  v_json_string clob;
begin
  select json_arrayagg
         ( json_object
           ( 'name' value v_method
           , 'detail' value v_method_det_arr
           )
         )
  into   v_json_string
  from   dual
  ;

  dbms_output.put_line( dbms_lob.substr( v_json_string, 200, 1 ) );
end;

I would believe solution #3 would be the best if you have no need to manipulate the JSON array further or use it as a JSON_ARRAY_T return value for your function, especially if you will be looping through the results of a query to generate the array.

Otherwise - though more verbose - I believe solution #1 would be a more readable and performant approach.

Upvotes: 1

Related Questions