ORA-01422: exact fetch returns more than requested number of rows in combination with a json object-CodePudding

I have a table with a few entries. There is a field with an ID but it is not unique. So I want to get all entries with a special ID and then insert the rows into a JSON object, which gets finally returned to the JavaScript. Here my current code:

DECLARE
    v_json_result_clob CLOB;
BEGIN
    SELECT JSON_ARRAY(
        JSON_OBJECT(
            KEY 'id'           VALUE ID,
            KEY 'blob_data'    VALUE IMAGE RETURNING CLOB
        )
        RETURNING CLOB
    ) INTO v_json_result_clob FROM TEST_TABLE WHERE ID = apex_application.g_x01;

    apex_json.open_object;
    apex_json.write('success', true);
    apex_json.write('result', v_json_result_clob);
    apex_json.close_object;
EXCEPTION
    WHEN OTHERS THEN
        apex_json.open_object;
        apex_json.write('success', false);
        apex_json.write('message', sqlerrm);
        apex_json.close_object;
END;

Error:

ORA-01422: exact fetch returns more than requested number of rows

I am returning the JSON object as CLOB because it contains a BLOB - this way i can not exceed the varchar length limitation.

Help is highly appreciated.

Best regards,
Filip.

CodePudding user response：

Use JSON_ARRAYAGG to aggregate all the rows into a single JSON array containing many objects rather than returning multiple rows each with a JSON array containing a single object.

DECLARE
  v_json_result_clob CLOB;
BEGIN
  SELECT JSON_ARRAYAGG(
           JSON_OBJECT(
              KEY 'id'           VALUE ID,
              KEY 'blob_data'    VALUE IMAGE RETURNING CLOB
           )
           RETURNING CLOB
         )
  INTO   v_json_result_clob
  FROM   TEST_TABLE
  WHERE ID = apex_application.g_x01;

  -- ...
END;
/

fiddle

CodePudding user response：

This happens because you're trying to fetch multiple values int a non-collection variable without explicitly saying to oracle what it should do in order for data to fit into the v_json_result_clob variable.

So, to get rid of this error you need either to make v_json_result_clob a collection or join all the lines of the output into one result line.

I'm not sure what is better for you but I did an example of how you can join lines using listagg because I suppose t might be heavy to pass and handle plsql collections in javascript

DECLARE
    v_json_result_clob CLOB;
BEGIN
    SELECT listagg(JSON_ARRAY(
        JSON_OBJECT(
            KEY 'id_col'           VALUE ID_col,
            KEY 'blob_col'    VALUE blob_col RETURNING CLOB
        )
        RETURNING CLOB
    )) INTO v_json_result_clob FROM TEST_TABLE WHERE ID_col = 1; -- apex_application.g_x01;

  dbms_output.put_line(v_json_result_clob);

END;