How To Generate Text File in Oracle - Javainhand Tutorial

In this Session, We will explore the Generate a Text File using UTL_FILE in Oracle PL/SQL.

The Following Steps to be needed for Generating Text file in Oracle.

1. Create Directory 
2. Create Function (CLOB TO BLOB in Oracle)
3. Create Procedure for Generate TEXT file to the System Directory

How to Generate a Text File using UTL_FILE in Oracle PL/SQL

Step 1:- Create a Folder to your local system. In my case, I have created an image folder to E directory. The following screenshot will help.

Step 2:-Open SQL Developer or CMD connect to the database with SYS User and RUN the following commands.

create directory upload_image_dir as 'E:\upload_images';
grant read or write on directory upload_image_dir  to anonymous;
grant read or write on directory upload_image_dir  to admin;

Step3:- Create a function which convert clob data to blob file in Oracle. Use the following function.

create or replace FUNCTION clob_to_blob (p_data  IN  CLOB)
  RETURN BLOB
AS
  l_blob         BLOB;
  l_dest_offset  PLS_INTEGER := 1;
  l_src_offset   PLS_INTEGER := 1;
  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning      PLS_INTEGER := DBMS_LOB.warn_inconvertible_char;
BEGIN

  DBMS_LOB.createtemporary(
    lob_loc => l_blob,
    cache   => TRUE);

  DBMS_LOB.converttoblob(
   dest_lob      => l_blob,
   src_clob      => p_data,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset, 
   blob_csid     => DBMS_LOB.default_csid,
   lang_context  => l_lang_context,
   warning       => l_warning);

   RETURN l_blob;
END;

Step 4:- Create a SaveFile Procedure. Use the Following Procedure

create or replace PROCEDURE SaveFile(
     FileContent IN OUT NOCOPY BLOB
   , FolderName IN VARCHAR2
   , FileName IN VARCHAR2)
IS
    BUFFER RAW(1024);
    OFFSET PLS_INTEGER := 1;
    FileLength PLS_INTEGER;
    amount PLS_INTEGER := 1024;
    fhandle UTL_FILE.FILE_TYPE;
BEGIN
    FileLength := DBMS_LOB.GETLENGTH(FileContent);
    fhandle := UTL_FILE.FOPEN(FolderName, FileName,'wb', 32767);         
    LOOP
        EXIT WHEN OFFSET > FileLength;
        DBMS_LOB.READ(FileContent, amount, OFFSET, BUFFER);
        UTL_FILE.PUT_RAW(fhandle, BUFFER, TRUE);
        OFFSET := OFFSET + amount;
    END LOOP;
    UTL_FILE.FCLOSE (fhandle);
EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(fhandle) THEN
            UTL_FILE.FCLOSE(fhandle);
        END IF;
    RAISE;
END SaveFile;

Step 5:- Create a GENERATE_FILE procedure. Use following procedure.

create or replace PROCEDURE GENERATE_FILE
(P_TEXT IN VARCHAR2,P_DIRECTORY_NAME IN VARCHAR2, P_FILE_NAME IN VARCHAR2)
AS
V_FILE BLOB;
V_FILENAME VARCHAR2(500):=P_FILE_NAME||'.txt';
V_CLOB CLOB:=P_TEXT;
BEGIN
		BEGIN
		SELECT CLOB_TO_BLOB(V_CLOB) INTO V_FILE FROM dual;
		EXCEPTION WHEN OTHERS THEN
		V_FILE:=NULL;
		END;
        
	    IF V_FILE IS NOT NULL AND V_FILENAME IS NOT NULL THEN
		SAVEFILE(V_FILE,P_DIRECTORY_NAME,V_FILENAME);
		ELSE
		dbms_output.put_line('Text File Not Generate');
		END IF;
END;

Step 6:- Now call GENERATE_FILE procedure with the parameters. Use the following code.

BEGIN
GENERATE_FILE('This is text file','upload_image_dir','test');
END;

That's all for today, I think(Generate Text File in Oracle) post will be helpful for you. If you like the post then share your view in the comment box.

Please support our youtube channel so we will upload more Oracle APEX Tutorial videos and blogs.