0

I have json object in CLOB data format in pl/sql, now I need to get each data-field separately from that json object to varchar variable. Without using pljson.

Janitha Madushan
  • 1,453
  • 3
  • 28
  • 40

1 Answers1

1

the simplest way is to use a 3rd party library if you using Oracle up to 11. A good library for me is pljson, which can handle CLOBs but there are maybe other libraries.

With Oracle 12c you can parse JSON natively via SQL; This is one of the great new features of 12c.

Update:
A very simple and crude aproach is to use the regexp_substr function to extract a dedicated key from a CLOB/VARCHAR2 like this:

REGEXP_SUBSTR(T.JOSN_CLOB, '\s*"*(key1)"*\s*:\s*"(.*)"', 1, 1, 'im', 2)

where key1 is your key of interest in your clob. This may not very fast and the regular expression could be more professional, but you can extract simple values.
Another way is to examine the pljson package and rename the parts which cause the problem by installing via the APEX SQL Workshop OR write a JAVA stored procedure/function with a appropriate JSON library and call this function through a wrapper function in PL/SQL or SQL.

Recoil
  • 168
  • 7
  • I am facing a difficulty to install pljson. So I am seeking another way to do this. – Janitha Madushan Feb 05 '15 at 14:20
  • What kind of difficulty? I think it's straight forward with the installscript, assuming you have all necessary privileges. – Recoil Feb 05 '15 at 14:27
  • I am using oracle apex, So I am going to install those scripts through apex user. While I am trying to install it shows, some method names are already exist. – Janitha Madushan Feb 06 '15 at 02:40
  • I tried this REGEXP but it captures all data until the last `"`. So, if JSON is `{"key0":"data0","key1":"data1","key2":288}`, it returns ´data1","key2´ I think problem is that `.` match every char and capture group doesn't stop until no `"` found. – jabujavi Feb 12 '16 at 12:01