Just another WordPress.com site

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: