Friday, November 20, 2020

Copy Files from one directory to another directory in PLSQL ( UTL_FILE.FCOPY, DBMS_LOB.FILEEXISTS example )

 

 

 

 

 

 

In this post, we have given a custom function which uses UTL_FILE.FCOPY to copy files from one directory to another directory. Further, custom function has below additional validations to check 

  1. Source DBA directory is valid or invalid
  2. Destination DBA directory is valid or invalid
  3. source directory exists in server
  4. destination directory exists in server
  5. source file exists or not. 
  6. Override of destination file is allowed based on user parameters

This function can be further expanded with other validations and utilized for file copy operations. Hope this is useful :)

Standard Utility Used: UTL_FILE.FCOPY and DBMS_LOB.FILEEXISTS

Custom Copy Function:

CREATE OR REPLACE FUNCTION FILECOPY (p_source_directory IN VARCHAR2, p_source_filename IN VARCHAR2, p_dest_directory IN VARCHAR2, p_dest_filename IN VARCHAR2, p_override_allowed IN VARCHAR2 DEFAULT 'N' ) RETURN VARCHAR2 AS lb_file_exists BOOLEAN; ln_dummy NUMBER; ln_file_size NUMBER; ln_block_size NUMBER; BEGIN --Check whether source directory exists BEGIN ln_dummy:= 0; SELECT 1 INTO ln_dummy FROM all_directories WHERE directory_name = p_source_directory; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Source DBA Directory Does not exists'; END; -- Check whether the directory present in server IF DBMS_LOB.FILEEXISTS(BFILENAME(p_source_directory,'.')) <> 1 THEN RETURN 'Source Directory Does not exists'; END IF; --Check whether destination directory exists BEGIN ln_dummy:= 0; SELECT 1 INTO ln_dummy FROM all_directories WHERE directory_name = p_dest_directory; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Destination DBA Directory Does not exists'; END; -- Check whether the directory present in server IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,'.')) <> 1 THEN RETURN 'Destination Directory Does not exists'; END IF; --Check whether source file exists IF DBMS_LOB.FILEEXISTS(BFILENAME(p_source_directory,p_source_filename)) <> 1 THEN RETURN 'Source File Does not exists'; END IF; --Check whether source file exists IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,p_dest_filename)) = 1 AND p_override_allowed = 'N' THEN RETURN 'Destination File Override is not allowed. But, destination file exists'; END IF; BEGIN UTL_FILE.FCOPY (p_source_directory, p_source_filename, p_dest_directory, p_dest_filename ); --Check whether destination file created IF DBMS_LOB.FILEEXISTS(BFILENAME(p_dest_directory,p_dest_filename)) = 1 THEN RETURN 'File Copied Successfully'; ELSE RETURN 'File Copy Failed. Please contact technical team'; END IF; EXCEPTION WHEN UTL_FILE.WRITE_ERROR THEN RETURN 'Destination Write/File Access denied'; WHEN UTL_FILE.READ_ERROR THEN RETURN 'Source Directory/File Access denied'; WHEN UTL_FILE.INVALID_OPERATION THEN -- This is true because we already checked other possible errors like -- source dir, destination dir , source file and dest file exists -- since above errs was not found, its assumed that access issue RETURN 'Directory/File Access denied'; WHEN OTHERS THEN RETURN SQLERRM; END; END FILECOPY;
 Sample Call to copy Function: 
SET SERVEROUTPUT ON; DECLARE lv_file_copy_sts VARCHAR2(2000); BEGIN lv_file_copy_sts := FILECOPY(p_source_directory => 'TEST1', p_source_filename => 'testfile.txt', p_dest_directory => 'TEST2', p_dest_filename => 'test.txt', p_override_allowed => 'Y' ); DBMS_OUTPUT.PUT_LINE('File copy Status: '||lv_file_copy_sts); END;
 Sample runs to copy Function: 



 

Friday, November 20, 2020 by Team search · 0

Query to get list of month and year since specific date in Oracle (Hierarchical queries - CONNECT BY Example)

 

 

 

 

 

 

 

        In this post, we have given a query which will help you to get the list of months with year information since specific date.

For example, below query will get months and year in format of JAN-2019, FEB-2019 till today.

 Query: 

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,((LEVEL-1)*-1)),'MON-RRRR') mon_yyyy FROM dual CONNECT BY ADD_MONTHS(SYSDATE,((LEVEL-1)*-1)) > TO_DATE('01-01-2019','MM-DD-RRRR') ORDER BY LEVEL;

Sample Run:

by Team search · 0

Thursday, November 12, 2020

Query to get hours between two dates excluding weekends

 

 

 

 

 

 

 

In this post, we have a custom function which we used to determine hours between two date excluding weekends. Hope this can act as a base to meet your requirements.

Custom Function: 

CREATE FUNCTION xxsh_timedif_without_wknds ( p_from_date IN DATE, 
                                             p_to_date   IN DATE 
                                           )
RETURN NUMBER
IS
ln_hours_between  NUMBER;
ld_start_date    DATE; 
ld_end_date      DATE; 
ln_num_of_wknds  NUMBER DEFAULT 0;
ln_return_value  NUMBER;
BEGIN 
  ln_hours_between := p_to_date - p_from_date; 
  
  IF TRUNC(p_to_date) = TRUNC(p_from_date)
     AND TO_CHAR(p_from_date,'DY','nls_date_language=english') NOT IN ('SAT','SUN')
     AND TO_CHAR(p_to_date,'DY','nls_date_language=english') NOT IN ('SAT','SUN')
  THEN
    ln_return_value := ln_hours_between;
    
  ELSIF ln_hours_between <= 2
     AND TO_CHAR(p_from_date,'DY','nls_date_language=english')  IN ('SAT','SUN')
     AND TO_CHAR(p_to_date,'DY','nls_date_language=english')  IN ('SAT','SUN')
  THEN 
    ln_return_value := 0;

  ELSE 
    IF TO_CHAR(p_from_date,'DY','nls_date_language=english')  = 'SAT'
    THEN
      ld_start_date := TRUNC(p_from_date+2);
    ELSIF TO_CHAR(p_from_date,'DY','nls_date_language=english')  = 'SUN'
    THEN
      ld_start_date := TRUNC(p_from_date+1);
    ELSE 
      ld_start_date :=  p_from_date;
    END IF;
    
    IF TO_CHAR(p_to_date,'DY','nls_date_language=english')  = 'SAT'
    THEN
      ld_end_date := TRUNC(p_to_date) - (1/(24*60*60));
    ELSIF TO_CHAR(p_to_date,'DY','nls_date_language=english')  = 'SUN'
    THEN
      ld_end_date := TRUNC(p_to_date-1) - (1/(24*60*60));
    ELSE 
      ld_end_date :=  p_to_date;
    END IF;
    
    SELECT COUNT(1)
      INTO ln_num_of_wknds
      FROM dual
     WHERE TO_CHAR(ld_start_date+ level-1,'DY','nls_date_language=english') IN ('SAT','SUN')  
   CONNECT BY LEVEL <= CEIL(ld_end_date - ld_start_date);
   
    ln_return_value := (ld_end_date - ld_start_date) - ln_num_of_wknds;
  END IF;  
   RETURN(ln_return_value * 24);
END xxsh_timedif_without_wknds;                                                     

Sample Call:

If you run the query on Friday, the below query will fetch 24 hours, whereas if you run the same query on Monday, then it will return 72 hours.

SELECT XXSH_TIMEDIF_WITHOUT_WKNDS(sysdate,sysdate+3) FROM dual;

 

Thursday, November 12, 2020 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.