How to extract JSON data from a Oracle column

[Cugel]

I'm looking for a way to read a JSON column from an Oracle table into SAS.
The Oracle table contains a column called "Document" in which a JSON structure is stored.
Now our organization uses an ODBC link to Oracle but the ODBC link has a 1024 limitation on the number of characters.
The "Document" column contains 32767 characters broken down into a large number of JSON arrays.
Is there a possibility to access this Oracle table directly on the Oracle server and then read it into SAS tables?.

[Ksharp · Accepted solution]

Try libname 's option DBMAX_TEXT= .

libname x odbc dsn=xxx dbmax_text=32767 ;

[Cugel]

I will discuss with our technical colleagues whether this is the solution to our problem.

[Tom]

In general no.  Unless you want to write your own logic to scan the text and figure out what parts are the data and what parts and the variable names.

But you could perhaps write the data into an actual FILE and then try using the JSON libref engine and see if SAS can parse the JSON into something that at least looks a little like data.

Make sure to add DBMAX_TEXT=32767 dataset option so you can pull out all of the text.

You might also need add some extra text to the generated file so that the result is valid JSON syntax.  If nothing else perhaps opening and closing square brackets and commas between the lines.

Try something like:

filename json temp;
data _null_;
  set mylib.mydata(dbmax_text=32767) end=eof;
  if _n_=1 then put '[' @;
  else put ',' @;
  put json_text ;
  if eof then put ']';
run;
libname json json ;
proc copy inlib=json out=work;
run;