PLSQL utl_file Package

Utl_File

Utl_File : This is the Oracle supplied package which is used for extracting data from Oracle queries into csv file or in other format files.

 

Note:- Before using utl_file package, we need to create a directory in Database. Also need to read and write permission on that directory.

SQL>CREATE DIRECTORY test_dir AS 'c:\'; (For Window OS)
SQL>CREATE DIRECTORY test_dir AS '/tmp'; (For Linux OS)

 

Note:-Directory should be created on OS as well.

 

SQL>grant read,write on directory test_dir to scott; 

OPEN a file FOR read operations:-

UTL_FILE.fopen(
file_location IN VARCHAR2, 
file_name     IN VARCHAR2,
open_mode     IN VARCHAR2,
max_linesize  IN BINARY_INTEGER DEFAULT NULL)
RETURN file_type; 


Example:- Reading line from file and print 

DECLARE
 vInHandle UTL_FILE.file_type;
 vNewLine  VARCHAR2(250);
BEGIN
  vInHandle := UTL_FILE.fopen('TEST_DIR', 'test_file.txt', 'R');
  LOOP
    BEGIN
      UTL_FILE.get_line(vInHandle, vNewLine);
      DBMS_OUTPUT.put_line(vNewLine);
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
  UTL_FILE.fclose(vInHandle);
END fopen;
/ 

Open a File in Write Mode:- 

UTL_FILE.fopen(, file_name, 'w') ;

 

Open a File in Read Mode:-

UTL_FILE.fopen(, file_name, 'r') ;

Close a File:- 

UTL_FILE.fclose(file IN OUT  RECORD );

CLOSE ALL FILES:-

PROCEDURE 

fclose_all ();

Example:- Writing a line in file  

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('TEST_DIR', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/


declare 
  fhandle  utl_file.file_type;
begin
  fhandle := utl_file.fopen('TEST_DIR', 'test_file.txt' , 'w');
  utl_file.put(fhandle, 'Hello world!'|| CHR(10));
  utl_file.put(fhandle, 'Hello again!');
  utl_file.fclose(fhandle);
exception
  when others then 
    dbms_output.put_line('ERROR: ' || SQLCODE || ' - ' || SQLERRM);
    raise;
end;
/

Example:- Creating a report from SQL Query 

declare
   fileID UTL_FILE.FILE_TYPE;
BEGIN
   fileID := UTL_FILE.FOPEN ('TEST_DIR', 'emp.csv', 'W');
   FOR emprec IN (SELECT * FROM emp)
   LOOP
      UTL_FILE.PUT (fileID,TO_CHAR (emprec.empno) || ',' ||
          emprec.ename || ',' ||TO_CHAR (emprec.deptno)|| CHR(10));
   END LOOP;
   UTL_FILE.FCLOSE (fileID);
END;
/

PLSQL Package

Package

Package :-A package is a group of PL/SQL types, objects, stored procedures and functions. 

Package specification :-The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. 

Package body:-The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package. 

Example:-

create or replace package my_api
as
       function emp_sal(eno number) return number;
       function emp_name(eno number) return varchar2;
       procedure emp_info(eno number,name out varchar2);
       procedure sal_raise (emp_id number, sal_incr number);
end;
/

create or replace package body my_api
as
       function emp_sal(eno number) return number
       is
           vsal number;
       begin
          select sal into vsal from emp where empno=eno;
          return vsal;
       end emp_sal;
       function emp_name(eno number) return varchar2
       is
            vname varchar2(20);
       begin
            select ename into vname from emp where empno=eno;
       return vname;
       end emp_name;
       procedure emp_info(eno number,name out varchar2)
       is
       begin
       select ename into name from emp where empno=eno;
       end;
       procedure sal_raise (emp_id number, sal_incr number)
       is
       begin
          update emp set sal=sal + sal_incr where empno=emp_id;
          if sql%notfound then
          raise_application_error(-20011, 'Invalid Employee Number:' || TO_CHAR(emp_id));
         end if;
      end sal_raise;
end my_api;
/