Oracle - An Managing Lob examples

I met a request: I need to read files from Computer File System and insert this readed files into Oracle database as Clob, and need to complete this through PL/SQL only.

To describ this request more clearly, we list the create table sql statements firstly:

create table stylesheet (
  stylesheet_name varchar2(100) primary key not null,
  stylesheet_text CLOB
);

Obviously, this is a very simple table, which has 2 column, stylesheet_name is table primary key has not null constraints, and stylesheet_text is a clob type use to store the files readed from Computer file system.

 

First: I found use Oracle External Tables skill can meet this request, But have some defects:

In this condition, we use Oracle Databse System Package DBMS_LOB, DBMS_LOB has some methods can handle this. DBMS_LOB.loadfromfile() can insert the clob values to database. So we need to read the file system's data to clob pattern, use BFILE can accomplish this, the whole processing SQL CODE as following:

CREATE OR REPLACE DIRECTORY 
  XSLT_LOB_DIR
  AS
  'C:/Share/xslt'
/

DECLARE
  dest_clob   CLOB;
  src_bfile    BFILE  := BFILENAME('XSLT_LOB_DIR', 'MSProject2Plan.xslt');
BEGIN
  insert into stylesheet(stylesheet_name, stylesheet_text) values('MSProject2Plan', empty_clob()) returning stylesheet_text into dest_clob;
  DBMS_LOB.fileopen(src_bfile);
  DBMS_LOB.loadfromfile(dest_clob, src_bfile, DBMS_LOB.getlength(src_bfile));
  DBMS_LOB.fileclose(src_bfile);
  commit;
END;
/  

 'MSProject2Plan.xslt' is the file we need to insert into table stylesheet, which saved under 'C:/Share/xslt' folder.

Use the above can sovle the Request only if this files and Oracle Database Syatem in the same machine, if you try this in Oracle client or other client the following error you will found :

ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the path specified.

 So this solution isn't able to meet our request,  we need to find another solution:

 

Second: Use Oracle utility to load the file from Computer File System

      SQL*Loader (sqlldr ) is the utility to use for high performance data loads.  The data can be loaded from any text file and inserted into the database. The key feature what sqlldr can meet our request is : It Can Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.

1. SQL*Loader architecture

 The below figure depicts the SQL*Loader architecture.  SQL*Loader reads a data file and a description of the data which is defined in the control file.  Using this information and any additional specified parameters (either on the command line or in the PARFILE), SQL*Loader loads the data into the database. 

2. The Test Sequence(a demo use SQL*Loader load data)

 Also as the below figure depicted, My Test Example use a windows scrpits invoke test.sql, test.sql invoke the stylesheetcontrol.sql to complete the load processing.

3. Test Demo List:

run.cmd

@echo off

set SYSTEMUSER=system
set SYSTEMPASSWD=ADMIN
set DBPROTOCOL=TCP
set DBMODE=DEDICATED
set COMUSR=IPCUSERTEST
set COMPASSWD=tibco
set DBSERVER=192.168.68.116
set DBPORT=1521
set SERVICENAME=orcl
set COMDIR=c:/
set TABLESPACENAME=IPCTEST

set SYSTEMCONNECTSTRING=%SYSTEMUSER%/%SYSTEMPASSWD%@(DESCRIPTION=(ADDRESS=(PROTOCOL=%DBPROTOCOL%)(HOST=%DBSERVER%)(PORT=%DBPORT%))(CONNECT_DATA=(SERVER=%DBMODE%)(SERVICE_NAME=%SERVICENAME%)))
set IPCUSERCONNECTSTRING=%COMUSR%/%COMPASSWD%@(DESCRIPTION=(ADDRESS=(PROTOCOL=%DBPROTOCOL%)(HOST=%DBSERVER%)(PORT=%DBPORT%))(CONNECT_DATA=(SERVER=%DBMODE%)(SERVICE_NAME=%SERVICENAME%)))
set SQLLDRCONNECTSTRING=%COMUSR%/%COMPASSWD%@//%DBSERVER%:%DBPORT%/%SERVICENAME%

rem echo %SYSTEMCONNECTSTRING%
rem echo %SQLLDRCONNECTSTRING%
echo sql*loader load data start...
echo.

set RUN_SCRIPT=%ORACLE_HOME%/bin/sqlplus -l -s "%SYSTEMCONNECTSTRING%" @test.sql %SQLLDRCONNECTSTRING% %IPCUSERCONNECTSTRING%

%RUN_SCRIPT%

 test.sql

define SQLLDRCONNECTSTRING='&1'

connect &IPCUSERCONNECTSTRING 
 
@stylesheetcontrol.sql &SQLLDRCONNECTSTRING

 stylesheetcontrol.sql

define SQLLDRCONNECTSTRING='&1'

set serveroutput on

DECLARE
BEGIN
  DBMS_OUTPUT.PUT_LINE('Create stylesheet table, Insert the MSProject2Plan.xslt into the stylesheet');
END;
/

create table stylesheet (
  stylesheet_name varchar2(100) primary key not null,
  stylesheet_text CLOB
);

host sqlldr &SQLLDRCONNECTSTRING control=loadXSLT.ctl log=logs/loadXSLT.log

 loadXSLT.ctl

LOAD DATA
INFILE xsltlist.dat
INTO TABLE stylesheet
REPLACE
FIELDS TERMINATED BY ','
( stylesheet_name CHAR(100),
  clob_filename   FILLER CHAR(100),
  stylesheet_text LOBFILE(clob_filename) TERMINATED BY EOF
)

 xsltlist.dat

MSProject2Plan,../xslt/MSProject2Plan.xslt

 Run the run.cmd the file under xslt folder(MSProject2Plan.xslt) will be loaded and insert into the table stylesheet.