Hi,
I have below json (in varchar2 variable) got through pl/sql/service call , Need to update values (main json, sub json , json arrays values) then call other service to update.
I am unable to parse values/sub-json values/array values . these values are coming thru parameters of store procedure.. please guide any easy and quick way to do that thru sql or pl sql.. example how to update refno inside bankRec
In pl sql I am using JSON_object_T / put method etc.
{
"clientNo": "U02529",
"status": "A",
"statusReason": null,
"statusRemark": null,
"registeredDt": "2009-11-25T00:00:00.000+05:00",
"internalClientYn": false,
"categoryType": "CORPORATION",
"clientShort": "Silverlake Pte Ltd",
"clientShortFormat": "FULL",
"clientShortFormatLength": null,
"clientShortFormatStructure": null,
"clientName": "Silverlake Pte Ltd",
"clientNameFormat": "FREE",
"clientNameFormatValue": null,
"clientType": "GEN",
"remark": null,
"branchCtrl": null,
"profitCentre": null,
"residentYn": true,
"countryResident": "SG",
"stateResident": "SG",
"residentCity": null,
"residentStatus": null,
"residentSinceDt": null,
"location": null,
"taxableYn": false,
"taxType": null,
"taxNo": null,
"taxValidityDt": null,
"nextReviewDt": null,
"periodFreqReview": null,
"reviewDay": null,
"lastReviewBy": null,
"lastReviewDt": null,
"clientIndicator": "N",
"lndClientYn": false,
"gftClientYn": false,
"depClientYn": false,
"tfnClientYn": false,
"bgtClientYn": false,
"classifyClientYn": false,
"clientNature": "CUSTOMER",
"reclassYn": null,
"crRating": null,
"prevCrRating": null,
"crRatingDt": null,
"cnbRating": null,
"extCompany": null,
"extRating": null,
"taxExempt": "N",
"branchRec": null,
"bankRec": null,
"clientCorpRec": {
"clientNo": "U02529",
"incorporationDt": null,
"incorporationPlace": null,
"countryIncorporation": null,
"stateIncorporation": null,
"ownershipType": "LIMITED",
"employeesNo": null,
"businessType": "PRIVATE",
"totalCapitalAmt": null,
"ccyTotalCapital": null,
"businessNature": null,
"industryCode": "TR01",
"oenaceCode": null,
"segmentCode": "SC1",
"businessRiskRating": null,
"businessLengthSource": null,
"businessLength": null,
"fiscalYear": null,
"currentStockAmt": null,
"ccyCurrentStock": null,
"marketView": null,
"liquidationDt": null,
"nextReviewDt": null,
"periodFreqReview": null,
"reviewDay": null,
"lastReviewBy": null,
"lastReviewDt": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": null,
"lastSuccessfulValidation": null,
"changeSummary": null
},
"clientEmployeeRec": null,
"clientIndvlRec": null,
"clientDetailRec": {
"clientNo": "U02529",
"countryRisk": null,
"stateRisk": null,
"riskCity": null,
"riskProfileSummary": null,
"riskWeightagePct": null,
"customerSince": null,
"depositorSince": null,
"borrowerSince": null,
"migrationDt": null,
"centralBankRefNo": null,
"controlBankCode": null,
"bankruptcyDt": null,
"clientNoBankruptcyTrustee": null,
"contactRefNoBankruptcyTrustee": null,
"bankruptcyReason": null,
"bankruptcyRemark": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": null,
"lastSuccessfulValidation": null,
"changeSummary": null
},
bankRec { "refno" : 10},
"clientOthRec": null,
"clientInsuranceCompanyRec": null,
"clientAliasList": [],
"clientAltNoList": [],
"clientClassList": [
{
"clientNo": "U02529",
"classType": "CLASS-01",
"classValue": "40",
"classValueDesc": "GENERAL COMMERCE",
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529~CLASS-01",
"lastSuccessfulValidation": null,
"changeSummary": null
},
{
"clientNo": "U02529",
"classType": "CLASS-02",
"classValue": "PA",
"classValueDesc": "PASSED",
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529~CLASS-02",
"lastSuccessfulValidation": null,
"changeSummary": null
},
{
"clientNo": "U02529",
"classType": "CLASS-04",
"classValue": "700000",
"classValueDesc": "XXXXXXXXXX UPD",
"version": 1,
"bizVersion": 1,
"createdBy": "rina.valdez",
"createdDt": "2017-08-28T13:51:03.688+05:00",
"journalDt": "2017-08-13T00:00:00.000+05:00",
"journalNo": 153456,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529~CLASS-04",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"clientContactList": [
{
"clientNo": "U02529",
"defaultContactTypeYn": true,
"contactRefNo": "00000000000000277308",
"contactType": "PHONE",
"contactSubType": null,
"contactPerson": null,
"contactDepartment": null,
"contactDetail": "+00-1010124310233",
"contactStatus": "INVALID",
"contactStatusReason": null,
"contactLang": "ENGLISH",
"nextReviewDt": null,
"periodFreqReview": "5Y",
"reviewDay": null,
"lastReviewBy": null,
"lastReviewDt": null,
"usedYn": true,
"activeYn": true,
"startDt": "2009-11-25T00:00:00.000+05:00",
"endDt": null,
"contactAddressRec": null,
"contactDtlList": [
{
"contactRefNo": "00000000000000277308",
"contactDetailRefNo": "00000000000000000141",
"bestContactType": "PMA",
"nonContactDay": "THURSDAY",
"nonContactStartDt": "2017-08-26T00:00:00.000+05:00",
"nonContactEndDt": null,
"bestContactStartTm": "1970-01-01T00:00:00.000+05:00",
"bestContactEndTm": "1970-01-01T09:00:00.000+05:00",
"extCommunicationYn": false,
"extCommunicationGateway": null,
"version": 3,
"bizVersion": 1,
"createdBy": "emy.bartolome",
"createdDt": "2018-08-16T02:48:06.253+05:00",
"journalDt": "2017-08-25T00:00:00.000+05:00",
"journalNo": 230274,
"modifiedBy": "emy.bartolome",
"modifiedDt": "2018-08-22T05:23:31.317+05:00",
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "00000000000000000141",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"dndYn": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529~00000000000000277308",
"lastSuccessfulValidation": null,
"changeSummary": null
},
{
"clientNo": "U02529",
"defaultContactTypeYn": true,
"contactRefNo": "00000000000000277309",
"contactType": "POSTAL",
"contactSubType": "POS",
"contactPerson": "1",
"contactDepartment": "MRRTV2",
"contactDetail": null,
"contactStatus": "VALID",
"contactStatusReason": "MRRTV2",
"contactLang": "ENGLISH",
"nextReviewDt": "2020-11-25T00:00:00.000+05:00",
"periodFreqReview": "5Y",
"reviewDay": null,
"lastReviewBy": null,
"lastReviewDt": null,
"usedYn": true,
"activeYn": true,
"startDt": "2009-11-25T00:00:00.000+05:00",
"endDt": "2999-11-25T00:00:00.000+05:00",
"contactAddressRec": {
"contactRefNo": "00000000000000277309",
"addressFormat": "FREE",
"blockNo": null,
"blockName": null,
"streetNo": null,
"streetName": null,
"addr1": "71 Robinson Road",
"addr2": null,
"addr3": null,
"addr4": null,
"addr5": null,
"area": "1",
"district": "1",
"city": "SD",
"country": "SG",
"state": "SG",
"postalCode": "1",
"province": "1",
"nearestLandmark": "1",
"mapLatLng": null,
"version": 3,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2019-01-04T00:00:00.000+05:00",
"journalNo": 695660,
"modifiedBy": "cbs.user1",
"modifiedDt": "2019-01-22T08:54:37.754+05:00",
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": null,
"lastSuccessfulValidation": null,
"changeSummary": null
},
"contactDtlList": [],
"dndYn": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529~00000000000000277309",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"clientEducationList": [],
"clientEmploymentList": [],
"clientGlobalIdList": [
{
"clientNo": "U02529",
"globalIdType": "BRN",
"globalId": "BRN201301-001",
"globalIdStatus": "VALID",
"issueDt": "2007-05-18T00:00:00.000+05:00",
"expiryDt": null,
"issuePlace": "New York",
"issueAuthority": null,
"countryIssue": "US",
"stateIssue": null,
"defaultFlag": "ALL",
"nextReviewDt": null,
"periodFreqReview": null,
"reviewDay": null,
"lastReviewBy": null,
"lastReviewDt": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "BRN~BRN201301-001~US",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"clientListStatusList": [],
"clientManagerList": [],
"clientStatusHistList": [
{
"statusId": -4,
"clientNo": "U02529",
"status": "A",
"statusReason": null,
"remark": null,
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "-4",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"clientGrpDtlList": [
{
"grpDtlRefNo": "00000000000000078261",
"grpRefNo": "00000000000000034448",
"grpType": "CG001",
"relationType": "C003",
"relationTypeDesc": null,
"clientNo": "U02529",
"clientName": "Silverlake Pte Ltd",
"othName": "SunGard Pte Ltd",
"member": "PARENT_COMPANY",
"clientNoCounter": "U02541",
"clientNameCounter": "Silverlake Japan Pte Ltd",
"counterOthName": "SunGard Japan Pte Ltd",
"counterMember": "CHILD_COMPANY",
"nextReviewDt": "2010-11-25T00:00:00.000+05:00",
"periodFreqReview": "1Y",
"reviewDay": 25,
"lastReviewBy": null,
"lastReviewDt": null,
"activeYn": false,
"startDt": "2009-11-25T00:00:00.000+05:00",
"endDt": "2017-08-24T00:00:00.000+05:00",
"version": 2,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2017-08-25T00:00:00.000+05:00",
"journalNo": 434761,
"modifiedBy": "emy.bartolome",
"modifiedDt": "2018-10-12T02:48:37.662+05:00",
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "00000000000000078261",
"lastSuccessfulValidation": null,
"changeSummary": null
},
{
"grpDtlRefNo": "00000000000000078259",
"grpRefNo": "00000000000000034448",
"grpType": "CG001",
"relationType": "C003",
"relationTypeDesc": null,
"clientNo": "U02529",
"clientName": "Silverlake Pte Ltd",
"othName": "SunGard Pte Ltd",
"member": "PARENT_COMPANY",
"clientNoCounter": "U02532",
"clientNameCounter": "Silverlake Singapore Pte Ltd",
"counterOthName": "SunGard Singapore Pte Ltd",
"counterMember": "CHILD_COMPANY",
"nextReviewDt": "2010-11-25T00:00:00.000+05:00",
"periodFreqReview": "1Y",
"reviewDay": 25,
"lastReviewBy": null,
"lastReviewDt": null,
"activeYn": false,
"startDt": "2009-11-25T00:00:00.000+05:00",
"endDt": "2017-08-24T00:00:00.000+05:00",
"version": 2,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2017-08-25T00:00:00.000+05:00",
"journalNo": 434761,
"modifiedBy": "emy.bartolome",
"modifiedDt": "2018-10-12T02:48:37.652+05:00",
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "00000000000000078259",
"lastSuccessfulValidation": null,
"changeSummary": null
},
{
"grpDtlRefNo": "00000000000000078260",
"grpRefNo": "00000000000000034448",
"grpType": "CG001",
"relationType": "C003",
"relationTypeDesc": null,
"clientNo": "U02529",
"clientName": "Silverlake Pte Ltd",
"othName": "SunGard Pte Ltd",
"member": "PARENT_COMPANY",
"clientNoCounter": "U02535",
"clientNameCounter": "Silverlake Philippines Pte Ltd",
"counterOthName": "SunGard Philippines Pte Ltd",
"counterMember": "CHILD_COMPANY",
"nextReviewDt": "2010-11-25T00:00:00.000+05:00",
"periodFreqReview": "1Y",
"reviewDay": 25,
"lastReviewBy": null,
"lastReviewDt": null,
"activeYn": false,
"startDt": "2009-11-25T00:00:00.000+05:00",
"endDt": "2017-08-24T00:00:00.000+05:00",
"version": 2,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2017-08-25T00:00:00.000+05:00",
"journalNo": 434761,
"modifiedBy": "emy.bartolome",
"modifiedDt": "2018-10-12T02:48:37.659+05:00",
"virtualAttributeList": [],
"dataExtensionList": [],
"header": null,
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "00000000000000078260",
"lastSuccessfulValidation": null,
"changeSummary": null
}
],
"clientGrpDtlCounterList": [],
"taxExemptList": [],
"version": 1,
"bizVersion": 1,
"createdBy": "demo",
"createdDt": "2013-07-01T00:00:00.000+05:00",
"journalDt": "2013-07-01T00:00:00.000+05:00",
"journalNo": 1,
"modifiedBy": null,
"modifiedDt": null,
"virtualAttributeList": [],
"dataExtensionList": [],
"header": {
"serviceOperation": null,
"applUserCode": null,
"organisationCode": null,
"userRoleList": null,
"dataChannelCode": null,
"langCode": null,
"corrId": null,
"screenNo": null,
"bpmInfo": null,
"clientInfo": null,
"creationTime": null,
"typeName": "com.silverlakesymmetri.cbs.mcl.bdo.sdo.Client",
"channelSource": null,
"resend": null,
"requestUri": null
},
"securityHint": null,
"digitalSignature": null,
"hasNextYn": null,
"messageInfoList": [],
"hasError": null,
"publicKey": "U02529",
"lastSuccessfulValidation": null,
"changeSummary": null
}