title: Parsing JSON in Oracle with attribute longer than 4000
body: <p>I'm trying to parse JSON attribute with value longer than 4000 characters. This is my sample json:</p>
  <pre><code>[{
          &quot;id&quot;: &quot;268edbb5d111&quot;,
          &quot;name&quot;: &quot;Sample Product&quot;,
          &quot;status&quot;: &quot;created&quot;,
          &quot;description&quot;: &quot;this is sample product&quot;,
          &quot;productCharacteristic&quot;: [{
                  &quot;name&quot;: &quot;property_1&quot;,
                  &quot;value&quot;: &quot;abc&quot;,
                  &quot;valueType&quot;: &quot;String&quot;
              }, {
                  &quot;name&quot;: &quot;property_2&quot;,
                  &quot;value&quot;: 123,
                  &quot;valueType&quot;: &quot;Number&quot;
              }, {
                  &quot;name&quot;: &quot;property_3&quot;,
                  &quot;value&quot;: &quot;2020-05-01T04:56:07.000+00:00&quot;,
                  &quot;valueType&quot;: &quot;Date&quot;
              }
          ]
      }
  ]
  </code></pre>
  <p>and this is PL/SQL code:</p>
  <pre><code>FOR x IN (SELECT *
              FROM json_table(l_response, '$[*]'
                   COLUMNS
                   id                        varchar2(255)   PATH '$.id',
                   name                      varchar2(255)   PATH '$.name',
                   description               varchar2(255)   PATH '$.description',
                   status                    varchar2(255)   PATH '$.status',
                   productCharacteristic     varchar2(4000)  FORMAT JSON PATH '$.productCharacteristic'
                   )
              ) LOOP
    -- do something
  END LOOP;
  </code></pre>
  <p>The productCharacteristic attribute might be longer than 4000 but changing its datatype to VARCHAR2(32000) compiles the package with ORA-00910: specified length too long for its datatype and changing to CLOB returns ORA-40484: invalid data type for JSON_TABLE column. Is there maybe a way to pass the whole array of productCharacteristic as JSON and parse it inside of the loop?</p>
  <p>Thanks.</p>