Skip to Main Content
  • Questions
  • Check constraint violated while loading JSON doc into DB

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: January 09, 2017 - 2:34 am UTC

Last updated: January 11, 2017 - 1:02 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

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
}

and Chris said...

The problem isn't your JSON document. It's inserting an empty_clob()!

create table t(
  doc clob constraint t_valid_json check(doc is json) 
);
insert into t values (empty_clob());

SQL Error: ORA-02290: check constraint (CHRIS.T_VALID_JSON) violated


To get around this, load the document using an external table instead of a bfile + dbms_lob.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SQL*Loader-416:

Rajeshwaran Jeyabal, January 09, 2017 - 2:09 pm UTC

Tried to generate External Table script from SQL*Loader got this error.

demo@ORA12C> create table t(doc clob);

Table created.

demo@ORA12C> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt external_Table=generate_only

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Jan 9 19:36:45 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-416: SDF clause for field DOC in table T references a non existent field.

C:\Users\179818>type d:\ctl.txt
load data
infile *
truncate into table t
(doc lobfile(filename) TERMINATED BY EOF)
begindata
d:\sample_json.txt

C:\Users\179818>

SQL*Loader-803

Rajeshwaran Jeyabal, January 10, 2017 - 2:54 am UTC

Thanks for the link, it really helps(able to understand the mistake on my part), but now ended up with a different issue.
Please let me know if any workarounds to this.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt external_Table=generate_only

SQL*Loader: Release 12.1.0.2.0 - Production on Tue Jan 10 08:17:17 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      External Table
SQL*Loader-803: Field type LOBFILE not supported by External Table

C:\Users\179818>type d:\ctl.txt
load data
infile *
truncate into table t
(
lob_filenm filler char(100),
doc lobfile(lob_filenm) TERMINATED BY EOF)
begindata
d:\sample_json.txt

C:\Users\179818>

Chris Saxon
January 10, 2017 - 5:35 pm UTC

You could just create the external table manually ;)

Thanks got it resolved.

Rajeshwaran Jeyabal, January 11, 2017 - 7:27 am UTC

demo@ORA12C> CREATE TABLE t_load_json (
  2    doc CLOB
  3  )
  4  ORGANIZATION EXTERNAL
  5  (
  6    TYPE ORACLE_LOADER
  7    DEFAULT DIRECTORY ETL_DATA_DIR
  8    ACCESS PARAMETERS
  9    (
 10      RECORDS DELIMITED BY NEWLINE
 11     FIELDS TERMINATED BY ','
 12      MISSING FIELD VALUES ARE NULL
 13      (
 14        clob_filename     CHAR(100)
 15      )
 16      COLUMN TRANSFORMS (doc FROM LOBFILE (clob_filename) FROM (ETL_DATA_DIR) CLOB)
 17    )
 18    LOCATION ('sample_data.txt')
 19  )
 20  REJECT LIMIT UNLIMITED ;

Table created.

demo@ORA12C> select dbms_lob.getlength(doc) from t_load_json;

DBMS_LOB.GETLENGTH(DOC)
-----------------------
                  11877

1 row selected.

demo@ORA12C> insert into t(doc)
  2  select doc
  3  from t_load_json;

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select dbms_lob.getlength(doc) from t where doc IS JSON;

DBMS_LOB.GETLENGTH(DOC)
-----------------------
                  11877

1 row selected.

demo@ORA12C>

Connor McDonald
January 11, 2017 - 1:02 pm UTC

nice work

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here