Read/Write a file through Oracle dataBase And UTL FILE

1. Craete a virtual directory by going to

   ServerIP/em -> Schema ->Directory Objects   Named UNESCO_PHONE_BOOK

2. Name of procedure : rowcount

(v_file_name varchar2,v_group_id number)            
IS 
    f utl_file.file_type; 
    s varchar2(200); 
n number := 0; 
v_sql varchar2(500);                            
R_FILE UTL_FILE.FILE_TYPE;                            
W_FILE UTL_FILE.FILE_TYPE;                            
R_File_content varchar2(250);                            
Mobile_no varchar2(100);                     
v_msisdn varchar2(100);                
c_name varchar2(250);                           
mob number;                        
   NOTFOUND_ERROR EXCEPTION;      
   NUMBER_FORMAT EXCEPTION;      
   MSISDN_LENGTH EXCEPTION;      

begin 
          
        R_FILE := UTL_FILE.FOPEN('UNESCO_PHONE_BOOK',v_file_name, 'R');                             
    W_FILE := UTL_FILE.FOPEN('UNESCO_PHONE_BOOK',v_file_name||'.bad', 'W');                             
        --f := utl_file.fopen('UNESCO_PHONE_BOOK',v_file_name,'R'); 
    loop 
        utl_file.get_line(R_FILE,R_File_content); 

        
    if R_File_content is not null  then
begin
--UTL_FILE.PUT_LINE (W_FILE, ' test\n');   
        Mobile_no:=replace(substr(R_File_content,0,instr(R_File_content,',')-1),' ','');                          
        mob := replace(to_number(Mobile_no),' ','');                     
        DBMS_OUTPUT.PUT_LINE(substr(Mobile_no,0,3)|| '    '|| length(Mobile_no));        
        SELECT msisdn into v_msisdn from WHITELIST where msisdn=Mobile_no;       
        IF (substr(Mobile_no,0,3)<>'030'  )THEN                         
            raise NUMBER_FORMAT;                         
        END IF;                       
        IF (length(Mobile_no) <> 11)THEN                         
            raise MSISDN_LENGTH;                         
        END IF;                                    
        c_name := substr(R_File_content,instr(R_File_content,',')+1);                           
        DBMS_OUTPUT.put_line( trim(Mobile_no)||'-'||c_name||'-'||v_group_id);           
        insert into LEARNERS (MSISDN, NAME, GROUP_ID)  values (trim(Mobile_no),c_name,v_group_id);                                     
        commit; 
        EXCEPTION                        
            WHEN NO_DATA_FOUND THEN 
            DBMS_OUTPUT.PUT_LINE('Error'||sqlcode);        
            UTL_FILE.PUT_LINE (W_FILE,R_File_content|| ' ------Does Not exist in Whitelist');    
            
            WHEN NUMBER_FORMAT THEN             
                          DBMS_OUTPUT.PUT_LINE('Error'||sqlcode);        
              UTL_FILE.PUT_LINE (W_FILE, R_File_content||' --------MSISDN Format is wrong');                      
 
            WHEN MSISDN_LENGTH THEN             
                          DBMS_OUTPUT.PUT_LINE('Error'||sqlcode);        
              UTL_FILE.PUT_LINE (W_FILE, R_File_content||' --------MSISDN Length is wrong');               
 
            WHEN DUP_VAL_ON_INDEX THEN             
                          DBMS_OUTPUT.PUT_LINE('Error'||sqlcode);        
              UTL_FILE.PUT_LINE (W_FILE, R_File_content||' -------- Duplicate MSISDN ');                      
 
            WHEN OTHERS THEN             
                          DBMS_OUTPUT.PUT_LINE('Error'||sqlcode);        
              UTL_FILE.PUT_LINE (W_FILE, R_File_content||' Other');              
 
            
end;
        --n := n+1;  

    end if; 
        --dbms_output.put_line(R_File_content); 
    end loop; 
exception 
    when NO_DATA_FOUND then 
    --dbms_output.put_line('no of lines:'||n); 
        utl_file.fclose(R_FILE);  
        utl_file.fclose(W_FILE); 
end;

3. To execute :

   exec rowcount('120308155704msisdn.txt',1);

where txt file is the file we jus uploaded and want to read

4.Tables Used:

    LEARNERS
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       MSISDN                                    NOT NULL VARCHAR2(11)
       NAME                                               VARCHAR2(20)
       GROUP_ID                                           NUMBER(10)
   WHITELIST
   Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    MSISDN                                             VARCHAR2(11)