COPYING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
UTL_FILE
0
REQUEST
I want to make a file copy through PL/SQL block
RELATED
WRITING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
READING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
REMOVING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
READING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
REMOVING AN OPERATING SYSTEM FILE THROUGH A PL/SQL BLOCK
TESTCASE
In this test case I’m going to copy a file from dir1 to dir2 by UTL_FILE package. First, Let’s create two directories and a file for copying
oracle@dbpilot.net: mkdir /tmp/dir1
oracle@dbpilot.net: mkdir /tmp/dir2
oracle@dbpilot.net: echo "A test of UTL_FILE.FCOPY procedure" > /tmp/dir1/myfile.txt
oracle@dbpilot.net: ls -lh /tmp/dir1/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 35 myfile.txt
oracle@dbpilot.net: ls -lh /tmp/dir2/ --time-style='+'
total 0
SQL> create or replace directory DIR1 as '/tmp/dir1';
Directory created.
SQL> create or replace directory DIR2 as '/tmp/dir2';
Directory created.
So, there is a need to copy myfile.txt file from /tmp/dir1 to /tmp/dir2. The simplest code is
SET SERVEROUTPUT ON
BEGIN
UTL_FILE.FCOPY('DIR1', 'myfile.txt', 'DIR2', 'myfile.cloned.txt');
END;
/
oracle@dbpilot.net: ls -lh /tmp/dir2/ --time-style='+'
total 4.0K
-rw-r--r-- 1 oracle oinstall 35 myfile.cloned.txt
oracle@dbpilot.net: cat /tmp/dir2/myfile.cloned.txt
A test of UTL_FILE.FCOPY procedure
If a file does not exist or you don’t have sufficient privileges you will get a following error
SET SERVEROUTPUT ON
BEGIN
UTL_FILE.FCOPY('DIR1', 'nonexistingfile.txt', 'DIR2', 'myfile.cloned.txt');
END;
/
BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 270
ORA-06512: at "SYS.UTL_FILE", line 1243
ORA-06512: at line 2
A little bit more complicated code
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
v_file_name VARCHAR2(100);
v_file_new_name VARCHAR2(100);
v_file_dir1 VARCHAR2(100);
v_file_dir2 VARCHAR2(100);
v_existence BOOLEAN;
v_file_length NUMBER;
v_block_size NUMBER;
BEGIN
v_file_name := 'myfile.txt';
v_file_new_name := 'myfile.cloned.txt';
v_file_dir1 := 'DIR1';
v_file_dir2 := 'DIR2';
-- Getting file size
UTL_FILE.FGETATTR(v_file_dir1 , v_file_name , v_existence , v_file_length, v_block_size);
-- Verifying that file exists
IF v_existence THEN
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE(v_file_name || ' => ' || v_file_new_name );
DBMS_OUTPUT.PUT_LINE('Length of the file in bytes : ' || v_file_length);
DBMS_OUTPUT.PUT_LINE('File system block size in bytes : ' || v_block_size);
-- Copying file
UTL_FILE.FCOPY(v_file_dir1, v_file_name, v_file_dir2, v_file_new_name);
ELSE
DBMS_OUTPUT.PUT_LINE('(*) Read_file_error : File not found.');
END IF;
END;
/
myfile.txt => myfile.cloned.txt
Length of the file in bytes : 35
File system block size in bytes : 4096
For more details refer to the official documentation.
Version : 14:11 18.01.2018
Platform : Red Hat 5.11
Database : Oracle database 12.1.0.1.0
Platform : Red Hat 5.11
Database : Oracle database 12.1.0.1.0
Tags In
Related Posts
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (62)
- Account (2)
- AWR (2)
- Database Errors (6)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (23)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Privileges (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum