During a recent migration project for one of our customers, I needed to export certain XML rows from a Novell Sentinel 6.1 Oracle 10G R2 Hotfix 4 Database. In particular, I found that it was not possible to export Filter Configuration in Novell Sentinel 6.1 and I needed the 250+ Filters in the new Database and new Sentinel Server. The restrictions in place made it impossible to edit the fields using utilities such as DBVIS or Toad for Oracle. I wanted to be able to write SQL Statements that allowed files to be read into the stream and update the backend database column (CLOB).
With some MSSQL and MySQL skills, I did some reading up on how Oracle can manipulate CLOB/LOB Columns using DBMS_LOB. The result is a simple SQL Statement that results in an SQL UPDATE statement, which updates a column, with XML located in /directory/to/filter.xml.
**** WARNING ****
This is currently unsupported by Novell, but in both DEV, TEST and PROD environments, this is working as expected and hasn’t broken any function within Sentinel 6.1 itself.
To perform the below you will need to :
- Shutdown the Sentinel Service (/opt/novell/sentinel6/bin/sentinel.sh stop)
- Backup the ESEC Oracle Database
1. Create Directory Object
To start with, you’ll need to place the XML file in a directory and create the File Handler in Oracle for use later. In my case, I copied the text (XML) from the source column in the source database and pasted it to a file. The file handler is “EXAMPLE_LOB_DIR” and the directory is “/directory/to/filter.xml”.
CREATE OR REPLACE DIRECTORY
2. Update Column In Database
Now that we have the file handler, we’ll use the chunk of SQL Syntax below, to read the XML File into a SQL UPDATE statement, which updates DATA(column) in the CONFIG(table) with data in the dest_clob(xml file).
src_clob BFILE := BFILENAME(‘EXAMPLE_LOB_DIR’, ‘filter.xml’);
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
— THE FOLLOWING BLOCK OF CODE WILL ATTEMPT TO INSERT / WRITE THE CONTENTS
— OF AN XML FILE TO A CLOB COLUMN. IN THIS CASE, I WILL USE THE NEW
— DBMS_LOB.LoadCLOBFromFile() API WHICH *DOES* SUPPORT MULTI-BYTE
— CHARACTER SET DATA. IF YOU ARE NOT USING ORACLE 9iR2 AND/OR DO NOT NEED
— TO SUPPORT LOADING TO A MULTI-BYTE CHARACTER SET DATABASE, USE THE
— FOLLOWING FOR LOADING FROM A FILE:
— DEST_LOB => dest_clob
— , SRC_LOB => src_clob
— , AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
WHERE UNIT = ‘filter’
— OPENING THE SOURCE BFILE IS MANDATORY
DEST_LOB => dest_clob
, SRC_BFILE => src_clob
, AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
, BFILE_CSID => DBMS_LOB.DEFAULT_CSID
, LANG_CONTEXT => lang_ctx
, WARNING => warning
DBMS_OUTPUT.PUT_LINE(‘Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).’);