Forum Stats

  • 3,875,405 Users
  • 2,266,911 Discussions
  • 7,912,196 Comments

Discussions

JSON Framing using BULK COLLECT

select banner from v$version;
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
----------------------------------------
Database Server
----------------------------------------
RDBMS : 19.0.0.0.0
Oracle Applications : 12.2.9

Functionality Required:
=======================

Loop by loop each record should hit Web services. 
So, my task is to send the data one by one .
But in Script which i created, it is not printing correct order. 
Should I go for BULK Collect, since we are expecting 2000 + records in the flat file.
Need assistance on the same.

E.g. 
This should go in first loop... 
following webservices will be called (seperate code). We expect a return parameter.
Once its over, call the next in loop.

"BatchId": "250922163630_Batch1",
  "Request": [
    {
      "Id": "123411",
      "coreid": " ABCDEF123456098765",
      "Amount": 100,
      "currency": "QAR",
      "Status": "YES",
      "remarks": "SUCCESS"
    }
]


CREATE TABLE TEST_12 (BatchId VARCHAR2(1000), 
           Id VARCHAR2(1000),
           coreid VARCHAR2(1000),
           Amount NUMBER,
           currency VARCHAR2(1000),
           Status VARCHAR2(1000),
           remarks VARCHAR2(1000));

SET DEFINE OFF;
Insert into TEST_12 (BATCHID,ID,COREID,AMOUNT,CURRENCY,STATUS,REMARKS) 
values ('250922163630_Batch1','123411','ABCDEF123456098765',100,'QAR','YES','SUCCESS');
Insert into TEST_12 (BATCHID,ID,COREID,AMOUNT,CURRENCY,STATUS,REMARKS) 
values ('250922163630_Batch1','123422','QWERTY123456098765',80.66,'GBP','NO','NOT SUCCESS');
Insert into TEST_12 (BATCHID,ID,COREID,AMOUNT,CURRENCY,STATUS,REMARKS) 
values ('250922163630_Batch2','127578','TEST12345',2000,'USD','YES','SUCCESS');


Desired Output:
===============

{
  "BatchId": "250922163630_Batch1",
  "Request": [
    {
      "Id": "123411",
      "coreid": " ABCDEF123456098765",
      "Amount": 100,
      "currency": "QAR",
      "Status": "YES",
      "remarks": "SUCCESS"
    }
	]
	"BatchId": "250922163630_Batch1",
  "Request": [
    {
      "Id": "123422",
      "coreid": " QWERTY123456098765",
      "Amount": 80.66,
      "currency": "GBP",
      "Status": "NO",
      "remarks": "NOT SUCCESS"
    }
  ]
	"BatchId": "250922163630_Batch2",
  "Request": [
    {
      "Id": "127578",
      "coreid": " TEST12345",
      "Amount": 2000,
      "currency": "USD",
      "Status": "YES",
      "remarks": "SUCCESS"
    }
  ]
}


DECLARE
	
	CURSOR C_SEL
	IS
		SELECT * FROM TEST_12;
	
	l_batch		VARCHAR2(30000);
BEGIN
	
	FOR REC_SEL	IN	C_SEL
	LOOP
		l_batch := NULL;
		---
		SELECT JSON_ARRAYAGG
                (
                       JSON_OBJECT('batchid' VALUE batchid,
                       'id' VALUE id ,
                       'coreid' VALUE coreid,
                       'amount' VALUE amount,
                       'currency' VALUE currency,
                       'status' VALUE status,
                       'remarks' VALUE remarks
                                  )
                ) 
		INTO	l_batch
		FROM test_12;			
				
	DBMS_OUTPUT.PUT_LINE ('BATCH OUTPUT: '||l_batch);		
	END LOOP;
EXCEPTION
WHEN OTHERS
THEN
	DBMS_OUTPUT.PUT_LINE('Error : '||SQLERRM);
END;

OUTPUT:
=======

