通过SAS从ORACLE DB导入Blob [英] Import blob through SAS from ORACLE DB

问题描述

一天的美好时光给大家. 在前一周的工作中，我面临着一个巨大的问题. 这是交易:

Good time of a day to everyone. I face with a huge problem during my work on previous week. Here ia the deal:

我需要通过SAS从ORACLE数据库下载exel文件(blob). 我正在使用:

I need to download exel file (blob) from ORACLE database through SAS. I am using:

1. 第一步，我需要从oracle获取数据.我使用了结构(blob文件将近100kb):

1. First step i need to get data from oracle. I used the construction (blob file is nearly 100kb):

proc sql;
connect to oracle;
create table SASTBL as 
select * from connection to oracle (
select dbms_lob.substr(myblobfield,1,32767) as blob_1,
dbms_lob.substr(myblobfield,32768,32767) as blob_2,
dbms_lob.substr(myblobfield,65535,32767) as blob_3,
dbms_lob.substr(myblobfield,97302,32767) as blob_4

  from my_tbl;
);
quit;

结果是:

    blob_1 = 70020202020202...02
    blob_2 = 02020202020...02
    blob_3 = 02020202...02

我不明白为什么该字段由"02"(整个文件)组成

I do not understand why the field consists from "02"(the whole file)

并且sas中任何变量的长度都是$ HEX2024格式的1024(而不是37767). 如果我愿意:

And the length of any variable in sas is 1024 (instead of 37767) $HEX2024 format. If I ll take:

dbms_lob.substr(my_blob_field，2000,900)来自同一对象，其结果将类似于真实情况: blob ="A234ABC4536AE7 ...."

dbms_lob.substr(my_blob_field,2000,900) from the same object the result will mush more similar to the truth: blob = "A234ABC4536AE7...."

问题是:1.如何通过SAS从blob字段正确获取二进制数据?我怎么了?

The question is: 1. how can i get binary data from blob field correctly trough SAS? What is my mistake?

谢谢.

我得到了信息，但是最大字符串是2000 kb.

I get the information but max string is 2000 kb.

推荐答案

在CONNECT语句(或LIBNAME语句)上使用DBMAX_TEXT选项最多可获取32,767个字符.默认值可能是1024.

Use the DBMAX_TEXT option on the CONNECT statement (or a LIBNAME statement) to get up to 32,767 characters. The default is probably 1024.

这篇关于通过SAS从ORACLE DB导入Blob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！