Tag Archives: Sentinel 6.1

Updating CLOB Columns | Oracle Database

9 Aug

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
EXAMPLE_LOB_DIR
AS
‘/directory/to/filter.xml’

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).

DECLARE
dest_clob CLOB;
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;
warning number;
BEGIN
DBMS_OUTPUT.ENABLE(100000);
— ———————————————————————–
— 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:

— DBMS_LOB.LoadFromFile(
— DEST_LOB => dest_clob
— , SRC_LOB => src_clob
— , AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
— );

— ———————————————————————–
SELECT DATA
   INTO dest_clob
   FROM CONFIGS
   WHERE UNIT = ‘filter’
   FOR UPDATE;
— ————————————-
— OPENING THE SOURCE BFILE IS MANDATORY
— ————————————-
DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadCLOBFromFile(
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_LOB.CLOSE(src_clob);
COMMIT;
DBMS_OUTPUT.PUT_LINE(‘Loaded XML File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).’);
END;

Advertisements