BATCH OUTPUT: [{"batchid":"250922163630_Batch1","id":"123422","coreid":"QWERTY123456098765","amount":80.66,"currency":"GBP","status":"NO","remarks":"NOT SUCCESS"},{"batchid":"250922163630_Batch1","id":"123411","coreid":"ABCDEF123456098765","amount":100,"currency":"QAR","status":"YES","remarks":"SUCCESS"},{"batchid":"250922163630_Batch2","id":"127578","coreid":"TEST12345","amount":2000,"currency":"USD","status":"YES","remarks":"SUCCESS"}]
BATCH OUTPUT: [{"batchid":"250922163630_Batch1","id":"123422","coreid":"QWERTY123456098765","amount":80.66,"currency":"GBP","status":"NO","remarks":"NOT SUCCESS"},{"batchid":"250922163630_Batch1","id":"123411","coreid":"ABCDEF123456098765","amount":100,"currency":"QAR","status":"YES","remarks":"SUCCESS"},{"batchid":"250922163630_Batch2","id":"127578","coreid":"TEST12345","amount":2000,"currency":"USD","status":"YES","remarks":"SUCCESS"}]
BATCH OUTPUT: [{"batchid":"250922163630_Batch1","id":"123422","coreid":"QWERTY123456098765","amount":80.66,"currency":"GBP","status":"NO","remarks":"NOT SUCCESS"},{"batchid":"250922163630_Batch1","id":"123411","coreid":"ABCDEF123456098765","amount":100,"currency":"QAR","status":"YES","remarks":"SUCCESS"},{"batchid":"250922163630_Batch2","id":"127578","coreid":"TEST12345","amount":2000,"currency":"USD","status":"YES","remarks":"SUCCESS"}]


Thanks for your kind assistance as always.


Regards,

SG

Best Answer

