How do read CLOB values which has length greater than 32767 in Oracle table thru SAS

[ez123]

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.

[Ksharp]

32767 is the max length of character variable in sas . It is sas limitation .
Try dump the oracle table into a csv file ,and import it via data step ?

[ez123]

we are trying to automate data pull process from Oracle to SAS. Do we have any way to handle thru SAS? 

[Ksharp]

Can you let Oracle DB do it ?
First, use TOCHAR(clob_variable) to change it into a string,
and split it into multiple variables
and import these variables into SAS ?

[SASKiwi]

@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.

[ez123]

that field have json format data

[TET_34]

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,

[Ksharp]

And @LinusH  @SASKiwi  maybe have good ideas.

[LinusH]

I think that you @Ksharp mentioned the options that I can think of

[Tom]

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;

[ez123]

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.

[SASKiwi]

Please post your complete SAS log including code.

[ez123]

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;

[ChrisNZ]

Maybe ?

 cast(DBMS_LOB.SUBSTR(a.clob_var,24000,1) as varchar(32767)) as clob1