Unable to read full variable (CLOB type) from Oracle to Sas

[devi001]

Hello everyone,

We are trying to read the table having one variable type as clob into SAS but the length is truncating.

We want to create a table from Oracle to sas work library.

Tried below options but didn't work :

1. Used pass through facility & created new table in Oracle. Changed the CLOB type to VARCHAR

2. Data step with new column creation & reading CLOB variable to it

Data test;

Length remarks1 $4000.;

Set oradb.tbl;

Remarks1 = remarks;

Run;

3. Trying to print the Oracle table in sas, however remarks column is truncated.

Proc print data= orada.tbl; run;

We found one option dbmax_text to use in libname statement. But will that work while creating the table in sas work library?

Please suggest the solution so that we can create the table in sas work library & able to read clob variable without truncation.

Thanks in advance!

[Ksharp]

1) using dbmax_text= option to have more length for that CLOB variable:

libname orada oracle path= user= pw= ....... dbmax_text=32767;



2) using proc contents to check the variable length:

data have;
set oradb.tbl;
run;
proc contents data=have;
run;

If you want to use proc print, you need add a format to display full value.

Proc print data= orada.tbl;

format remarks $32767.;

run;

[Patrick]

SAS 9 character variables can't store more than 32kb. Furthermore the default setting for dbmax_text= is 4000 bytes. An Oracle Clob can store up to 4GB.

As @Ksharp already proposed you can increase dbmax_text= to 32kb on the SAS side so that at least the max string that a SAS character variable can store gets also transferred from Oracle. 

If you're dealing with multibyte characters then be aware that the SAS length define the number of bytes and not characters. A utf-8 character can take up to 4 bytes.

If the Clob stores more than 32kb then you need to split it up on the Oracle side before transferring the data to SAS (explicit SQL pass-through). If you have some idea about the max size of this clob and it's not too massive then you could just populate multiple variables with 32KB "junks", if that would create too many variable then you could also create a row per 32kb chunk. You then download this new table structure into SAS.

Above splitting will require some plsql. You will find discussions/documentation around it ...or just first try if you can make this already work by asking Copilot or ChatGPT.