About
Oracle dbms_lob
Oracle provides the
DBMS_LOB package which is used to access and
manipulate LOB values in both internal or
external storage locations.
dbms_lob Routines That Can Modify
BLOB, CLOB, And NCLOB Values
* APPEND() - append the
contents of the source LOB to the
destination LOB
* COPY() - copy all or
part of the source LOB to the destination
LOB
* ERASE() - erase all or
part of a LOB
* LOADFROMFILE() - load
BFILE data into an internal LOB (8.0.3 and
higher)
* TRIM() - trim the LOB
value to the specified shorter length
* WRITE()- write data to
the LOB from a specified offset
Routines That Read Or
Examine LOB Values in dbms_lob
*
GETLENGTH() - get the length of the
LOB value
* INSTR() - return the
matching position of the nth occurrence of
the pattern in the LOB
* READ() - read data from
the LOB starting at the specified offset
* SUBSTR() - return part
of the LOB value starting at the specified
offset
dbms_lob Read-Only Routines Specific
To Bfiles
*
FILECLOSE() - close the file
* FILECLOSEALL()- close
all previously opened files
* FILEEXISTS() - check if
the file exists on the server
* FILEGETNAME() - get the
directory alias and file name
* FILEISOPEN() - check if
the file was opened using the input BFILE
locators
* FILEOPEN() - open a file
dbms_lob Datatypes
Parameters for the DBMS_LOB
routines use the datatypes:
*
BLOB, for a source or destination
binary LOB
* RAW, for a source or
destination raw buffer (used with BLOB)
* CLOB, for a source or
destination character LOB (including NCLOB)
* VARCHAR2, for a source
or destination character buffer (used with
CLOB and NCLOB)
* INTEGER, to specify the
size of a buffer or LOB, the offset into a
LOB, or the amount to access
dbms_lob
examples
The following SQL statements show the rule
conditions for the above mentioned rules:
SELECT
dbms_lob.substr(rule_condition,dbms_lob.getlength(rule_condition),1)
rule_condition FROM dba_rules where
rule_name = 'NY270'
/
RULE_CONDITION
--------------------------------------------------|
((:dml.get_object_owner () = 'NY2') and
:dml.is_nul
l_tag() = 'Y' and
:dml.get_source_database_name ()
= 'DNYTST10.WORLD' )
SELECT
dbms_lob.substr(rule_condition,dbms_lob.getlength(rule_condition),1)
rule_condition
FROM dba_rules where rule_name = 'NY271'
/
GRAPHICS_DBA
BEGIN dbms_lob.read (:1, :2, :3, :4);
END;2121 1 0 10251 488
alter session set nls_language= 'AMERICAN'
nls_territory= 7 1 0 3975 408'AMERICA'
nls_currency= '$' nls_iso_currency='AMERICA'
nls_numeric_characters= '.,'
nls_calENDar='GREGORIAN'
nls_date_format= 'DD-MON-YY'
nls_date_language= 'AMERICAN'
nls_sort= 'BINARY'
BEGIN :1 := dbms_lob.getLength (:2); END; 6
1 0 9290 448
SELECT TO_CHAR(image_seq.nextval) FROM dual6
1 0 6532 484
SELECT graphic_blob FROM internal_graphics
SEE CODE DEPOT FOR FULL SCRIPT
SELECT RPAD(TO_CHAR(graphic_id),5)||':1 1 0
7101 472
'||RPAD(graphic_desc,30)||' :
'||RPAD(graphic_type,10)
FROM internal_graphics ORDER BY graphic_id
SELECT graphic_blob FROM internal_graphics
SEE CODE DEPOT FOR FULL SCRIPT
|
