topic Length of column getting truncated while reading from Oracle DB in SAS Programming

Length of column getting truncated while reading from Oracle DB

abhik_giri — Tue, 08 Mar 2016 07:28:24 GMT

I need to look at the query for a view in Oracle DB. Hence I run the following: "select * from all_views where view_name=..." to get the record for a particular view. I get a truncated "Text" column. For the particular view, "Text_length" = 5558 but length of "Text" is only 1000.
Looks like this is a drawback of SAS as the URL http://support.sas.com/kb/36/237.html says that "Currently, there is no solution or workaround for this problem."

Please suggest what should be done.

Re: Length of column getting truncated while reading from Oracle DB

BrunoMueller — Tue, 08 Mar 2016 07:43:57 GMT

Have a look at the DBMAX_TEXT option. This option sets the maximum length of char variable coming from the DBMS.

See example below

proc sql;
  connect to oracle (
    path="host/dbms"
    user=yourUser
    password=yourPassword
    DBMAX_TEXT=32767
  );

  create table myViews as
  select
    *
  from 
    connection to oracle (
    select * from all_views 
  )
  ;
quit;

Bruno

Re: Length of column getting truncated while reading from Oracle DB

RW9 — Tue, 08 Mar 2016 09:18:32 GMT

In addition, I would check why the database has a field setup which is >5.5k characters in length in the first place. E.g. a) is a database a good format for what is essentially a book, and b) if it needs to be in the database, why is it not coded, or reduced into component rows of < 200 characters?