BookmarkSubscribeRSS Feed
tmg02
Calcite | Level 5

I am trying to read an Oracle view using Windows Base SAS.  The SAS error log is returning and error message "Error fetching from cursor.  ORACLE error is ora-22835.  Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4254, maximum: 4000).  SAS read 210000 observations before abnormally terminating.  Does anyone know a solution to this issue?

 

Thanks,  tmg02 

6 REPLIES 6
LaurieF
Barite | Level 11

Can you show your query? At least the bit where you're referencing a CLOB column (I assume that's the problem).

 

In the meantime, if you don't need to read the whole of the column, a substr may help.

tmg02
Calcite | Level 5

Hi Laurie,

 

Thanks for offering to help.  I'm not actually referencing the CLOB column.  The code references the Oracle view and I assume that one of the fields in this view is a CLOB or BLOB.  I'm not sure which field is throwing the error, but SAS successfully reads over 200,000 records before throwing the error.  Unfortunately, there is no data dictionary available for the view and IT is not willing to provide one.  Maybe I should start with a proc contents to see what is actually stored on Oracle?

 

-Todd

LaurieF
Barite | Level 11
proc contents will certainly help for a start. It might give some clues.
Ksharp
Super User
You can drop that column if it does not mean anything.
OR
you can convert it into CHAR at ORACLE side, and import it into SAS .

create view xx as
select ......,tochar(CLOB) as new


tmg02
Calcite | Level 5
How do I figure out which field in the view is causing the error? I only have read rights on the Oracle view so I need to have a SAS solution. Thanks for your help. Much appreciated.
LaurieF
Barite | Level 11

I don't have Oracle access here, but you may be able to investigate the metadata Oracle table all_tab_columns where table_name = your table. That will show the column types.

 

Alternatively, proc contents on the Oracle view or do a subset extract:

proc sql outobs=10000;
create table x 
   from connection to oracle( 
etc etc
);
(or from view of Oracle table, of course) quit;

then do a contents on that.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1707 views
  • 0 likes
  • 3 in conversation