Insert long text variable from SAS into Oracle database table

[marieK]

Hi,

I have  a SAS dataset with a text-variable (diags, 8000 characters) which I need to insert into an Oracle database table. Unfortunately the length of the variable is always truncated to 1024 characters, even with DBMAX_TEXT=8000 and CLOB datatype. I don´t get any error messages or warnings in the log. Do you have any ideas what could be the problem here? Thank you very much!

I use the following code with SAS 9.3:

libname _d2s_lib oracle user=xxx password="xxx" path=xxx schema=xxx DBMAX_TEXT=8000;

proc sql;
    connect to oracle (user=xxx password="xxx" path=phdb2b DBMAX_TEXT=8000);
        execute ( alter table xxx.test1 add
                ("IDNO" number, "SEX" number, "AGE" number, "DIAGS" CLOB) )
        by oracle;
    disconnect from oracle;
quit;

proc sql;
  insert into xxx.test1
    select * from work.test;
quit;

[kiranv_]

have this options 

 options sastrace=",,,d" sastraceloc=saslog no$stsuffix;

 and try this 

proc sql;
  insert into xxx.test1 (DBMAX_TEXT=8000)
    select * from work.test;
quit;

[marieK]

Thank you very much for your answer. Unfortunately it is not working out as well... I found out, that the variable is already 1024 characters long after I added it with the alter table add DIAGS CLOB. So even before I insert the values.

[kiranv_]

what you are saying indicates that it is getting assigned to default length that is $1024. I guess thhis is probably configuration level settings, which is overwriting your libname options, datset options. if possible, please contact your sas admin and discuss about his. I am very curious to know outcome of this.

[ChrisBrooks]

It looks like the destination field is a CLOB (Character Large Object) and not a regular varchar. SAS doesn't have the concept of a CLOB so it looks like some unwanted conversion is going on. Many years ago I wrote a macro to store a SAS data set into an ORACLE BLOB (Binary Large Object) field that works for CLOBs as well. You run the macro, passing parameters which enable it to build all the files necessary to run the ORACLE Bulkload utility then shell out to the operating system and run the Bulkloader with the generated parameters.

Although I no longer have direct access to the code I wrote, a google search (and I'd completely forgotten this) showed me that I'd posted it as an answer to a question on another forum a long time ago - it's the third post on this page https://groups.google.com/forum/#!topic/comp.soft-sys.sas/nH9P9oATX3E so you could try adapting it for your purpose.

[marieK · Accepted solution]

The solution is very simple 😉

It seems that the viewer to look at the Oracle datatables in SAS has a limit of 1024 characters for text variables in Oracle. As soon as I look at the data with another tool (sqldeveloper) I can see all values without truncation. If I read the Oracle dataset back into SAS with the DBMAX_TEXT=8000, I can also see the untruncated values in the work SAS dataset test3.

data test3;
    set xxx.test1(DBMAX_TEXT=8000);
run;