Transfer local files to network share

In this implementation we use Oracle to manage files on the server file system and transfer them to a designated network shared location.

We do so by providing a controller specification inside a table, and use utl_file to place the specification inide of a batch (bat) file on the local system.

Windows Task Scheduler is used to trigger the batch file periodically and perform file management which here is to transfer all txt files to a preconfigured network share folder.

Create local folder C:\batch_job
Configre accessible network share as \\\\\<hostname>\\\<folder>

-- Grants
-- as SYSDBA:
-- review security reasons to later REVOKE CREATE ANY DIRECTORY FROM WH;
GRANT CREATE ANY DIRECTORY TO wh;
GRANT EXECUTE ON sys.utl_file TO wh;
CREATE OR REPLACE DIRECTORY batch_folder AS 'c:\batch_job';
GRANT READ ON DIRECTORY batch_folder TO wh;

Create table containing parameters, as WH:

CREATE TABLE DOCUMENT_MANAGEMENT (
 "ID"          VARCHAR2(20 BYTE) NOT NULL ENABLE,
 "DOCUMENT"    VARCHAR2(200 BYTE),
 "FOLDER_NAME" VARCHAR2(200 BYTE),
 "CONFIG"      VARCHAR2(2000 BYTE),
 CONSTRAINT "PK_ID_DOCUMENT_MANAGEMENT" PRIMARY KEY ( "ID" )
);

Batch parameters: we insert into batch file a check if network share exists, if not we map it all documents (*.txt) will be moved to folder ‘project01’

Insert into DOCUMENT_MANAGEMENT (ID,DOCUMENT,FOLDER_NAME,CONFIG)
 values ('1','*.txt',
 'project01',
 '@echo off
 if exist \\192.168.1.1\drive1 (set shareExists=1) else (set shareExists=0)
 if exist y:\ (set driveExists=1) else (set driveExists=0)
 if %shareExists%==1 if not %driveExists%==1 (net use y: \\192.168.1.1\drive1)
 if %shareExists%==0 if %driveExists%==1 (net use /delete y:)
 set driveExists=
 set shareExists=
 ');

Create a procedure that pulls data from the config table DOCUMENT_MANAGEMENT and puts commands into the batch file ‘process.bat’.

CREATE OR REPLACE PROCEDURE p_create_batch IS

out_file utl_file.file_type;
 v_file VARCHAR2(200);
 v_folder VARCHAR2(200);
 v_buff VARCHAR2(2000);
 v_pre VARCHAR2(1000);
BEGIN
 out_file := utl_file.fopen('BATCH_FOLDER', 'process.bat', 'W');
 SELECT
 document,
 folder_name,
 config
 INTO
 v_file, v_folder, v_pre
 FROM document_management;

v_buff := v_pre ||'move' ||' ' ||v_file ||' ' ||v_folder;

utl_file.put_line(out_file, v_buff);
 utl_file.fclose(out_file);
END;

Run procedure for the first time:

BEGIN
 p_create_batch ();
END;

Create daily job to get fresh data from DOCUMENT_MANAGEMENT table and fill batch file:

BEGIN
dbms_scheduler.create_job (
 job_name =&amp;gt; 'CREATE_BATCH',
 job_type =&amp;gt; 'PLSQL_BLOCK',
 job_action =&amp;gt; 'BEGIN p_create_batch() END;',
 start_date =&amp;gt; SYSDATE,
 repeat_interval =&amp;gt; 'FREQ=DAILY',
 enabled =&amp;gt; TRUE,
 comments =&amp;gt; 'making a batch file');
END;

Finally, create Windows tash scheduller job to daily trigger the ‘process.bat’.

References:
René Nyffenegger’s Utl file
Oracle write to file
How to write to a text file from Pl/SQL, PLS error 00363
Granting “Create Directory” Privileges in Oracle
Examples of Using the Scheduler
http://www.orafaq.com/wiki/DBMS_SCHEDULER
https://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm#ADMIN12062

1 Reply to “Transfer local files to network share”

Leave a Reply

Your email address will not be published. Required fields are marked *