OraLobEditor - Edit Oracle LOB (BLOB, CLOB) data »
Download OraLobEditor Free Trial »
Form http://www.dba-oracle.com/t_dbms_lob.htmOracle 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