Scribd
Upload
16 views3 pages

Oracle

This document provides guidance on writing CLOB data larger than 32K to a file in Oracle databases, detailing the limitations and necessary procedures. It includes two sample procedures: one for databases prior to 10g that requires new line commands for every 32K of data, and another for 10g and later that allows writing in binary mode without new line characters. The document also includes example code and expected results for both procedures.

Uploaded by

rodrigo.devlife
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views3 pages

Oracle

This document provides guidance on writing CLOB data larger than 32K to a file in Oracle databases, detailing the limitations and necessary procedures. It includes two sample procedures: one for databases prior to 10g that requires new line commands for every 32K of data, and another for 10g and later that allows writing in binary mode without new line characters. The document also includes example code and expected results for both procedures.

Uploaded by

rodrigo.devlife
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Document 358641.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-st...

Copyright (c) 2024, Oracle. All rights reserved. Oracle Confidential.

How to Write CLOB Data > 32K Out to a File? (Doc ID 358641.1)

In this Document

Goal
Solution

APPLIES TO:

PL/SQL - Version 9.2.0.7 and later


Information in this document applies to any platform.

GOAL

How to write a CLOB with a length > 40K out to a file?

SOLUTION

There is a database limit which only allows at the most 32760 characters to be written at one time. Any data greater
than that size will have to be written in chunks.

Another database limit (with databases version less than 10g) requires a "new line" to be issued for each 32K of data
written to a file.

The following illustrates two examples that write out 84K of data.

The first example can be used within a 9i or 10g database and illustrates using the "new line" for each 32K of data.

The samples require that you have created the directory alias named 'ORAFILES_OUTPUT' and reference an existing
directory with write privileges.

Sample 1 - Main Procedure

create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN


CLOB) IS
l_output UTL_FILE.file_type;
l_amt NUMBER DEFAULT 32000;
l_offset NUMBER DEFAULT 1;
l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
x varchar2(32760);
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_file, 'w', 32760);
WHILE (l_offset < l_length) LOOP
dbms_lob.read (p_clob, l_amt, l_offset, x);
UTL_FILE.put (l_output, x);
UTL_FILE.fflush (l_output);
UTL_FILE.new_line (l_output);
l_offset := l_offset + l_amt;
END LOOP;
UTL_FILE.fclose (l_output);
END clob_to_file;
/

Anonymous block to create the data and call the above procedure

DECLARE
v_Clob varchar2(4000);
t_Clob CLOB;

1 de 3 23/01/2024, 9:57 a. m.
Document 358641.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-st...

BEGIN
dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
FOR i IN 0 .. 20 LOOP
select RPAD (i, 4000, i) into v_clob from dual;
dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
END LOOP;
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
clob_to_file ('ORAFILES_OUTPUT', 'clob_test9i.txt', t_clob);
END;
/

Results of Sample1

After executing the above code, navigate to the directory location referenced by the directory alias and check for a file
named clob_test9i.txt.

> ls -l clob_test9i.txt
-rw-r--r-- 84003 clob_test9i.txt

The total data is 84000, the output file is 84003. This shows there were 3 writes to the file and the 3 extra characters
are due to the NEW_LINE command.

Sample 2 - Main Procedure

Open a file in binary mode was new to the 10g database version and allows the data to be written in RAW form and
eliminates the need for the NEW_LINE as shown in the previous example.

create or replace PROCEDURE clob_to_file (p_dir IN VARCHAR2, p_file IN VARCHAR2, p_clob IN


CLOB) IS
l_output UTL_FILE.file_type;
l_amt NUMBER DEFAULT 32000;
l_offset NUMBER DEFAULT 1;
l_length NUMBER DEFAULT NVL (DBMS_LOB.getlength (p_clob), 0);
x varchar2(32760);
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_file, 'wb', 32760);
WHILE (l_offset < l_length) LOOP
dbms_lob.read (p_clob, l_amt, l_offset, x);
UTL_FILE.PUT_raw(l_output, utl_raw.cast_to_raw(x), TRUE);
UTL_FILE.fflush (l_output);
l_offset := l_offset + l_amt;
END LOOP;
UTL_FILE.fclose (l_output);
END clob_to_file;
/

Anonymous block to create the data and call the above procedure

DECLARE
v_Clob varchar2(4000);
t_Clob CLOB;
BEGIN
dbms_lob.createtemporary(t_clob,true,dbms_lob.session);
FOR i IN 0 .. 20 LOOP
select RPAD (i, 4000, i) into v_clob from dual;
dbms_lob.write(t_clob, 4000, ((4000 * i) + 1), v_clob);
END LOOP;
DBMS_OUTPUT.PUT_LINE(DBMS_LOB.GETLENGTH(T_CLOB));
clob_to_file ('ORAFILES_OUTPUT', 'clob_test10.txt', t_clob);
END;
/

2 de 3 23/01/2024, 9:57 a. m.
Document 358641.1 https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrl-st...

Results of Sample2

After executing the above code, navigate to the directory location referenced by the directory alias and check for a file
named clob_test10.txt.

> ls -l clob_test10.txt
-rw-r--r-- 84000 clob_test10.txt

Since there are no "new line" characters added to the file, the data written is exactly the same as the data originally
created. Essentially, no extra characters.

Didn't find what you are looking for?

3 de 3 23/01/2024, 9:57 a. m.

You might also like