I have issue when reading CLOB values that are greater than 32767 characters in length in oracle table thru SAS. For example, I have to read a record's CLOB value which has a length = 70000. I am using DBMAX_TEXT=32767 statement in the option, but the value are got truncated after 32767. Please someone help me how do we get values without truncate thru sas.
we are trying to automate data pull process from Oracle to SAS. Do we have any way to handle thru SAS?
@ez123 - What is actually stored in the CLOB? Is it structured data or not? If it is non-structured then that makes it hard to split it into usable chunks.
that field have json format data
Hi @Abraham,
The following Oracle function can be useful.
proc sql ;
connect to oracle ... ;
create table want as select * from connection to oracle (
select
regexp_replace(clob_var), '[[:space:]]+', chr(4000)) as new_var,
from mytable a
);
quit;
Best,
I think that you @Ksharp mentioned the options that I can think of
You need to use pass thru code so you can call the Oracle function DBMS_LOB.SUBSTR(), which is lot like the SAS SUBSTR() function except the start position and length have swapped places and it counts by CHARACTERs instead of BYTES, to break the CLOB into pieces. Make sure to take fewer than 32767 characters in each piece to allow some room for multi-byte characters. For example you might decide to split your 70K clob into strings of lengths 24K,24K and 22K.
proc sql ;
connect to oracle ... ;
create table want as select * from connection to oracle (
select a.var1
, a.var2
, DBMS_LOB.SUBSTR(a.clob_var,24000,1) as clob1
, DBMS_LOB.SUBSTR(a.clob_var,24000,24001) as clob2
, DBMS_LOB.SUBSTR(a.clob_var,22000,48001) as clob3
from mytable a
);
quit;
Thank you!!!
I am using exactly same as like example but getting below error . Even i am using dbmax_text=32767.
ERROR: ORACLE execute error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1.
Please post your complete SAS log including code.
proc sql;
connect to oracle ( user="XXXX" password="SSSSS"
path="path" dbmax_text=32767);
create table in1.test1 as select * from connection to oracle
( select DBMS_LOB.SUBSTR(a.field,32000,1) as clob1
, DBMS_LOB.SUBSTR(a.field,32000,32001) as clob2
from schema.mytable a);
disconnect from oracle;
quit;
Maybe ?
cast(DBMS_LOB.SUBSTR(a.clob_var,24000,1) as varchar(32767)) as clob1
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.