write log file from procedure in oracle

 create directory logfile as 'C:\log_file'; -- must have priv to do this

declare
  SFile utl_file.file_type;
begin
  SFile := utl_file.fopen(logfile ,'sunil','w'); -- w is write. This returns file handle
  utl_file.put(SFile,'Start write th log on log file'); -- note use of file handle vFile
  utl_file.fclose(SFile); -- note use of file handle vFile
end;

Read more ...

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;  

Read more ...

like with regular expressions in oracle

select sum(TERT_COUNT) from gj_chl_tert_sales where  REGEXP_LIKE(TERT_COUNT, '[[:digit:]]');

select sum(TERT_COUNT) from gj_chl_tert_sales where  REGEXP_LIKE(TERT_COUNT, '[^0-9]');

select sum(TERT_COUNT) from gj_chl_tert_sales where  REGEXP_LIKE(TERT_COUNT, '[[:alpha:]]');

select sum(TERT_COUNT) from gj_chl_tert_sales where  REGEXP_LIKE(TERT_COUNT, '[A-Z]');

Read more ...