// 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