I have table like below. I am trying to pull all BRKPK_CNTNR_ID, SSP_Q with respect to each OVRPK_CNTNR_ID

enter image description here

Below query runs fine and produces desired result.

DECLARE
    json_objects JSON_OBJECT_LIST := JSON_OBJECT_LIST() ;
    counter NUMBER := 1;

BEGIN
    FOR ovrpk_detail IN (SELECT OVRPK_CNTNR_ID ,BRKPK_CNTNR_ID,ITEM_DISTB_Q FROM OVRPK_DET od WHERE od.OVRPK_CNTNR_ID='92000000356873110552') LOOP
    begin
        json_objects.extend;
        json_objects(counter) :=JSON_UTIL.JSON_OBJECT(
            JSON_ATTRIBUTE_LIST(
                JSON_UTIL.JSON_ATTRIBUTE('over_pack_container_id',ovrpk_detail.OVRPK_CNTNR_ID),
                JSON_UTIL.JSON_ATTRIBUTE('break_pack_container_id',ovrpk_detail.BRKPK_CNTNR_ID),
                JSON_UTIL.JSON_ATTRIBUTE('item_distributed_quantity',ovrpk_detail.ITEM_DISTB_Q)
        ));

counter := counter 1;

END;

END LOOP;

dbms_output.put_line( JSON_UTIL.JSON_ARRAY(json_objects));

END;

I am expecting is to create function that takes OVRPK_CNTNR_ID as input and returns JSON_OBJECT_LIST as output.

Here is my query I tried.

CREATE OR REPLACE FUNCTION get_json_objects(ovrk_cntnr_id IN CHAR)
RETURN JSON_OBJECT_LIST IS 
   json_objects JSON_OBJECT_LIST := JSON_OBJECT_LIST();
BEGIN 
        DECLARE counter NUMBER := 1;
        
        FOR ovrpk_detail IN (SELECT OVRPK_CNTNR_ID ,BRKPK_CNTNR_ID FROM OVRPK_DET od WHERE od.OVRPK_CNTNR_ID= ovrk_cntnr_id) LOOP
        begin
        json_objects.extend;
        json_objects(counter) :=JSON_UTIL.JSON_OBJECT(
        JSON_ATTRIBUTE_LIST(
            JSON_UTIL.JSON_ATTRIBUTE('over_pack_container_id',ovrpk_detail.OVRPK_CNTNR_ID),
            JSON_UTIL.JSON_ATTRIBUTE('break_pack_container_id',ovrpk_detail.BRKPK_CNTNR_ID)
        ));
        counter := counter 1;
        END;
        END LOOP; 
    
   RETURN json_objects; 
END; 


DECLARE 
   json_return_object JSON_OBJECT_LIST;
   ovrk_cnt_i char := '92000000356873110552'
BEGIN 
   json_return_object := get_json_objects(ovrk_cnt_i); 
   dbms_output.put_line('JSON Object is: ' || json_return_object); 
END;

I am missing something, would anyone help me to resolve this issue? Thank you Note: I am using Oracle 12.c database which does not support JSON however, JSON_UTIL is library written internally to address same

CodePudding user response：

We do not have your packages or functions so this is difficult to test.

Your code has a DECLARE without a corresponding BEGIN or END. If you get rid of all the unnecessary DECLARE/BEGIN/END statements then you can simplify it to:

CREATE OR REPLACE FUNCTION get_json_objects(
  ovrk_cntnr_id IN CHAR
)
RETURN JSON_OBJECT_LIST
IS 
  json_objects JSON_OBJECT_LIST := JSON_OBJECT_LIST();
  counter NUMBER := 1;
BEGIN 
  FOR ovrpk_detail IN (
    SELECT OVRPK_CNTNR_ID,
           BRKPK_CNTNR_ID
    FROM   OVRPK_DET
    WHERE  OVRPK_CNTNR_ID = ovrk_cntnr_id
  ) LOOP
    json_objects.extend;
    json_objects(counter) :=JSON_UTIL.JSON_OBJECT(
      JSON_ATTRIBUTE_LIST(
        JSON_UTIL.JSON_ATTRIBUTE('over_pack_container_id',ovrpk_detail.OVRPK_CNTNR_ID),
        JSON_UTIL.JSON_ATTRIBUTE('break_pack_container_id',ovrpk_detail.BRKPK_CNTNR_ID)
      )
    );
    counter := counter 1;
  END LOOP; 

  RETURN json_objects; 
END; 
/

If your package functions are callable from SQL then you should be able to simplify it further to:

CREATE OR REPLACE FUNCTION get_json_objects(
  i_ovrpk_cntnr_id IN OVRPK_DET.OVRPK_CNTNR_ID%TYPE
) RETURN JSON_OBJECT_LIST
IS 
  json_objects JSON_OBJECT_LIST;
BEGIN      
  SELECT JSON_UTIL.JSON_OBJECT(
           JSON_ATTRIBUTE_LIST(
             JSON_UTIL.JSON_ATTRIBUTE('over_pack_container_id', OVRPK_CNTNR_ID),
             JSON_UTIL.JSON_ATTRIBUTE('break_pack_container_id', BRKPK_CNTNR_ID)
           )
         )
  BULK COLLECT INTO json_objects
  FROM   OVRPK_DET
  WHERE  OVRPK_CNTNR_ID = i_ovrpk_cntnr_id;

  RETURN json_objects; 
END;
/

DECLARE 
   json_return_object JSON_OBJECT_LIST;
   ovrk_cnt_i         OVRPK_DET.OVRPK_CNTNR_ID%TYPE := '92000000356873110552'
BEGIN 
   json_return_object := get_json_objects(ovrk_cnt_i); 
   dbms_output.put_line('JSON Object is: ' || json_return_object); 
END;
/