Breaking News

Editors Picks

Tuesday, December 13, 2011

write log file from procedure in oracle

// first you will create the CREATE DIRECTORY f
create directory logfile as 'C:\LOG_DIR'; 
 
create or replace
PROCEDURE prcBreakDataCircleWise(tableName in varchar2) is
circlecode varchar2(20);
tbl_exist number;
logsFile UTL_FILE.FILE_TYPE;
strLog varchar2(500);
cursor  c1 is select distinct circle_code from circle_master ORDER BY CIRCLE_CODE ;
BEGIN
 open c1;
 logsFile:=UTL_FILE.FOPEN(logfile , 'log_file.txt', 'W');
  loop      
      fetch c1 into circlecode;     
        EXIT WHEN c1%NOTFOUND;     
       select count(*) into tbl_exist from user_tables where Upper(table_name)=Upper(circlecode||'_'||tableName);
        
        --DBMS_OUTPUT.put_line(tbl_exist);
       if tbl_exist = 1 then 
        EXECUTE IMMEDIATE 'drop table '||circlecode||'_'||tableName;     
        EXECUTE IMMEDIATE 'CREATE TABLE '||circlecode||'_'||tableName||' AS (SELECT * FROM '||tableName||' WHERE circle_code='''||circlecode||''') '  ;               
        UTL_FILE.PUTF(logsFile, circlecode||'_'||tableName ||' Created\n');  
      else 
         EXECUTE IMMEDIATE 'CREATE TABLE '||circlecode||'_'||tableName||' AS (SELECT * FROM '||tableName||' WHERE circle_code='''||circlecode||''') '  ;
          UTL_FILE.PUTF(logsFile, circlecode||'_'||tableName ||' Created\n');
       end if ;
    
 end loop;
 UTL_FILE.FCLOSE(logsFile);
close c1;
END prcBreakDataCircleWise;  

No comments :

Post a Comment

Contact Us

Name

Email *

Message *