Working with Oracle BLOB

[DanK]

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.

[lu]

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]

There must be some way around this limitation. SAS indicates it can access BLOBs up to 2gig.

[sbb]

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]

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]

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]

Suggest you open a SAS support tracking issue, given the situation.

Scott Barry
SBBWorks, Inc.

[ChrisNZ]

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]

Could you not use the substr function in the passthru sql and bring back the long string in parts?

[DanK]

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]

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]

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]

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]

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]

you will need to adapt the substr() usage from my SAS style into syntax valid for oracle.

peterC