Hi Have a table as
CREATE TABLE Account_Form
(Forms_id NUMBER(10),
Forms_Data CLOB NOT NULL,
CONSTRAINT Pk_Accouont_Form PRIMARY KEY (Forms_id),
CONSTRAINT Chk_Json01_Account_Form CHECK (Forms_Data IS JSON)
);
– inserting data
INSERT INTO Account_Form(Forms_id, Forms_Data)
VALUES(1000, '{
"birthDate": {
"year": 1949,
"month": 9,
"day": 23
},
"firstName": "Bruce",
"lastName": "Hutchinson,",
"middleNames": "The Boss",
"citizenship": "US",
"authoritativeIds": {
"usSsn": "223-34-6547",
"passport": {
"nationality": "US",
"number": "ZA-98908634",
"issueDate": {
"year": 2022,
"month": 5,
"day": 10
},
"expirationDate": {
"year": 2028,
"month": 5,
"day": 11
}
},
"usDriversLicense": {
"number": "6013132843",
"issuer": "Illinois",
"issueDate": {
"year": 2020,
"month": 5,
"day": 14
},
"expirationDate": {
"year": 2028,
"month": 5,
"day": 15
}
},
"usStateId": {
"number": "14562712345678901234",
"issuer": "Illinois",
"issueDate": {
"year": 2020,
"month": 5,
"day": 14
},
"expirationDate": {
"year": 2028,
"month": 5,
"day": 15
}
},
"usMilitaryId": {
"number": "99550463993651558",
"issuer": "US Department of Defense",
"issueDate": {
"year": 2020,
"month": 5,
"day": 14
},
"expirationDate": {
"year": 2028,
"month": 5,
"day": 15
}
},
"usStudentId": {
"number": "90134903661027474489",
"issuer": "Harvard",
"issueDate": {
"year": 2020,
"month": 5,
"day": 14
},
"expirationDate": {
"year": 2028,
"month": 5,
"day": 15
}
},
"usFinCenId": "FINC12345"
},
"mobileNumber": "+14155552671",
"emailAddress": "theboss@springsteen.com",
"physicalAddress": {
"addressLine1": "4025 Jessica Leigh LN",
"addressLine2": "4025 Jessica Leigh LN2",
"city": "Asbury Park",
"stateOrProvince": "California",
"postalCode": "07712",
"country": "US"
},
"postalAddress": {
"addressLine1": "4025 Jessica Leigh LN",
"addressLine2": "4025 Jessica Leigh LN2",
"city": "Asbury Park",
"stateOrProvince": "California",
"postalCode": "07712",
"country": "US"
},
"supportingDocuments": {
"usDriversLicense": {
"frontImage": "docum-31641-8b6ed22f-a645-4c95-8818-532658c179a7",
"rearImage": "docum-31641-5b3a37a0-1120-4cd9-8ef3-c4edc233464b"
}
},
"attestedTermsAndConditions": [{
"agreementCode": "demo-individual-terms-and-conditions",
"documentHash": "7f83b1657ff1fc53b92dc18148a1d65dfc2d4b1fa3d677284addd200126d9069"
}]
}');
now I have to generate random 9 digits to upate the ssn from here
I have prepared the scripts like below, would anyone please suggest how to update it dynamically.
Sample Code as below, I will use cursor
here the usSsn should be coming from the V_SSN dynamically.
Please share any thoughts, I am using Oracle 19c
DECLARE
V_FirstName VARCHAR2(1000) := 'Test1';
V_LastName VARCHAR2(1000) := 'Swain';
V_SSN VARCHAR2(100);
json_doc CLOB;
BEGIN
SELECT TRUNC(DBMS_RANDOM.VALUE(900000000, 999999999)) INTO V_SSN FROM DUAL;
V_SSN := SUBSTR(V_SSN,1,3)||'_'||SUBSTR(V_SSN,4,3)||'_'||SUBSTR(V_SSN,7,3);
-- you can give your where condition or cursor condition
SELECT FORMS_DATA INTO json_doc FROM Account_Form WHERE Rownum =1 ;
update Account_Form
set Forms_Data = JSON_MERGEPATCH(Forms_Data,'{"authoritativeIds":{"usSsn":"******-**-***"}}');
DBMS_OUTPUT.PUT_LINE(json_doc);
END;