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.
- Create Directory
- Create Function (CLOB TO BLOB in Oracle)
- 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 Procedurecreate 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.
0 Comments
If you have any doubts, Please let me know