The DBMS_LOB package provides subprograms to operate on BLOBs, CLOBs, NCLOBs, BFILEs, and temporary LOBs. You can use DBMS_LOB to access and manipulation specific parts of a LOB or complete LOBs. This package must be created under SYS (connect internal). Operations provided by this package are performed under the current calling user, not under the package owner SYS. DBMS_LOB can read and modify BLOBs, CLOBs, and NCLOBs; it provides read-only operations for BFILEs. The bulk of the LOB operations are provided by this package.
解决方案 »
- win2008 r2 oracle rac搭建 报网络基础结构配置错误
- ORA-12514: TNS: 监听程序无法识别连接描述符中请求的服务
- expdp/impdp使用简介,替代exp/imp
- Oracle 网络访问不通(别人访问我),怎么做? (提示“无监听程序”)
- Oracle 错误码(用的是Toad)
- 有 没有配置OMS的资料
- 急,急,急!数据库停电.再进入出现"ora-01033 oracle initialization or shutdown in progress"
- 高手请进,帮我看看这个程序,该如何优化!!谢谢
- 谁能给我解释一下svrmgr和lsnrctl的作用是什么?
- oracle 字符集 ZHS16GBK 与utf8 的问题
- 在线等待,建表时字段类型为 CLOB 时出的错,求急,,,,!!!
- 数据库无法创建:“ORA-02231:??????ALTER DATABASE??”急急急!
dbms_lob.instr用来在lob值上执行sql语句的instr函数。
This function returns the matching position of the nth occurrence of the pattern in the LOB, starting from the offset you specify.The form of the VARCHAR2 buffer (the pattern parameter) must match the form of the CLOB parameter. In other words, if the input LOB parameter is of type NCLOB, then the buffer must contain NCHAR data. Conversely, if the input LOB parameter is of type CLOB, then the buffer must contain CHAR data.For BFILEs, the file must be already opened using a successful FILEOPEN operation for this operation to succeed.Operations that accept RAW or VARCHAR2 parameters for pattern matching, such as INSTR, do not support regular expressions or special matching characters (as in the case of SQL LIKE) in the pattern parameter or substrings.Syntax
DBMS_LOB.INSTR (
lob_loc IN BLOB,
pattern IN RAW,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;DBMS_LOB.INSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;DBMS_LOB.INSTR (
file_loc IN BFILE,
pattern IN RAW,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;Pragmas
pragma restrict_references(INSTR, WNDS, WNPS, RNDS, RNPS);Parameters
Table 23-29 INSTR Function Parameters
Parameter Description
lob_loc Locator for the LOB to be examined.
file_loc The file locator for the LOB to be examined.
pattern Pattern to be tested for. The pattern is a group of RAW bytes for BLOBs, and a character string (VARCHAR2) for CLOBs.The maximum size of the pattern is 16383 bytes.
offset Absolute offset in bytes (BLOBs) or characters (CLOBs) at which the pattern matching is to start. (origin: 1)
nth Occurrence number, starting at 1.
Returns
Table 23-30 INSTR Function Returns
Return Description
INTEGER Offset of the start of the matched pattern, in bytes or characters. It returns 0 if the pattern is not found.
NULL Either: -any one or more of the IN parameters was NULL or INVALID. -offset < 1 or offset > LOBMAXSIZE. -nth < 1. -nth > LOBMAXSIZE.
Exceptions
Table 23-31 INSTR Function Exceptions for BFILES
Exception Description
UNOPENED_FILE File was not opened using the input locator.
NOEXIST_DIRECTORY Directory does not exist.
NOPRIV_DIRECTORY You do not have privileges for the directory.
INVALID_DIRECTORY Directory has been invalidated after the file was opened.
INVALID_OPERATION File does not exist, or you do not have access privileges on the file.
Examples
CREATE OR REPLACE PROCEDURE Example_12a IS
lobd CLOB;
pattern VARCHAR2 := 'abcde';
position INTEGER := 10000;
BEGIN
-- get the LOB locator
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 21;
position := DBMS_LOB.INSTR(lobd,
pattern, 1025, 6);
IF position = 0 THEN
dbms_output.put_line('Pattern not found');
ELSE
dbms_output.put_line('The pattern occurs at '
|| position);
END IF;
END;CREATE OR REPLACE PROCEDURE Example_12b IS
DECLARE
fil BFILE;
pattern VARCHAR2;
pos INTEGER;
BEGIN
-- initialize pattern
-- check for the 6th occurrence starting from 1025th byte
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
dbms_lob.fileopen(fil, dbms_lob.file_readonly);
pos := dbms_lob.instr(fil, pattern, 1025, 6);
dbms_lob.fileclose(fil);
END;See Also:
"SUBSTR Function"