Team,
Could you please help me to understand why this JSON document load got failed with check constraint enabled?
but this is a Valid JSON document, validated it through http://jsonlint.com/ portal.
for time being, we are able to just load it with check constraint disabled, and then re-enable the constraint after the load completes.
demo@ORA12C> drop table t purge;
Table dropped.
demo@ORA12C> create table t(doc clob constraint t_valid_json
2 check(doc is json) );
Table created.
demo@ORA12C> declare
2 l_bfilename bfile;
3 l_temp clob;
4 begin
5 insert into t(doc)
6 values( empty_clob() )
7 returning doc into l_temp;
8 l_bfilename := bfilename('TMP','sample_json.txt');
9 dbms_lob.open(l_bfilename);
10 dbms_lob.loadfromfile(
11 dest_lob=>l_temp,
12 src_lob=>l_bfilename,
13 amount=>dbms_lob.getlength(l_bfilename));
14 commit;
15 dbms_lob.close(l_bfilename);
16 exception
17 when others then
18 if dbms_lob.isopen(l_bfilename)=1 then
19 dbms_lob.close(l_bfilename);
20 end if;
21 raise;
22 end;
23 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 897
ORA-06512: at line 18
ORA-02290: check constraint (DEMO.T_VALID_JSON) violated
demo@ORA12C> alter table t disable constraint t_valid_json;
Table altered.
demo@ORA12C> declare
2 l_bfilename bfile;
3 l_temp clob;
4 begin
5 insert into t(doc)
6 values( empty_clob() )
7 returning doc into l_temp;
8 l_bfilename := bfilename('TMP','sample_json.txt');
9 dbms_lob.open(l_bfilename);
10 dbms_lob.loadfromfile(
11 dest_lob=>l_temp,
12 src_lob=>l_bfilename,
13 amount=>dbms_lob.getlength(l_bfilename));
14 commit;
15 dbms_lob.close(l_bfilename);
16 exception
17 when others then
18 if dbms_lob.isopen(l_bfilename)=1 then
19 dbms_lob.close(l_bfilename);
20 end if;
21 raise;
22 end;
23 /
PL/SQL procedure successfully completed.
demo@ORA12C> alter table t enable constraint t_valid_json;
Table altered.
demo@ORA12C> select count(*) from t where doc is json;
COUNT(*)
----------
1
1 row selected.
demo@ORA12C> select dbms_lob.getlength(doc) from t where doc is json;
DBMS_LOB.GETLENGTH(DOC)
-----------------------
11877
1 row selected.
demo@ORA12C>
Sample_json.txt{
"bbccbb": 2705,
"bbccbbxxxxxxcccccOrddddd": "ddddd",
"aabbaa": "2016-01-12T15:17:35.666",
"aaaaadbbbbb": {
"bbccbb1": [],
"bbccbb2": "bbccbb",
"aabbaa": "2015-12-17T12:18:03.933+0000",
"bbccbb3": "1950-01-01",
"bbccbb4": "Mr",
"bbccbb5": 8,
"bbccbb6": "tester",
"bbccbbcccdddeee": { },
"cccdddeeecccdddeee1": {
"cccdddeee": {
"cccdddeeecccdddeee": {
"cccdddeee1": "zzzzz",
"ccccc": {
"pppppp": {
"cccdddeee": "Ooooo"
},
"rrrrr": {
"cccdddeee": "Ounce"
},
"cccdddeee2": 40
},
"aaaaa": "zzzzz",
"aaaaacccdddeee": "1965-01"
}
},
"cccdddeeeaaaaa": { }
},
"aaaaa": "testerson",
"bbbbbaabbaa": 2743.0,
"cccdddeee3": 2705.0,
"aabbaaqq": "Married"
},
"xxxxxxaabbaa": "bbccbb",
"fffffgggggeeeee": {
"eeeee": "800",
"sssss": "xxxxx xxxxx",
"eeeee1": "19-11-2015"
},
"cccdddeeegggggeeeee": {
"eeeee2": "512",
"sssss": "mmmmm",
"eeeeee": "19-06-2015"
},
"bbccbbxxxxxxccccddddd": "ddddd",
"bbccbb7": 46,
"aaaaaaabbaa": 4022,
"pppppp": {
"hhhhhs": [{
"jjjjj": 75,
"aaaaabbccbb": {
"aabbaa": "bbccbb"
},
"aabbaa": "2016-01-12T15:17:35.667",
"aabbaa1": [{
"iiiiiaabbaa": {
"aabbaa2": "ddddd",
"xxxxx": 25.0
},
"kkkkk": "unkkkkk",
"aabbaaccccc": 200000.0,
"ccccc": 200000.0,
"aabbaa3": "aabbaa",
"aabbaa4": 0,
"aabbaa5": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"mmmmmm": {
"aabbaa6": {
"nnnnnaabbaa": [{
"aabbaaedooooos": "Y",
"aabbaa7": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "qqqqq",
"sssss": "ppppp= qqqqq rrrrr aabbaa (aabbaa)",
"aabbaa8": "aabbaa",
"aabbaa9": "1086",
"ddddd1": 10.0,
"aabbaa10": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa11": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv wwwww ttttt (aabbaa) ",
"aabbaa12": "aabbaa",
"aabbaa13": "1271",
"ddddd2": 10.0,
"aabbaa14": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa15": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv wwwww aabbaa uuuuu (aabbaa) ",
"aabbaa16": "aabbaa",
"aabbaa17": "1272",
"ddddd3": 10.0,
"aabbaa18": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa19": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv wwwww uuuuu (aabbaa) ",
"aabbaa20": "aabbaa",
"aabbaa21": "1270",
"ddddd4": 10.0,
"aabbaa22": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa23": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv wwwww aabbaa aabbaa (aabbaa) ",
"aabbaa24": "aabbaa",
"aabbaa25": "1273",
"ddddd5": 10.0,
"aabbaa26": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa27": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv uuuuu",
"aabbaa28": "aabbaa",
"aabbaa29": "187",
"ddddd6": 10.0,
"aabbaa30": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa31": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv uuuuu (aabbaa)",
"aabbaa32": "aabbaa",
"aabbaa33": "1013",
"ddddd7": 10.0,
"aabbaa34": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa35": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv ttttt",
"aabbaa36": "aabbaa",
"aabbaa37": "185",
"ddddd8": 10.0,
"aabbaa38": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa39": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv ttttt (aabbaa)",
"aabbaa40": "aabbaa",
"aabbaa41": "1011",
"ddddd9": 10.0,
"aabbaa42": "O"
}, {
"aabbaaedooooos": "Y",
"aabbaa43": "2016-01-12T15:17:35.668",
"nnnnnaabbaa": "vvvvv",
"sssss": "vvvvv aabbaa",
"aabbaa44": "aabbaa",
"aabbaa45": "220",
"ddddd10": 10.0,
"aabbaa46": "O"
}]
},
"zaswed": {
"nnnnnaabbaa": []
}
},
"bbccbbaabbaa": "bbccbb",
"aabbaa47": 0,
"fffffcccdddeee": [{
"xxxxxxxxxxxxxxx": "yyyyy",
"aabbaa48": "2016-01-12T15:17:35.667",
"xxxxxxxxxxxxxxxaa": "yyyyy",
"xxxxxxxxxxxxxxxss": "zzzzz",
"aabbaa49": [{
"iiiiiaabbaa": {
"aabbaa50": "ccccc",
"xxxxx": 0.0
},
"kkkkklisation": "n/a",
"ccccc": 20000.0,
"aabbaa51": "aabbaa",
"aabbaa52": 0,
"aabbaa53": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"xxxxxxxxxx": 100.0,
"aabbaa54": "aabbaa",
"bbccbb55": 75,
"aabbaa56": "bbccbb",
"bbccbb57": "bbccbb",
"aaaaabbccbb": {
"aabbaa58": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"bbccbbaabbaa": "bbccbb",
"aabbaa59": 0,
"cccdddeeebbccbb": {
"aabbaa60": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa61": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "xxxxxx"
}, {
"xxxxxxxxxxxxxxx": "yyyyy",
"aabbaa62": "2016-01-12T15:17:35.667",
"xxxxxxxxxxxxxxxff": "yyyyy",
"xxxxxxxxxxxxxxxgg": "zzzzz",
"aabbaa63": [{
"iiiiiaabbaa": {
"aabbaa64": "ccccc",
"xxxxx": 0.0
},
"kkkkklisation": "n/a",
"ccccc": 20000.0,
"aabbaa65": "aabbaa",
"aabbaa66": 0,
"aabbaa67": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"xxxxxxxxxx": 100.0,
"aabbaa68": "aabbaa",
"bbccbb69": 75,
"aabbaa70": "bbccbb",
"bbccbb71": "bbccbb",
"aaaaabbccbb": {
"aabbaa72": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"bbccbbaabbaa": "bbccbb",
"aabbaa73": 1,
"cccdddeeebbccbb": {
"aabbaa74": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa75": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "xxxxxx"
}, {
"xxxxxxxxxxxxxxx78": "yyyyy",
"aabbaa76": "2016-01-12T15:17:35.667",
"xxxxxxxxxxxxxxx79": "yyyyy",
"xxxxxxxxxxxxxxx80": "zzzzz",
"aabbaa77": [{
"iiiiiaabbaa": {
"aabbaa81": "ccccc",
"xxxxx82": 0.0
},
"kkkkklisation": "n/a",
"ccccc": 20000.0,
"aabbaa83": "aabbaa",
"aabbaa84": 0,
"aabbaa85": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"xxxxxxxxxx86": 100.0,
"aabbaa87": "aabbaa",
"bbccbb88": 75,
"aabbaa89": "bbccbb",
"bbccbb90": "bbccbb",
"aaaaabbccbb": {
"aabbaa91": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"bbccbbaabbaa": "bbccbb",
"aabbaa92": 2,
"cccdddeeebbccbb": {
"aabbaa93": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa94": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "xxxxxx"
}, {
"xxxxxxxxxxxxxxx98": "yyyyy",
"aabbaa95": "2016-01-12T15:17:35.667",
"xxxxxxxxxxxxxxx99": "yyyyy",
"xxxxxxxxxxxxxxx100": "zzzzz",
"aabbaa96": [{
"iiiiiaabbaa": {
"aabbaa97": "ccccc",
"xxxxx": 0.0
},
"kkkkklisation": "n/a",
"ccccc102": 20000.0,
"aabbaa103": "aabbaa",
"aabbaa104": 0,
"aabbaa105": "aabbaa",
"lllllInaabbaa106": "aabbaa"
}],
"xxxxxxxxxx107": 100.0,
"aabbaa108": "aabbaa",
"bbccbb109": 75,
"aabbaa110": "bbccbb",
"bbccbb111": "bbccbb",
"aaaaabbccbb112": {
"aabbaa113": "bbccbb"
},
"cccdddeee114": 0.0,
"bbccbbxxxxx115": "yyyyy",
"bbccbbaabbaa116": "bbccbb",
"aabbaa117": 3,
"cccdddeeebbccbb118": {
"aabbaa119": "bbccbb"
},
"cccdddeeeddddd120": 0.0,
"bbccbbbbccbb121": {
"aabbaa122": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "xxxxxx"
}, {
"xxxxxxxxxxxxxxx123": "yyyyy",
"aabbaa124": "2016-01-12T15:17:35.668",
"xxxxxxxxxxxxxxx125": "yyyyy",
"xxxxxxxxxxxxxxx126": "zzzzz",
"aabbaa127": [{
"iiiiiaabbaa128": {
"aabbaa129": "ccccc",
"xxxxx130": 0.0
},
"kkkkklisation131": "n/a",
"ccccc": 20000.0,
"aabbaa132": "aabbaa",
"aabbaa133": 0,
"aabbaa134": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"xxxxxxxxxx": 100.0,
"aabbaa135": "aabbaa",
"bbccbb136": 75,
"aabbaa137": "bbccbb",
"bbccbb138": "bbccbb",
"aaaaabbccbb": {
"aabbaa139": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"bbccbbaabbaa": "bbccbb",
"aabbaa": 4,
"cccdddeeebbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "xxxxxx"
}],
"upcccdddeeebbccbb": {
"aabbaa": "bbccbb"
},
"bbccbbbbccbb": {
"aabbaa": "bbccbb"
}
}],
"fffffcccdddeee": [],
"cccdddeeecccdddeee": [{
"xxxxxxxxxxxxxxx": "yyyyy",
"aabbaa": "2016-01-12T15:17:35.668",
"xxxxxxxxxxxxxxxaaq": "yyyyy",
"aabbaaaav": [{
"iiiiiaabbaa": {
"aabbaa": "ddddd",
"xxxxx": 25.0
},
"kkkkklisation": "unkkkkklised",
"ccccc": 200000.0,
"aabbaa": "aabbaa",
"aabbaaaar": 0,
"aabbaaaas": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"aabbaaaaw": "aabbaa",
"aabbaaaat": "bbccbb",
"bbccbb": "bbccbb",
"aaaaabbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"cccdddeeeaabbaa": "bbccbb",
"bbccbbaabbaa": "bbccbb",
"aabbaaaau": 0,
"cccdddeeebbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "bbccbb"
}, {
"xxxxxxxxxxxxxxx": "yyyyy",
"aabbaaaav": "2016-01-12T15:17:35.668",
"xxxxxxxxxxxxxxxaaa": "yyyyy",
"aabbaaaab": [{
"iiiiiaabbaa": {
"aabbaa": "ddddd",
"xxxxx": 25.0
},
"kkkkklisation": "unkkkkklised",
"ccccc": 200000.0,
"aabbaaaac": "aabbaa",
"aabbaaaad": 0,
"aabbaaaae": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"aabbaaaaf": "aabbaa",
"aabbaaaag": "bbccbb",
"bbccbb": "bbccbb",
"aaaaabbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"cccdddeeeaabbaa": "bbccbb",
"bbccbbaabbaa": "bbccbb",
"aabbaaaah": 1,
"cccdddeeebbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "bbccbb"
}, {
"xxxxxxxxxxxxxxx": "yyyyy",
"aabbaaaai": "2016-01-12T15:17:35.668",
"xxxxxxxxxxxxxxxaak": "yyyyy",
"aabbaaaaj": [{
"iiiiiaabbaa": {
"aabbaa": "ddddd",
"xxxxx": 25.0
},
"kkkkklisation": "unkkkkklised",
"ccccc": 200000.0,
"aabbaa": "aabbaa",
"aabbaaaal": 0,
"aabbaaaam": "aabbaa",
"lllllInaabbaa": "aabbaa"
}],
"aabbaaaan": "aabbaa",
"aabbaaaao": "bbccbb",
"bbccbb": "bbccbb",
"aaaaabbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeee": 0.0,
"bbccbbxxxxx": "yyyyy",
"cccdddeeeaabbaa": "bbccbb",
"bbccbbaabbaa": "bbccbb",
"aabbaaaap": 2,
"cccdddeeebbccbb": {
"aabbaa": "bbccbb"
},
"cccdddeeeddddd": 0.0,
"bbccbbbbccbb": {
"aabbaa": "bbccbb"
},
"aabbaaxxxxxbbccbbAs": "bbccbb"
}]
},
"bbccbbxxxxxxddddd": 0.0,
"bbccbbxxxxxxccccc": 0.0,
"bbbbbaabbaa": 2743,
"bbccbbaaa": {
"hhhhhbbccbb": "J25611A",
"bbccbb": "123456",
"gggggbbccbb": "cccdddeee-009"
},
"bbccbbaab": "2016-01-12T15:17:35.666",
"bbccbbaabbaa": "cccdddeee",
"bbccbbxxxxxx": 0.0,
"bbccbbxxxxxxdddddaaa": 0.0
}