如何在sql*plus中使用dbms_lob.substr
如何在sql*plus中使用dbms_lob.substr
提问于 2019-09-11 01:10:37
给定一张表：
desc SOL_3_ARTIFACTS
Name Null Type
---- ---- -------------
DATA BLOB
ID VARCHAR2(100) 如果需要使用SQL*plus将BLOB列的内容读入为VARCHR2，则需要如下语句：
select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(lob_loc,2000,1))
from SOL_3_ARTIFACTS where ID = 'RETAILPOOLS'有人能告诉我如何获得特定行的lob_loc (即数据列的定位器) (ID = 'RETAILPOOLS')吗？
根据贝莱尔的建议，我运行了文件夹，但得到了错误：
DECLARE
fil BFILE;
pos INTEGER;
amt BINARY_INTEGER;
buf RAW(40);
BEGIN
SELECT SOL_3_ARTIFACTS.DATA INTO fil from SOL_3_ARTIFACTS where ID = 'RETAILPOOLS';
dbms_lob.open(fil, dbms_lob.lob_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.close(fil);
exception
WHEN no_data_found
THEN
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
Error report -
ORA-06550: line 7, column 13:
PL/SQL: ORA-00932: inconsistent datatypes: expected FILE got BLOB
ORA-06550: line 7, column 6:
PL/SQL: SQL Statement ignored
ORA-06550: line 8, column 25:
PLS-00201: identifier 'DBMS_LOB' must be declared
ORA-06550: line 8, column 6:
PL/SQL: Statement ignored
ORA-06550: line 9, column 28:
PLS-00201: identifier 'DBMS_LOB' must be declared
ORA-06550: line 9, column 17:
PL/SQL: Statement ignored
ORA-06550: line 10, column 6:
PLS-00201: identifier 'DBMS_LOB' must be declared
ORA-06550: line 10, column 6:
PL/SQL: Statement ignored
ORA-06550: line 13, column 6:
PLS-00201: identifier 'DBMS_LOB' must be declared
ORA-06550: line 13, column 6:
PL/SQL: Statement ignored
ORA-06550: line 19, column 10:
PLS-00201: identifier 'DBMS_LOB' must be declared
ORA-06550: line 19, column 10:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:Stack Overflow用户
回答已采纳
发布于 2019-09-11 23:19:14
试着从这里开始
doc/appdev.901/a88879/adl02bs4.htm
特别是下面这个部分，它指出当您选择blob时，它会返回lob定位器。
通过Locator访问LOB
在LOB上执行SELECT的LOB返回定位器，而不是LOB值。在下面的PL/SQL片段中，您将为故事选择LOB定位器，并将其放在程序块中定义的PL/ select变量Image1中。当您使用PL/SQL DBMS_LOB函数操作LOB值时，可以使用定位器引用LOB。
DECLARE
Image1 BLOB;
ImageNum INTEGER := 101;
BEGIN
SELECT story INTO Image1 FROM Multimedia_tab
WHERE clip_id = ImageNum;
DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
DBMS_LOB.GETLENGTH(Image1));
/* more LOB routines */
END;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57879844
复制相关文章
相似问题
腾讯云开发者
