通过 SAS 从 ORACLE DB 导入 blob

2022-01-08 00:00:00 blob sas oracle

祝大家度过美好的一天.我在前一周的工作中遇到了一个巨大的问题.这是交易:

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中任何变量的长度都是1024(而不是37767)$HEX2024格式.如果我愿意:

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?

谢谢.

编辑 1:

我得到了信息，但最大字符串是 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.

相关文章