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 12.1.0.2 database yields
a CLOB of 30MB. The code used for this demo is based on 100,000 rows
and yields a CLOB of
about 4.6MB. |
conn sys@pdbdev as sysdba
CREATE TABLE uwclass.t (
textcol CLOB);
DECLARE
c CLOB;
CURSOR scur IS
SELECT text
FROM dba_source
WHERE rownum < 200001;
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;
/
SELECT LENGTH(textcol)
FROM uwclass.t; |
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';
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);
EXCEPTION
WHEN OTHERS THEN
utl_file.fflush(fhandle);
utl_file.fclose(fhandle);
END;
/
-- Note: with the EXCEPTION HANDLER it fails after writing most of the data. Without the handler you get a
zero byte file and the following exception.
DECLARE
*
ERROR at line 1:
ORA-29285: invalid directory path
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
In 12.1.0.2 it still generates an error if the CLOB is too large. But running with 200,000 rows, the 15.8MB CLOB was successful
and a small amount of testing indicates that it will work with larger clobs though the limit was not determined.
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 200,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 |
13,363,307 (partial) |
00:00:07.33 |
|
DBMS_ADVISOR |
CREATE_FILE |
16,103,589 |
00:00:01.04 |
|
DBMS_XSLPROCESSOR |
CLOB2FILE |
15,773,020 |
00:00:00.93 |
|
|
