topic Re: Working with Oracle BLOB in SAS Programming

Working with Oracle BLOB

DanK — Wed, 20 May 2009 17:18:26 GMT

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.

Re: Working with Oracle BLOB

lu — Wed, 20 May 2009 19:05:04 GMT

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

Re: Working with Oracle BLOB

DanK — Wed, 20 May 2009 19:26:07 GMT

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

Re: Working with Oracle BLOB

sbb — Wed, 20 May 2009 19:33:06 GMT

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.

Re: Working with Oracle BLOB

sbb — Wed, 20 May 2009 19:40:38 GMT

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.

Re: Working with Oracle BLOB

DanK — Wed, 20 May 2009 19:50:40 GMT

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

Re: Working with Oracle BLOB

sbb — Wed, 20 May 2009 21:36:03 GMT

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

Scott Barry
SBBWorks, Inc.

Re: Working with Oracle BLOB

ChrisNZ — Thu, 21 May 2009 10:54:25 GMT

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

Re: Working with Oracle BLOB

Bill — Thu, 21 May 2009 13:47:37 GMT

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

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 14:09:45 GMT

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

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 14:15:01 GMT

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.

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 14:15:15 GMT

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.

Re: Working with Oracle BLOB

Peter_C — Thu, 21 May 2009 15:02:55 GMT

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

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 15:51:14 GMT

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;

Re: Working with Oracle BLOB

Peter_C — Thu, 21 May 2009 16:39:17 GMT

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

peterC

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 17:05:20 GMT

I tried the same statement directly in Oracle SQL Developer and got the same ORA-00932 error. I have no clue on how to get around this.

Re: Working with Oracle BLOB

DanK — Thu, 21 May 2009 17:22:25 GMT

-----Original Message-----
From: SAS Technical Support [mailto:support@sas.com]
Sent: Thursday, May 21, 2009 1:18 PM
To: Kanze, Daniel R (GE Money)
Subject: [SAS 7610215082] Reading Oracle BLOB

Dan,
Yes, I will open a defect requesting and enhancement for a future release.

Regards,
Donna
SAS Technical Support

Re: Working with Oracle BLOB

ChrisNZ — Fri, 22 May 2009 01:31:27 GMT

You might need to convert the blob to a string (if oracle can do this) as substr probably does not take blobs as argument.

Re: Working with Oracle BLOB

DanK — Fri, 22 May 2009 12:17:02 GMT

I tried to_char(blob) in an Oracle select but that did not solve the problem. substr(blob,1,32000) works in SAS after retrieving the data from Oracle but select substr(blob,1,3200) as myblob will not work in a select statement.

Someone, somewhere has delt with this issue and solved it.

Thanks
Dan

Re: Working with Oracle BLOB

LinusH — Tue, 26 May 2009 10:25:35 GMT

I suggest that you post you problem on a Oracle forum instead, since it's quite clear this could not be solved with any SAS specific tool. Probably both suggested ways are feasible (substr and export to text-file).

/Linus