PL/SQL: Sample code for UTL_FILE

In this post I will a sample code to write to a file from PL/SQL.

• Create a folder/directory (ex:- C:\test )manually in the file system.
• Create a directory object in the database by connecting as SYSDBA.

 CONN / as SYSDBA;  
 create or replace directory TEST as 'C:\test';  
 

• Grant execute privilege on UTL_FILE to the user/schema (ex:- HR) you are going to use, using SYSDBA.

 connect / as SYSDBA;  
 grant execute on utl_file to HR;  

• Now you can use the below code to write a file into that directory.

 declare
      v_file   utl_file.file_type;  
      v_line   varchar2(1000);  
      v_file_name constant varchar2(15) := 'aj_test.txt';  
 begin  
      v_file := utl_file.fopen('TEST', v_file_name, 'w', 5000);
  
      v_line := 'This is the first line!!!!';  
      utl_file.put_line(v_file, v_line);  

      v_line := 'This is the last line!!!!';  
      utl_file.put_line(v_file, v_line);  

      utl_file.fclose(v_file);  
 exception  
      when others then  
           raise;  
 end;  

Refer:http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#ARPLS069

Feel free to point out if anything is missing/wrong in this blog.

PL/SQL: Sample code to insert BLOB into Oracle database table.

This post explains how to insert a blob object into database table programmatically.

• Create a folder/directory (ex:- C:\test )manually in the file system.
• Create a directory object in the database by connecting as SYSDBA and grant the access to custom schema(ex:- HR)

 CONN / AS SYSDBA;  
 CREATE OR REPLACE DIRECTORY images AS 'C:\test';  
 GRANT READ, WRITE ON DIRECTORY images TO HR;

• Create the table as below in the custom schema (ex:-HR)

 CREATE TABLE IMAGE_STORE  
  (  
   IMAGE_ID NUMBER ,  
   FILE_NAME VARCHAR2(200 BYTE) ,  
   IMAGE BLOB,  
   MIME_TYPE VARCHAR2(100 BYTE),  
   CONSTRAINT IMAGE_STORE_PK PRIMARY KEY (IMAGE_ID)  
  );

• Copy a test file (Test_File.pdf) into the directory ('C:\test') mentioned in the above script.
• Now connect to the custom schema (HR) and execute the below code

 DECLARE  
  src_lob BFILE := BFILENAME('IMAGES', 'Test_File.pdf');  
  dest_lob BLOB;  
 BEGIN  
  INSERT INTO image_store VALUES (1,'Test_File.pdf', EMPTY_BLOB(), 'application/pdf')  
  RETURNING image INTO dest_lob;  
  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);  
  DBMS_LOB.LoadFromFile(   
              DEST_LOB => dest_lob,  
              SRC_LOB => src_lob,  
              AMOUNT  => DBMS_LOB.GETLENGTH(src_lob) );  
  DBMS_LOB.CLOSE(src_lob);  
  COMMIT;  
 END;  
 /  

Feel free to point out if anything is missing/wrong in this blog.