Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
Multiple Solutions
Based On Built-In Packages |
For a very long time we
had only one way to write text to a file from inside the database
with PL/SQL and that was through the use of the UTL_FILE package.
UTL_FILE limped along with only a single text mode for read, write,
and append until Database 10.1 when Oracle introduced the read,
write, and append byte modes. But still we were stuck with a
VARCHAR2 buffer only able to handle 32K at a time.
We now have a ways to write CLOB data directly to a file
and this "How Can I" will show you two of them using DBMS_ADVISOR and
DBMS_XSLPROCESSOR. First lets create a test table with a decent
sized CLOB. Dropping the WHERE clause on my 11.1.0.7 database yields
a CLOB of 77.5MB. The code used for this demo is based on 100,000 rows
and yields a CLOB of
about 5.5MB. |
conn / as sysdba
CREATE TABLE uwclass.t (
textcol CLOB);
DECLARE
c CLOB;
CURSOR scur IS
SELECT text
FROM dba_source
WHERE rownum < 100001;
BEGIN
EXECUTE IMMEDIATE 'truncate table uwclass.t';
FOR srec IN scur LOOP
c := c || srec.text;
END LOOP;
INSERT INTO uwclass.t VALUES (c);
COMMIT;
END;
/ |
Lets start with the traditional
solution which is to use UTL_FILE. It isn't very pretty but it is
functional.
We will start by creating a
directory object, which is required for any real-world solution, and then test it out using the table created above. |
CREATE OR REPLACE DIRECTORY ctemp AS
'c:\temp';
32767
DECLARE
fhandle utl_file.file_type;
buf CLOB;
clobPart VARCHAR2(4096);
offset NUMBER := 1;
clobLen PLS_INTEGER;
FUNCTION next_row(clob_in IN CLOB, off_in IN INTEGER)
RETURN VARCHAR2 IS
BEGIN
RETURN dbms_lob.substr(clob_in, 1024, off_in);
END next_row;
BEGIN
fhandle := utl_file.fopen('CTEMP', 'testfile0.txt', 'W');
SELECT textcol
INTO buf
FROM uwclass.t
WHERE rownum = 1;
cloblen := LENGTH(buf);
LOOP
EXIT WHEN offset >= clobLen;
clobPart := next_row(buf, offset);
utl_file.put_line(fhandle,
clobPart, FALSE);
offset := offset + 1024;
END LOOP;
utl_file.fflush(fhandle);
utl_file.fclose(fhandle);
END;
/
DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at line 31
DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 889
ORA-06512: at line 26 |
The first solution I have found uses
the built-in package DBMS_ADVISOR. The package contains a fully
documented procedure, CREATE_FILE, that is used to write
advisor results to the file system. The syntax is very simple:
dbms_advisor.create_file(
buffer IN CLOB,
location IN VARCHAR2, -- Oracle Directory Object Name
filename IN VARCHAR2); -- Name of file to write in directory |
DECLARE
buf CLOB;
BEGIN
SELECT textcol
INTO buf
FROM uwclass.t
WHERE rownum = 1;
dbms_advisor.create_file(buf, 'CTEMP', 'testfile1.txt');
END;
/ |
When I tried this with 11.1.0.7 using a 21.5MB CLOB (168,000 rows from dba_source) I generated the
following:
ERROR at line 1:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [],
[], [], [], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 49
ORA-06512: at "SYS.PRVT_ADVISOR", line 1105
ORA-06512: at "SYS.DBMS_ADVISOR", line 543
ORA-06512: at line 9
But running with 157,000 rows the 20.4MB CLOB was successful though,
on Windows, there have been some locking problems where the file
remained locked, yes I tried
COMMIT,
until Oracle was shut down. This does not happen all of the time and
does not seem to happen with small to medium sized CLOBs.
Thus this is not a perfect solution but should work for the vast
majority of real-world CLOBs.
The second solution I found uses the built-in package DBMS_XSLPROCESSOR. The package contains a fully documented
procedure, CLOB2FILE, and again the syntax is very simple:
dbms_xslprocessor.clob2file(
cl IN CLOB,
flocation IN VARCHAR2, -- file directory
fname IN VARCHAR2, -- file name
csid IN NUMBER := 0); -- character set id of the file
Use the same table and anonymous block, at the top of the page, to
load rows exactly as done for the first demo. |
DECLARE
buf CLOB;
BEGIN
SELECT textcol
INTO buf
FROM uwclass.t
WHERE rownum = 1;
dbms_xslprocessor.clob2file(buf, 'CTEMP',
'testfile2.txt');
END;
/ |
The output files created
by the two methods do not have the same byte size nor do they take
the same amount of time. The first generates a file that is larger
than the second, looks like a standard Windows ASCII file when
opened in notepad.
Using 100,000 rows from dba_source the following are averaged
results following 5 runs. Each time after deleting the file
previously created.
|
Package Name |
Procedure |
File Size (bytes) |
Run Time |
|
UTL_FILE |
PUT_LINE |
5,641,448 |
00:00:05.06 |
|
DBMS_ADVISOR |
CREATE_FILE |
5,630,696 |
00:00:03.43 |
|
DBMS_XSLPROCESSOR |
CLOB2FILE |
5,505,673 |
00:00:05.71 |
|
|
