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