Read Oracle json database CLOB

[gtadda]

Hi i'm facing a problem reading an oracle 21.2 that has CLOB and JSON columns data type. 

The  same query in sql*plus or other GUI work perfectly but it doesnt exctrat any record if i launch it in SAS Pass Through.

I use pass Through to use the native ORACLE JSON functions such as JSON_TABLE, JSON_EXISTS and so on.

Tjhe client oracle is 19.1

Any help? Maybe an options in the connection string? 

Regards

[LinusH]

Please share your code and log.

[gtadda]

code:
proc sql _method ;
connect to oracle(user=user password='password'
PATH = pathname);
execute (alter session set current_schema = schema) by oracle;

create table table as
select * from connection to oracle
(
select j.*,d.JSON_field1 ,d.JSON_field2
from user.tableora d,
json_table (
d.JSON_field2, '$' columns (
id PATH '$.idnum',
date1 PATH '$.date1',
date2 PATH '$.date2')
)j
WHERE json_exists(d.JSON_field1 ,
'$[*]?(@.idnum == 11)')
);
DISCONNECT FROM ORACLE;
quit;

LOG:
NOTE: SQL execution methods chosen are:

sqxcrta
sqxextr( connection to oracle
/* dbms=oracle, connect options=(user=user password=XXXXX PATH = pathname) */ ( select j.*,d.JSON_field ,d.JSON_field2 from
user.tableora d, json_table ( d.JSON_field2, '$' colum ... ) )
NOTE: Table WORK.TABLE created, with 0 rows and 5 columns.

[LinusH]

So nothing in the log from Oracle itself?

I not sure if it helps, try add these options to your code:

options sastrace=',,,d' sastraqceloc=saslog nostsuffix msglevel=i;

If you don't get more information from this, see if a DBA can help you browse the Oracle internal logs.

Also, try a step that queries another table in the same schema with non-JSON data.

[gtadda]

Hi,

thanks for the answer, the options doesnt give any useful informations, i'm gonna aks the DBA for the details in log. 

And yes if i query a table with no json columns i get the records. 

[gtadda]

we just tried the same query on server with sql*plus and it works, maybe some missing options in sas code?

[Tom]

---

@gtadda wrote:
we just tried the same query on server with sql*plus and it works, maybe some missing options in sas code?

---

Do you mean you ran the exact same ORACLE code on the same ORACLE database using a different interface to ORACLE?

Did you sign-on using the same account?

Why did you include the 

alter session set current_schema = schema;

Your code does not look that complicated.  It wanted "table" created in "schema" then why not include that in the SQL to begin with?

create table "schema"."table" as

If you wanted "schema" to be your default schema why not just include that when you connected to ORACLE? Either in your SQL CONNECT statement or in the LIBNAME statement you used to create the connection to ORACLE.

[gtadda]

Do you mean you ran the exact same ORACLE code on the same ORACLE database using a different interface to ORACLE?
Yes i take the same query and the same user in the sql*plus interface and this is the result

[gtadda]

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 9 16:57:12 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle. All rights reserved.

SQL> connect user/xxxxxxxx@pathname
Connected.
SQL> @qq

id
--------------------------------------------------------------------------------
date1
--------------------------------------------------------------------------------
date2
--------------------------------------------------------------------------------
11
2023-09-14T00:00:00+0200
2024-09-14T00:00:00+0200

[Tom]

The lines SQL*PLUS drew under the variables names look very long.

Perhaps the TYPE of variable that your ORACLE query creates is not compatible with SAS variables?

Can you add CAST() functions into your SELECT so you are sure that the results are something SAS can load?

select cast(j.id as varchar(10)) ID
     , cast(j.date1 as varchar(24)) DATE1
     , cast(j.date2 as varchar(24)) DATE2
from ....

That alter schema does not appear to be doing anything since you are not making any table  so can you skip that step also in SQL*PLUS and see if it changes anything there?

Also I am not sure what the @qq in your SQL*PLUS log means.  Are you sure that is generating the exact same query as what you posted before in the SAS code?

[gtadda · Accepted solution]

solved ! the problem was in the code, you have to select only the flattened columns cause SAS doesnt understand the ORACLE CLOB or JSON data type. 

This works like a charm

proc sql _method ;
connect to oracle(user=user password='xxxx'
PATH = pathname);
execute (alter session set current_schema = schema) by oracle;

create table tablesas as
select * from connection to oracle
(
select j.*
from user.tableora d,
json_table (
d.JSON_field2, '$' columns (
id PATH '$.idnum',
date1 PATH '$.date1',
date2 PATH '$.date2')
)j
WHERE json_exists(d.JSON_field1 ,
'$[*]?(@.idnum == 11)')
);
DISCONNECT FROM ORACLE;
quit;