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

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

ez123 — Thu, 21 May 2020 15:59:19 GMT

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.

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

Ksharp — Fri, 22 May 2020 12:03:12 GMT

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 ?

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

ez123 — Wed, 27 May 2020 12:14:35 GMT

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

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

Ksharp — Wed, 27 May 2020 12:49:49 GMT

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 ?

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

Ksharp — Wed, 27 May 2020 12:55:55 GMT

And @LinusH @SASKiwi maybe have good ideas.

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

LinusH — Wed, 27 May 2020 13:52:16 GMT

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

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

SASKiwi — Wed, 27 May 2020 20:32:42 GMT

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

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

Tom — Thu, 28 May 2020 01:37:25 GMT

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;

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

ez123 — Thu, 28 May 2020 23:17:04 GMT

that field have json format data

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

ez123 — Thu, 28 May 2020 23:21:47 GMT

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.

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

SASKiwi — Fri, 29 May 2020 00:06:40 GMT

Please post your complete SAS log including code.

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

ChrisNZ — Fri, 29 May 2020 05:26:04 GMT

Maybe ?

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

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

ez123 — Fri, 29 May 2020 13:09:50 GMT

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;

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

TET_34 — Thu, 17 Dec 2020 12:52:49 GMT

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,