How To Insert A TXT File Into Oracle

Many times need to upload files into database. In this post I proposed You a way using dbms_lob package.

Create the table to store the files. I have included a column of date type to store the insert time;

CREATE TABLE EXTERNAL_TXT_FILES
(
INS_TIME DATE,
TXT_FILE CLOB
)
/

Create from sqlplus a directory, which exist at operating system, where are the txt files;

CREATE OR REPLACE DIRECTORY DIRECTORY_PROBES
AS '/EXPORT/HOME/ORACLE/ANR/FILES';

Create a procedure

CREATE OR REPLACE
PROCEDURE LOAD_A_FILE(FILENAME_2_UPLOAD IN VARCHAR2 )
AS
COLUMN_CLOB CLOB;
PHYSYCAL_FILE BFILE;
BEGIN
INSERT INTO EXTERNAL_TXT_FILES VALUES ( SYSDATE, EMPTY_CLOB() ) RETURNING TXT_FILE INTO COLUMN_CLOB;
PHYSYCAL_FILE := BFILENAME( 'DIRECTORY_PROBES', FILENAME_2_UPLOAD );
DBMS_LOB.FILEOPEN( PHYSYCAL_FILE );
DBMS_LOB.LOADFROMFILE( COLUMN_CLOB, PHYSYCAL_FILE, DBMS_LOB.GETLENGTH( PHYSYCAL_FILE ) );
DBMS_LOB.FILECLOSE( PHYSYCAL_FILE );
END;
/

Execute the next command to upload a file, in this case MyFile.txt;

EXEC LOAD_A_FILE('MYFILE.TXT');

Now you can query the table with the next select;

SELECT * FROM EXTERNAL_TXT_FILES;

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s