BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
gtadda
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
gtadda
Fluorite | Level 6

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;

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

Please share your code and log.

Data never sleeps
gtadda
Fluorite | Level 6
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
Tourmaline | Level 20

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.

 

Data never sleeps
gtadda
Fluorite | Level 6

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
Fluorite | Level 6
we just tried the same query on server with sql*plus and it works, maybe some missing options in sas code?
Tom
Super User Tom
Super User

@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
Fluorite | Level 6
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
Fluorite | Level 6
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
Super User Tom
Super User

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
Fluorite | Level 6

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1547 views
  • 0 likes
  • 3 in conversation