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.
解决方案 »
- 求高手给看看是怎么回事
- 全两个表合并的sql
- oracle 别名作为where后面的过滤条件
- 关于登陆Http://x:5560//plplus或 Http://xx:1158//em问题
- 请问如何将SQL Server数据库转成Oracle数据库??
- 数据库备份失败怎么解决?
- 在pro*c中,无法在表中插入中文,但在sqlplus工具中能插入中文???
- ●●●●●一句SQL的写法.这是无法完成的任务吗,看看有没有高手来实现?●●
- 如何实现禁用Groupby 实现分组问题
- 我想在我本机访问另一台远程机的ORACLE,另一台机子不在内网,只能通过互联网访问,怎么做?开放端口?
- 在线等待,建表时字段类型为 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"