Oracle: How to read UNIX ASCII file contents with a SQL Query

Following example illustrate the way to read the UNIX system ASCII files with a Single SQL Query.
With the function, now you do not have hazels to login to your UNIX box to view the contents of your ASCII log files.

Create the following function and compile it. Then use the following SQL query to read your log file.

SQL> : SELECT read_file(‘/home/export/auditlog.txt’,'DIR_ALIAS’) AS data FROM DUAL;

NOTE: Return value would be a CLOB datatype. use TO_CHAR to cnvert CLOB data to a VARCHAR2 type.

CREATE FUNCTION read_file (pFileName VARCHAR2 , pDirAlias VARCHAR2) RETURN CLOB
IS
oBFile BFILE;
oCLob CLOB;
v_file_exists NUMBER;
bValue BOOLEAN := FALSE;
v_error VARCHAR2(4000);

BEGIN
-- Purpose: Reads the UNIX Log files
-- SRINIVAS Sreeramoju 02/21/2010 Initial Creation
-- --------- ------ ------------------------------------------
DBMS_LOB.CREATETEMPORARY(oCLob,true);
oBFile := BFILENAME(pDirAlias,pFileName);
v_file_exists := DBMS_LOB.fileexists(oBFile);

IF v_file_exists = 1 THEN
-- Open the file
  DBMS_LOB.fileOpen(oBFile,DBMS_LOB.file_readonly);
 DBMS_LOB.loadFromFile
  (  dest_lob => oCLob,
     src_lob => oBFile,
     amount => DBMS_LOB.getLength(oBFile)
 );

IF DBMS_LOB.ISOPEN(oBFile) = 1 THEN
  DBMS_LOB.fileclose(oBFile); --Close file
END IF ;

IF oCLob IS NULL THEN
  RETURN ' ';
ELSE
  RETURN oCLob;
END IF;

END IF;

EXCEPTION

WHEN others THEN
   v_error := SQLCODE '-' SQLERRM ;
   DBMS_OUTPUT.Put_Line( v_error );

END read_file;