# Create Directory

1
2
3
4
5
create directory EDI_TEST as '/usr/tmp/edi/test';

select *
from all_directories
where directory_name like '%EDI%';
OWNER DIRECTORY_NAME DIRECTORY_PATH
SYS EDI_TEST /usr/tmp/edi/test

Set Directory Access Permissions

1
2
3
4
5
6
7
[testmgr@erptest edi]$ ls -l
total 8
drwxr-xr-x. 2 testmgr dba 4096 Feb 18 17:57 test
[testmgr@erptest edi]$ chmod 777 test
[testmgr@erptest edi]$ ls -l
total 8
drwxrwxrwx. 2 testmgr dba 4096 Feb 18 17:57 test

# Put Content to File

1
2
3
4
5
6
7
8
9
10
11
12
13
declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('EDI_TEST', 'hello.txt', 'w');
utl_file.put_line(f1, 'put_line: hello world!');
utl_file.putf(f1, 'putf: hello world!\n');
utl_file.put(f1, 'put: hello world\n!');
utl_file.fclose(f1);
dbms_output.put_line('File Created');
exception
when others then
dbms_output.put_line(sqlerrm);
end;

hello.txt

1
2
3
put_line: hello world!
putf: hello world!
put: hello world\n!

# Get Content from File

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare
v1 varchar2(32767);
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('EDI_TEST', 'hello.txt', 'r');
loop
begin
utl_file.get_line(f1, v1);
dbms_output.put_line(v1);
exception
when no_data_found then
exit;
end;
end loop;
end;

-- put_line: hello world!
-- putf: hello world!
-- put: hello world\n!

# Put Table Content to File

1
2
3
4
5
6
7
8
9
10
11
declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('EDI_TEST', 'emp.csv', 'w');

for i in (select * from scott.emp) loop
utl_file.put_line(f1, i.empno || ',' || i.ename || ',' || i.job || ',' || i.hiredate);
end loop;

utl_file.fclose(f1);
end;

# Put Cursor Content to File

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
f1 utl_file.file_type;
cursor c1 is select * from scott.emp;
i c1%rowtype;
begin
f1 := utl_file.fopen('EDI_TEST', 'emp2.csv', 'w');

open c1;

loop
fetch c1 into i;
exit when c1%notfound;
utl_file.put_line(f1, i.empno || ',' || i.ename || ',' || i.job || ',' || i.hiredate);
--utl_file.new_line(f1, 2);
end loop;

close c1;

utl_file.fclose(f1);
end;

# Put Content use Bulk Collect

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
declare
f1 utl_file.file_type;
cursor c1 is select * from scott.emp;
type t1 is table of scott.emp%rowtype index by binary_integer;
v1 t1;
begin
f1 := utl_file.fopen('EDI_TEST', 'emp3.csv', 'w');

open c1;
fetch c1 bulk collect
into v1;

for i in v1.first .. v1.last loop
utl_file.put_line(f1, v1(i).empno || ',' || v1(i).ename || ',' || v1(i).job || ',' || v1(i).hiredate);
end loop;

close c1;

utl_file.fclose(f1);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare
f1 utl_file.file_type;
type t1 is table of scott.emp%rowtype index by binary_integer;
v1 t1;
begin
f1 := utl_file.fopen('EDI_TEST', 'emp4.csv', 'w');

select * bulk collect into v1 from scott.emp;

for i in v1.first..v1.last
loop
utl_file.put_line(f1, v1(i).empno || ',' || v1(i).ename || ',' || v1(i).job || ',' || v1(i).hiredate);
end loop;

utl_file.fclose(f1);
end;

# Copy

1
2
3
begin
utl_file.fcopy('EDI_TEST', 'emp.csv', 'EDI_TEST', 'emp_copy.csv');
end;

# Remove

1
2
3
begin
utl_file.fremove('EDI_TEST', 'emp.csv');
end;

# Rename

1
2
3
begin
utl_file.frename('EDI_TEST', 'emp.csv', 'EDI_TEST', 'emp_rename.csv', true);
end;

# File Attribute

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare
exisit boolean;
flen number;
bsize number;
begin
utl_file.fgetattr('EDI_TEST', 'emp3.csv', exisit, flen, bsize);

if exisit then
dbms_output.put_line('File Exists');
else
dbms_output.put_line('File Not Exists');
end if;

dbms_output.put_line('File Length: ' || to_char(flen));
dbms_output.put_line('Block Size: ' || to_char(bsize));
end;

# Get CSV Content from File to Table

1
2
3
4
5
6
7
8
9
10
11
declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('EDI_TEST', 'emp.csv', 'w');

for i in (select * from scott.emp) loop
utl_file.put_line(f1, i.empno || ',' || i.ename || ',' || i.job || ',' || i.hiredate);
end loop;

utl_file.fclose(f1);
end;

emp.csv

1
2
3
4
5
6
7
8
9
10
11
12
13
14
7369,SMITH,CLERK,17-DEC-80
7499,ALLEN,SALESMAN,20-FEB-81
7521,WARD,SALESMAN,22-FEB-81
7566,JONES,MANAGER,02-APR-81
7654,MARTIN,SALESMAN,28-SEP-81
7698,BLAKE,MANAGER,01-MAY-81
7782,CLARK,MANAGER,09-JUN-81
7788,SCOTT,ANALYST,09-DEC-82
7839,KING,PRESIDENT,17-NOV-81
7844,TURNER,SALESMAN,08-SEP-81
7876,ADAMS,CLERK,12-JAN-83
7900,JAMES,CLERK,03-DEC-81
7902,FORD,ANALYST,03-DEC-81
7934,MILLER,CLERK,23-JAN-82
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
declare 
f1 utl_file.file_type;
v1 varchar2(32767);
v2 varchar2(32767);
stmt varchar2(32767);
begin
f1 := utl_file.fopen('EDI_TEST', 'emp.csv', 'R');
loop
begin
utl_file.get_line(f1, v1);
dbms_output.put_line(v1);
select replace(v1, ',', ''',''') into v2 from dual;
stmt := 'insert into emp_test values (''' || v2 || ''')';
execute immediate stmt;
dbms_output.put_line(stmt);
exception
when no_data_found then
exit;
end;
end loop;
utl_file.fclose(f1);
exception
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line(dbms_utility.format_error_stack);
end;
/

select * from emp_test;
EMPNO ENAME JOB HIREDATE
7369 SMITH CLERK 1980-12-17
7499 ALLEN SALESMAN 1981-02-20
7521 WARD SALESMAN 1981-02-22
7566 JONES MANAGER 1981-04-02
7654 MARTIN SALESMAN 1981-09-28
7698 BLAKE MANAGER 1981-05-01
7782 CLARK MANAGER 1981-06-09
7788 SCOTT ANALYST 1982-12-09
7839 KING PRESIDENT 1981-11-17
7844 TURNER SALESMAN 1981-09-08
7876 ADAMS CLERK 1983-01-12
7900 JAMES CLERK 1981-12-03
7902 FORD ANALYST 1981-12-03
7934 MILLER CLERK 1982-01-23
Edited on