Answers

  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon

    I got the problem of printing. Forgot to include the Cursor reference variable.

    But, I would like to make use of BULK COLLECT functionalities through JSON.

    Appreciate if someone can share a sample of utilizing JSON with BULK COLLECT.

    SELECT JSON_ARRAYAGG(
         JSON_OBJECT('batchid' VALUE REC_SEL.batchid,
              'id' VALUE REC_SEL.id ,
              'coreid' VALUE REC_SEL.coreid,
              'amount' VALUE REC_SEL.amount,
              'currency' VALUE REC_SEL.currency,
              'status' VALUE REC_SEL.status,
              'remarks' VALUE REC_SEL.remarks
              )
              ) 
    		INTO	l_batch
    		FROM test_12;	 
    


  • User_3ABCE
    User_3ABCE Member Posts: 212 Silver Badge

    It is strange to have a repeated keys with an order dependency.

    select json_serialize(
      json_arrayagg(
        json_object(
          t.BatchId,
          'Request' value json_arrayagg(json_object(t.Id, t.coreid, t.Amount, t.currency, t.Status, t.remarks))
                   ))
           pretty) "Not a Desired Output"
    from test_12 t
    group by t.batchid;    
    
    Not a Desired Output
    --------------------        
    [
      {
        "BatchId" : "250922163630_Batch1",
        "Request" :
        [
          {
            "Id" : "123411",
            "coreid" : "ABCDEF123456098765",
            "Amount" : 100,
            "currency" : "QAR",
            "Status" : "YES",
            "remarks" : "SUCCESS"
          },
          {
            "Id" : "123422",
            "coreid" : "QWERTY123456098765",
            "Amount" : 80.66,
            "currency" : "GBP",
            "Status" : "NO",
            "remarks" : "NOT SUCCESS"
          }
        ]
      },
      {
        "BatchId" : "250922163630_Batch2",
        "Request" :
        [
          {
            "Id" : "127578",
            "coreid" : "TEST12345",
            "Amount" : 2000,
            "currency" : "USD",
            "Status" : "YES",
            "remarks" : "SUCCESS"
          }
        ]
      }
    ]
    
    User_HH9X5
  • User_3ABCE
    User_3ABCE Member Posts: 212 Silver Badge
    select /*+ leading(t) use_nl(t u) */json_serialize(
      json_objectagg(
        k value case k when 'BatchId' then json_scalar(t.batchid)
                       when 'Request' then json_array(json_object(t.Id, t.coreid, t.Amount, t.currency, t.Status, t.remarks) returning json)
                end
        returning clob)
           pretty) "Desired Output with no an order guarantee"
    from test_12 t cross join (select 'BatchId' k from dual union all select 'Request' from dual) u;
    
    Desired Output with no an order guarantee                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    {
      "BatchId" : "250922163630_Batch1",
      "Request" :
      [
        {
          "Id" : "123411",
          "coreid" : "ABCDEF123456098765",
          "Amount" : 100,
          "currency" : "QAR",
          "Status" : "YES",
          "remarks" : "SUCCESS"
        }
      ],
      "BatchId" : "250922163630_Batch1",
      "Request" :
      [
        {
          "Id" : "123422",
          "coreid" : "QWERTY123456098765",
          "Amount" : 80.66,
          "currency" : "GBP",
          "Status" : "NO",
          "remarks" : "NOT SUCCESS"
        }
      ],
      "BatchId" : "250922163630_Batch2",
      "Request" :
      [
        {
          "Id" : "127578",
          "coreid" : "TEST12345",
          "Amount" : 2000,
          "currency" : "USD",
          "Status" : "YES",
          "remarks" : "SUCCESS"
        }
      ]
    }
    
    User_HH9X5
  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon

    @User_3ABCE : Thank you for sharing the code.

    While trying to execute the query, receiving the below error.

    select /*+ leading(t) use_nl(t u) */
    json_serialize(
     json_objectagg(
      k value case k when 'BatchId' then json_scalar(t.batchid)
              when 'Request' then json_array(json_object(t.Id, t.coreid, t.Amount, t.currency, t.Status, t.remarks) returning json)
          end
      returning clob)
        pretty) "Desired Output with no an order guarantee"
    from test_12 t cross join (select 'BatchId' k from dual union all select 'Request' from dual) u;
    
    Error
    ======
    
    ORA-40449: invalid data type for return value
    40449. 00000 - "invalid data type for return value"
    *Cause:  The provided data type for the return value was invalid or
          unsupported.
    *Action:  Provide a supported return value.
    Error at Line: 29 Column: 132
    
  • User_3ABCE
    User_3ABCE Member Posts: 212 Silver Badge
    -- 19.13
    select /*+ leading(t) use_nl(t u) */json_serialize(
      json_objectagg(
        k value case k when 'BatchId' then '"'||replace(replace(t.batchid,'\','\\'),'"','\"')||'"'
                       when 'Request' then json_array(json_object(t.Id, t.coreid, t.Amount, t.currency, t.Status, t.remarks) returning varchar2)
                end
        format json
        returning clob
        )
           pretty) "Desired Output with no an order guarantee"
    from test_12 t cross join (select 'BatchId' k from dual union all select 'Request' from dual) u;
    
  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon
    ORA-40590: invalid format
    40590. 00000 - "invalid format"
    *Cause:  The specified format was invalid.
    *Action:  Ensure that the format is specified correctly.
    Error at Line: 45 Column: 6
    
  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon
    edited Oct 11, 2022 12:04PM
    • As per the requirement, I want to send the first Batch at once. We are expecting one batch will have 500 records.
    E.g. 
    1) This should be one batch. i.e. 500 different values from "Id" till "remarks". 
    This will be sent to Webservice, where we will get a RETURN parameter from external system on the status.
    
    2) Once we receive, then the next batch will be sent. i.e. can have 500 or less than 500 records.
    This will be sent to Webservice, where we will get a RETURN parameter from external system on the status.
    


    E.g. This should be one batch. 
    {
      "BatchId" : "250922163630_Batch1",
      "Request" :
      [
       {
        "Id" : "123422",
        "coreid" : "QWERTY123456098765",
        "Amount" : 80.66,
        "currency" : "GBP",
        "Status" : "NO",
        "remarks" : "NOT SUCCESS"
       },
       {
        "Id" : "123411",
        "coreid" : "ABCDEF123456098765",
        "Amount" : 100,
        "currency" : "QAR",
        "Status" : "YES",
        "remarks" : "SUCCESS"
       }
      ]
     }
    
    2) Second batch
    
     {
      "BatchId" : "250922163630_Batch2",
      "Request" :
      [
       {
        "Id" : "127578",
        "coreid" : "TEST12345",
        "Amount" : 2000,
        "currency" : "USD",
        "Status" : "YES",
        "remarks" : "SUCCESS"
       }
      ]
     }
    
  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon

    Tried to frame the PL/SQL Block, but not receiving the expected output. Data is repeated twice.

    DECLARE
    	
    	CURSOR C_SEL
    	IS
    		SELECT * FROM TEST_12;
    	
    	l_batch		VARCHAR2(30000);
    BEGIN
    	
    	FOR REC_SEL	IN	C_SEL
    	LOOP
    		l_batch := NULL;
    		---
    		 SELECT
    				json_serialize
    				(
    				json_arrayagg(
    				JSON_OBJECT(
    				t.batchid,'Request' VALUE
    				JSON_ARRAYAGG(
    				JSON_OBJECT
    				(
    				'batch_id' 	VALUE REC_SEL.batchid,
    				'id_value' 	VALUE REC_SEL.id,
    				'core_id'  	VALUE REC_SEL.coreid,
    				'amount'   	VALUE REC_SEL.amount,
    				'currency' 	VALUE REC_SEL.currency,
    				'status'   	VALUE REC_SEL.status,
    				'remarks_val' VALUE REC_SEL.remarks
    				)
    				)
    				)
    				)
    				pretty
    				) "Not a Desired Output"
    		into l_batch	
    		FROM test_12 t
    		WHERE batchid = REC_SEL.batchid
    		group by t.batchid;    
    				
    		DBMS_OUTPUT.PUT_LINE ('BATCH OUTPUT: '||l_batch);		
    	END LOOP; 
    EXCEPTION
    WHEN OTHERS 
    THEN
        dbms_output.put_line('Error : ' || sqlerrm);
    END;
    


    Output Received:

    BATCH OUTPUT: [
     {
      "BATCHID" : "250922163630_Batch1",
      "Request" :
      [
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123422",
        "core_id" : "QWERTY123456098765",
        "amount" : 80.66,
        "currency" : "GBP",
        "status" : "NO",
        "remarks_val" : "NOT SUCCESS"
       },
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123422",
        "core_id" : "QWERTY123456098765",
        "amount" : 80.66,
        "currency" : "GBP",
        "status" : "NO",
        "remarks_val" : "NOT SUCCESS"
       }
      ]
     }
    ]
    BATCH OUTPUT: [
     {
      "BATCHID" : "250922163630_Batch1",
      "Request" :
      [
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123411",
        "core_id" : "ABCDEF123456098765",
        "amount" : 100,
        "currency" : "QAR",
        "status" : "YES",
        "remarks_val" : "SUCCESS"
       },
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123411",
        "core_id" : "ABCDEF123456098765",
        "amount" : 100,
        "currency" : "QAR",
        "status" : "YES",
        "remarks_val" : "SUCCESS"
       }
      ]
     }
    ]
    BATCH OUTPUT: [
     {
      "BATCHID" : "250922163630_Batch2",
      "Request" :
      [
       {
        "batch_id" : "250922163630_Batch2",
        "id_value" : "127578",
        "core_id" : "TEST12345",
        "amount" : 2000,
        "currency" : "USD",
        "status" : "YES",
        "remarks_val" : "SUCCESS"
       }
      ]
     }
    ]
    


    Expected Output:

    BATCH OUTPUT: [
     {
      "BATCHID" : "250922163630_Batch1",
      "Request" :
      [
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123422",
        "core_id" : "QWERTY123456098765",
        "amount" : 80.66,
        "currency" : "GBP",
        "status" : "NO",
        "remarks_val" : "NOT SUCCESS"
       },
       {
        "batch_id" : "250922163630_Batch1",
        "id_value" : "123411",
        "core_id" : "ABCDEF123456098765",
        "amount" : 100,
        "currency" : "QAR",
        "status" : "YES",
        "remarks_val" : "SUCCESS"
       }
      ]
     }
    ]
    BATCH OUTPUT: [
     {
      "BATCHID" : "250922163630_Batch2",
      "Request" :
      [
       {
        "batch_id" : "250922163630_Batch2",
        "id_value" : "127578",
        "core_id" : "TEST12345",
        "amount" : 2000,
        "currency" : "USD",
        "status" : "YES",
        "remarks_val" : "SUCCESS"
       }
      ]
     }
    ]
    


  • User_HH9X5
    User_HH9X5 Member Posts: 44 Blue Ribbon
    Answer ✓

    Issue resolved. Achieved this by hardcoding the header section. Remaining processing in a loop.


    Thanks all for your assistance.