BookmarkSubscribeRSS Feed
DanK
Calcite | Level 5
I am accessing an Oracle table having a column of type BLOB. The contents of the BLOB is a 55768 byte string of characters. I am using PROC SQL to get one row, then I use a DATA step to parse through the string of characters using SUBSTR and known offsets. See sections of SASLOG below.

Can someone tell me how to expose the enire contents of the BLOB so I can parse out the data?

Thanks
DanK

proc sql;
connect to oracle (user=xxxxxxxx orapw=XXXXXXXX path="xxxxxxxx" dbmax_text=32765);

create table application_fdata as
select *
from connection to oracle
( select fdata_keycode,
fdata_app
from cmlbrc.application_fdata
where fdata_keycode='SAMKAXAA84'
);
NOTE: Table WORK.APPLICATION_FDATA created, with 1 rows and 2 columns.

data xtbl;
set application_fdata;

DET_KEYCODECENTER =substr(fdata_app,51-50,10);
RD_KEYCODE =substr(fdata_app,55-50,6);
DET_BUS_NAME_1 =substr(fdata_app,61-50,1);
DET_BUS_NAME_A =substr(fdata_app,61-50,4);
DET_BUS_NAME_B =substr(fdata_app,61-50,7);
DET_BUS_NAME =substr(fdata_app,61-50,48);
DET_BUS_NAME_2 =substr(fdata_app,61-50,48);
CLI_CLIENT_ID =substr(fdata_app,109-50,1);
CLI_CLIENT_ID_1 =substr(fdata_app,109-50,1);
CLI_CLIENT_ID_12 =substr(fdata_app,110-50,12);
LOG_CLIENT_ID =substr(fdata_app,110-50,12);
EQ_NO_HIT_IND =substr(fdata_app,122-50,1);
DBUS_NO_HIT_IND =substr(fdata_app,123-50,1);
EXPB_NO_HIT_IND =substr(fdata_app,124-50,1);
DET_CBR_PULL =substr(fdata_app,125-50,1);
DET_STATUS =substr(fdata_app,126-50,18);
...
...
...
EQCA_BUREAU_DOWN_FLAG =substr(fdata_app,55761-50,1);
DBCA_BUREAU_DOWN_FLAG =substr(fdata_app,55762-50,1);
TUCA_BUREAU_DOWN_FLAG =substr(fdata_app,55763-50,1);
CRED_BUREAU_DOWN_FLAG =substr(fdata_app,55764-50,1);
SBE_BUREAU_DOWN_FLAG =substr(fdata_app,55765-50,1);
DNB_BUREAU_DOWN_FLAG =substr(fdata_app,55766-50,1);
EXPB_BUREAU_DOWN_FLAG =substr(fdata_app,55767-50,1);
run;

ERROR: Observation length 179093500 too large for file WORK.XTBL.DATA. Maximum allowed for this file is 16777191.
NOTE: The SAS System stopped processing this step because of errors.
21 REPLIES 21
lu
Calcite | Level 5 lu
Calcite | Level 5
Hi DanK
Length of SAS character variable can't be more than 32K (32765)
in proc sql it's truncated to 32K
when you try to use substr with second parameter >32K, you get ERROR.


lu
DanK
Calcite | Level 5
There must be some way around this limitation. SAS indicates it can access BLOBs up to 2gig.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Searched and found this item on the SAS support http://support.sas.com/ website:

Problem Note 35928: An error occurs when you access data of types BLOB and CLOB from a DB2 table
http://support.sas.com/kb/35/928.html

Scott Barry
SBBWorks, Inc.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
I meant to say that the DB2 reference may be useful information. Also, the SAS/Access for Oracle 9.2 DOC specifically mentions CLOB/BLOB data types:

Data Types for Oracle
http://support.sas.com/documentation/cdl/en/acreldb/61890/HTML/default/a003113620.htm

Scott Barry
SBBWorks, Inc.
DanK
Calcite | Level 5
Thank you. I have looked though support.sas.com and Oracle forums looking for an answer. It's odd that SAS will state in the same document that a BLOB can store up to 2gig but it can only reference 32k of it.

There must be some work around for this. I am thinking I need to learn how to write a small C or Java program to dump the BLOB of interest to a flat file, invoke it from my SAS program then read the file with SAS. Grrrrrr.

Thanks
Dan
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest you open a SAS support tracking issue, given the situation.

Scott Barry
SBBWorks, Inc.
ChrisNZ
Tourmaline | Level 20
Maybe storing the blob value in a macro variable using select ... into would work, since your blobs are less than 64k. Then you can work on it using the macro language.

But if sas supports larger blobs, then there might indeed be a better solution and tech support should be able to tell you. Also, a macro variable might not handle binary values perfectly, but it doesn't hurt to try...

Another way would be to read the blob in chuncks smaller than 32k, using oracle's substring function. That would be much better than dumping into a text file! Message was edited by: C. Graffeuille
Bill
Quartz | Level 8
Could you not use the substr function in the passthru sql and bring back the long string in parts?
DanK
Calcite | Level 5
I tried this too. This is a SAS limitation that will only expose 32k of the BLOB. I have requested a fix from SAS as the 32k limitation seems useless when the BLOB could contain 2gig.

Thanks
Dan
DanK
Calcite | Level 5
If someone has a C program that I could use to read an Oracle table to return a BLOB and write it to a flat file, that would be great. The contents of this BLOB is a 64k text string.
DanK
Calcite | Level 5
If someone has a C program that I could use to read an Oracle table to return a BLOB and write it to a flat file, that would be great. The contents of this BLOB is a 64k text string.
Peter_C
Rhodochrosite | Level 12
I think you may have missed BIll's point.

SAS should not stop you using oracle substring functions in explicit-pass-thru sql.
select * from connection to oracle (
select substr( blob,1,32000 ) as blob1, substr( blob, 32001,32000 ) as blob2
)
was what Bill's idea suggested to me.

Or has this approach already failed?

PeterC
DanK
Calcite | Level 5
I did miss the point. I just tried and got a data type error.

15 proc sql;
16 connect to oracle (user=xxxxxxxx orapw=XXXXXXXX path="xxxxxxxx" dbmax_text=32767);

17 create table application_fdata as
18 select *
19 from connection to oracle
20 ( select substr(fdata_app,1,32000) as blob1, substr(fdata_app,32001,2000) as blob2
21 from cmlbrc.application_fdata
22 where fdata_keycode='SAMKAXAA84'
23 );
ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected NUMBER got BLOB. SQL statement: select
substr(fdata_app,1,32000) as blob1, substr(fdata_app,32001,2000) as blob2 from cmlbrc.application_fdata where
fdata_keycode='SAMKAXAA84'.
24 run;
Peter_C
Rhodochrosite | Level 12
you will need to adapt the substr() usage from my SAS style into syntax valid for oracle.

peterC

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
  • 21 replies
  • 5939 views
  • 0 likes
  • 8 in conversation