File Input & Output in Oracle

 File Input & Output ( 8.0 )  ( Data Migration Technique )
 -------------------------------
 Used to Transfer Oracle Table contents to OS File  and OS File content to Oracle Table.
 * UTL_FILE -- Built_in package supports file    operations.
 * STANDARD -- System Package holds all Built_in     Functions like Arthematic, Character, Date , Group  & General functions.

 Sub Programs in UTL_FILE Package:
 *fopen :
 used to open the file in the specified mode and  returns  the address of the file.
 ( Mode ----> R - Read, W - Write , A - Append )
 *fclose :
 used to close the file
 *get_line :
 used to read a line from file to pl/sql variable
 *put :
 used to write a line into file in SAME line
 *new_line : used to create a empty line in file
 *put_line : used to write a line into file but in NEW line
 *fflush : writes the file content from Temporary buffer to   Physical Memory ( Saving file into OS )
 *file_type :   Data type  used to define the variable to hold the address of   file.

 * Invalid_file_handle , Read_error :  Pre-defined          Exceptions
   Automatically activated while working with files.
---------------------------------------------------------------
 * Pl/sql Block Transfers data from Oracle Table to     OS file:
 >declare
   vfile utl_file.file_type;
   vtext varchar2(200);
   cursor c1 is select * from emp;
  begin
 vfile := utl_file.fopen('C:\', 'employ.txt', 'W');
 for i in c1 loop
 vtext := i.empno||'  '||i.ename||'  '||i.sal||'  '||i.job
  ||'  '||i.deptno;
 utl_file.put_line(vfile,vtext);
 end loop;
 utl_file.fflush(vfile);
 utl_file.fclose(vfile);
 end;
/

 employ.txt
 101 RAM 20000 MANAGER 10
 --------------------------------------------------------------
 * Pl/Sql Procedure Transfers data from OS file to
    Database Table.

 create or replace procedure load_student
 (pdir varchar2, pfile varchar2) is
 vfile utl_file.file_type;
 vtext varchar2(200);
 vname varchar2(20);
 vcourse varchar2(20); 
 vfee number(5);         
 fcomma number(3);     
 scomma number(3);
 begin
 vfile := utl_file.fopen(pdir,pfile,'R');
 Loop
 BEGIN
 utl_file.get_line(vfile,vtext);
 EXCEPTION
 when no_data_found then
 exit;
 END;
 fcomma := instr(vtext, ',' , 1 , 1); -- 5
 scomma := instr(vtext, ',' ,1, 2); -- 14
 vname := substr(vtext, 1, fcomma - 1);
 vcourse := substr(vtext,fcomma + 1,scomma -   fcomma    -1);
 vfee := substr(vtext, scomma + 1);
 insert into student   values(s1.nextval,vname,vcourse,vfee);
 end loop;
 commit;
 exception
 when utl_file.read_error then
 dopl('Unable to read the file......');
 end;

 >exec load_student('C:\','student.txt');

 student.txt - File        student - Table
 --------------------        ------------------
 HARI,Oracle9i,1000                roll
 RAM,Oracle9i,2500        name
 SIVA,D6i,2000        course
            fee
 * Note:
 Provide the below parameter in "init.ora" System  File
 open the file in Notepad and add below line at the  end & save .
 * UTL_FILE_DIR = 'C:\oracle\ora92\BIN'

  init.ora --- System parameter file
  Holds the parameters defined for Oracle server.
---------------------------------------------------------------
 * Rollup & Cube Built_ins: (8.0)
    Used to retrieve data analysis reports

 Ex:1  create table " Pet_info "
 create table pet_info
 ( pet varchar2(20),
   city varchar2(20),
   pcount number(4));

insert into pet_info values('dog','hyd',350);
insert into pet_info values('cat','hyd',300);
insert into pet_info values('dog','sbad',250);
insert into pet_info values('cat','sbad',200);

Write queries to retrieve
1. no.of dogs in hyderabad
2. no.of cats in hyderabad
3. no.of dogs in secbad
4. no.of cats in secbad   
5. no.of pets in hyderabad     ( pets = dogs + cats )
6. no.of pets in secbad
7. no.of dogs in hyd & secbad
8. no.of cats in hyd & secbad
9. no.of pets in hyd & secbad

select pet, city, sum(pcount) total
from pet_info group by CUBE(pet,city);

       pet        city      total
       dog     hyd         350
       cat      hyd         300
       dog     sbad       250
       cat      sbad       200
       null     hyd         650    ( null = dog + cat )
       null     sbad       450
       dog     null        600     ( null = hyd + sbad )
       cat      null        500
       null     null       1100

select pet, city, sum(pcount) total
from pet_info group by ROLLUP(pet,city);

       pet      city    total
       dog    hyd        350
       cat     hyd         300
       dog   sbad        250
       cat    sbad        200
       dog   null         600     ( null = hyd + sbad )
       cat    null         500
       null   null         1100    ( null = dog + cat )

Ex: 2
select  to_char(hiredate,'yyyy') year, deptno, job, sum(Sal) total from emp group by cube(to_char(hiredate,'yyyy'),deptno,job);

2001  10   CLERK    ----
2001  10    null       ----
2001  null   CLERK   ----   *null -- All years / All                                            depts / All Jobs
2001  null    null      -----             
null    10      null      -----
null    null     CLERK  ----
null    null     null     -----
---------------------------------------------------------------
LOBS - Large Objects (8.0)-------------------------------
used to represent huge loads of data.
Max limit is 4 GB.
N columns allowed per Table.
4 Types:
1. CLOB - Character Large Object
    used to represent character information upto 4     Gb.
2. NCLOB - Native Character Large Object
   used to represent other language text.
   Nchar, Nvarchar2 - Represents other language Text
3. BLOB - Binary Large Object
    Used to represent images upto 4 GB.
4. BFILE - Binary file
    used to hold the address of OS file

To represent NULL values:
CLOB -- EMPTY_CLOB()
NCLOB -- EMPTY_NCLOB()
BLOB -- EMPTY_BLOB()
BFILE -- Skip the column while inserting

Ex:
 create table emp_lob(ecode number(4), ename  varchar2(20), description clob, sal number(12,2), photo  blob, edocs bfile);

 Making Logical directory to insert BFILE value:
 DBA : >grant create any directory to scott;
 Scott : >create directory emp_dir as 'C:\Employ\Bonds';

 insert into emp_lob values(101, 'RAM',   '--------------------', 21000, empty_blob(),   bfilename(emp_dir, 'e101.doc'));

 Bfilename - Built_in sub program used to represent
                   Os file details into bfile column

 >select ecode,ename,sal,description from    emp_lob;

 * LOB/LONG  supports Dynamic memory allocation
  If data is  <= 4000 Bytes - Stored with in the                                                                 Table
                  >   4000 Bytes - Stored outside the                                                                 Table
-------------------------------------------------------------------