DATA Step, Macro, Functions and more

Length of column getting truncated while reading from Oracle DB

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Length of column getting truncated while reading from Oracle DB

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.


Accepted Solutions
Solution
‎03-08-2016 04:27 AM
SAS Super FREQ
Posts: 683

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

Hi

 

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

View solution in original post


All Replies
Solution
‎03-08-2016 04:27 AM
SAS Super FREQ
Posts: 683

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

Hi

 

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

Super User
Super User
Posts: 7,413

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

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?  

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 266 views
  • 0 likes
  • 3 in conversation