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?
connect to oracle (user=xxxxxxxx orapw=XXXXXXXX path="xxxxxxxx" dbmax_text=32765);
create table application_fdata as
from connection to oracle
( select fdata_keycode,
NOTE: Table WORK.APPLICATION_FDATA created, with 1 rows and 2 columns.
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.
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
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.
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'
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