Home

# 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

Setting 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

Remove requires write permission on the file.

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

# Rename

Rename requires write permission on the file.

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