Read file content from an Oracle Directory in PL/SQL/ in IFS

Read single file

DECLARE
   file_handle_      UTL_FILE.FILE_TYPE;
   directory_name_   CONSTANT all_directories.directory_name%TYPE := 'DIRECTORY_NAME';
   file_name_        VARCHAR2(4000);
   line_             VARCHAR2(4000);
   file_content_     CLOB;
BEGIN
   -- initialize file content
   file_name_    := 'log_main.txt';
   file_content_ := NULL;
         
   BEGIN
      -- open file in read mode
      file_handle_ := UTL_FILE.FOPEN(directory_name_, file_name_, 'R');
            
      -- fetch data from file
      LOOP
         UTL_File.Get_Line(file_handle_, line_);
         
         file_content_ := file_content_ || line_;
      END LOOP;
            
      -- close the file
      UTL_FILE.FCLOSE(file_handle_);
   EXCEPTION
      WHEN no_data_found THEN
         -- close the file
         UTL_FILE.FCLOSE(file_handle_);
      WHEN OTHERS THEN
         -- close the file
         UTL_FILE.FCLOSE(file_handle_);
         
         -- reset file content
         file_content_ := NULL;
   END;
            
   -- output file content
   Dbms_Output.Put_Line(file_content_);
END;

Read multiple files NOTE: Filenames got via IFS Intface_Server_File_API.File_List procedure

DECLARE
   file_handle_      UTL_FILE.FILE_TYPE;
   directory_name_   CONSTANT all_directories.directory_name%TYPE := 'DIRECTORY_NAME';
   file_ist_         Intface_File_Types := Intface_File_Types();
   line_             VARCHAR2(4000);
   file_content_     CLOB;
BEGIN
   file_ist_ := Intface_Server_File_API.File_List(directory_name_);
   
   FOR i IN 1..file_ist_.count LOOP
      -- initialize file content
      file_content_ := NULL;
      
      BEGIN
         Dbms_Output.Put_Line(file_ist_(i).file_name);
         Dbms_Output.Put_Line('-----------------------------------------------------------------');
         
         -- open file in read mode
         file_handle_ := UTL_FILE.FOPEN(directory_name_, file_ist_(i).file_name, 'R');
            
         -- fetch data from file
         LOOP
            UTL_File.Get_Line(file_handle_, line_);
               
            file_content_ := file_content_ || line_;
         END LOOP;
            
         -- close the file
         UTL_FILE.FCLOSE(file_handle_);
      EXCEPTION
         WHEN no_data_found THEN
            -- close the file
            UTL_FILE.FCLOSE(file_handle_);
         WHEN OTHERS THEN
            -- close the file
            UTL_FILE.FCLOSE(file_handle_);
               
            -- reset file content
            file_content_ := NULL;
      END;
            
      -- output file content
      Dbms_Output.Put_Line(file_content_);
      Dbms_Output.Put_Line('');
   END LOOP;
END;

Related Links:

> Get file names in Oracle Directory in IFS without using Complied Java Source
> Get file names in an Oracle Directory using Java in Oracle PL/SQL

Remove new line characters in a VARCHAR2 in Oracle PL/SQL

-- Way 1
SELECT 
TRANSLATE('234
456
678', CHR(10) || CHR(13), '$') FROM DUAL;

-- Way 2
SELECT REPLACE(REPLACE('234
456
678', CHR(10), '$'), CHR(13), '%') FROM DUAL;

How to set background job Progress Info and Status Info in IFS Applications?

-- Call the procedure to set progress info
Transaction_SYS.Set_Progress_Info(info_ => :info_);

-- Call the procedure to set status info
Transaction_SYS.Set_Status_Info(info_ => :info_, status_type_ => :status_type_);

New line in Oracle PL/SQL

Use CHR(13) || CHR(10)

DECLARE 
   NEW_LINE_   CONSTANT VARCHAR2(2)  := CHR(13) || CHR(10);
BEGIN
   Dbms_Output.Put_Line('Hi, '    || NEW_LINE_ || 
                        'good'    || NEW_LINE_ ||
                        'morning' || NEW_LINE_ ||
                        'friends');
END;

Output

Add Months in Oracle PL/SQL

Example 1

SELECT ADD_MONTHS(TO_DATE('2021-08-01-00.00.00', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN'), 3)
FROM   DUAL;

Example 2

SELECT ADD_MONTHS(DATE '2016-02-29', 1)
FROM   DUAL;

Useful Links:

> PLSQL | ADD_MONTHS Function

Months between 2 dates in Oracle PL/SQL

WAY 1

SELECT MONTHS_BETWEEN(TRUNC(TO_DATE('2021-12-01-00.00.00', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN')), 
                      TRUNC(TO_DATE('2021-10-01-00.00.00', 'YYYY-MM-DD-HH24.MI.SS', 'NLS_CALENDAR=GREGORIAN')))
FROM   DUAL;

WAY 2

DECLARE 
   str_out_     VARCHAR2(32000);
   from_date_   DATE;
   to_date_     DATE;
   
   CURSOR add_month IS
      SELECT ADD_MONTHS(from_date_, 1)
      FROM   DUAL;
BEGIN
   from_date_ := TO_DATE('2021-08-01-00.00.00','YYYY-MM-DD-HH24.MI.SS','NLS_CALENDAR=GREGORIAN');
   to_date_   := TO_DATE('2021-12-21-00.00.00','YYYY-MM-DD-HH24.MI.SS','NLS_CALENDAR=GREGORIAN');
   
   str_out_ := '''' || TO_CHAR(from_date_, 'FMMON-YY') || '''';
   
   OPEN  add_month;
   FETCH add_month INTO from_date_;
   CLOSE add_month;
   
   WHILE from_date_ <= to_date_
   LOOP
      str_out_ := str_out_ || ', ''' || TO_CHAR(from_date_, 'FMMON-YY') || '''';
      
      OPEN  add_month;
      FETCH add_month INTO from_date_;
      CLOSE add_month;
   END LOOP;
   
   Dbms_Output.Put_Line(str_out_);
END;

Output

Export permission set from database in IFS Applications

e.g.: APPS75

BEGIN
  -- Call the procedure
  Security_SYS.Export_Role__(security_permissions_         => :security_permissions_,
                             count_                        => :count_,
                             role_                         => 'IFSINFO',
                             include_presentation_objects_ => 'TRUE',
                             include_database_objects_     => 'TRUE',
                             include_activities_           => 'TRUE',
                             comment_define_               => 'FALSE',
                             requested_string_             => 1,
                             include_role_grants_          => 'TRUE');
END;

e.g.: APPS10

BEGIN
  -- Call the procedure
  Security_SYS.Export_Role__(string_                       => :string_,
                             role_                         => 'IFSINFO',
                             include_presentation_objects_ => 'TRUE',
                             include_database_objects_     => 'TRUE',
                             include_activities_           => 'TRUE',
                             include_projections_          => 'TRUE',
                             comment_define_               => 'FALSE', -- If TRUE, the top most DEFINE blocks are commented
                             include_role_grants_          => 'TRUE'); -- LAST Grant section
END;

Sleep in PL/SQL

DBMS_LOCK.Sleep( milliseconds );

e.g.:

DBMS_LOCK.Sleep( 15 );

Get oracle service name

SELECT * FROM V$SERVICES;

Search in Event Actions in IFS

SELECT * 
FROM   fnd_event_action_tab
WHERE  UPPER(action_parameters) LIKE UPPER('%SEARCH_STRING%');

e.g.:

SELECT * 
FROM   fnd_event_action_tab
WHERE  UPPER(action_parameters) LIKE UPPER('%Mail%');

Select only NUMBER values in SELECT statement

Use REGEXP_LIKE(column_name, '^[[:digit:]]+$') in where clause

SELECT column_name 
FROM   some_table 
WHERE  REGEXP_LIKE(column_name, '^[[:digit:]]+$');

Useful Links:

> Oracle: SQL query that returns rows with only numeric values

Measuring Elapsed Time to Run a SQL Query in PL/SQL

DECLARE 
   t1   timestamp;
   t2   timestamp;
BEGIN
   t1 := systimestamp; 

   FOR i IN 1..1000 LOOP
      Dbms_Output.Put_Line(TO_CHAR(i));
   END LOOP;
   
   t2 := systimestamp; 

   Dbms_Output.Put_Line('Start: ' || t1); 
   Dbms_Output.Put_Line('  End: ' || t2); 
   Dbms_Output.Put_Line('Elapsed Seconds: ' || TO_CHAR(t2 - t1, 'SSSS.FF'));
END;

Useful Links:

> Measuring Elapsed Time to Run a SQL Query in PL/SQL

Find total occurrences/count in string in PL/SQL

DECLARE
   count_                    NUMBER;
   count_with_ignore_case_   NUMBER;
   data_                     CLOB;
BEGIN
   data_ := 'The REGEXP_LIKE condition uses the input character set to evaluate strings.
If you specify match_parameter values that conflict, the REGEXP_LIKE condition will use the last value to break the conflict.
If the match_parameter is omitted, the REGEXP_LIKE condition will use the case-sensitivity as determined by the NLS_SORT parameter.';

   count_                  := REGEXP_COUNT(data_, 'the');
   count_with_ignore_case_ := REGEXP_COUNT(LOWER(data_), LOWER('the'));
   
   Dbms_Output.Put_Line('Exact Count: ' || count_);
   Dbms_Output.Put_Line('  All Count: ' || count_with_ignore_case_);
END;

Useful Links:

> Oracle / PLSQL: REGEXP_COUNT Function

Get file names in Oracle Directory in IFS without using Complied Java Source

DECLARE
   file_handle_      UTL_FILE.FILE_TYPE;
   directory_name_   all_directories.directory_name%TYPE;
   file_list_        Intface_File_Types := Intface_File_Types();
   file_count_       NUMBER;
   
   FUNCTION Get_File_Count (
      temp_directory_name_ IN VARCHAR2 ) RETURN NUMBER
   IS
      file_count_       NUMBER;
      temp_file_list_   Intface_File_Types := Intface_File_Types();
   BEGIN
      file_count_ := 0;
      
      temp_file_list_ := Intface_Server_File_API.File_List(temp_directory_name_);
   
      FOR i IN 1..temp_file_list_.count LOOP
         file_count_ := file_count_ + 1;
      END LOOP;
      
      RETURN file_count_;
   END Get_File_Count;
BEGIN
   directory_name_ := 'ORACLE_DIRECTORY_NAME';
   file_count_     := Get_File_Count(directory_name_);
      
   IF (file_count_ > 0) THEN
      Dbms_Output.Put_Line(directory_name_ || ' - ' || file_count_);
         
      file_list_ := Intface_Server_File_API.File_List(directory_name_);
   
      FOR i IN 1..file_list_.count LOOP
         Dbms_Output.Put_Line(file_list_(i).file_name);
      END LOOP;
   END IF;
END;

Related Links:

> Get file names in an Oracle Directory using Java in Oracle PL/SQLL

DROP COMPILE JAVA SOURCE NAMED in ORACLE

Complied Java Source

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "RmaxFileHandler" AS

DROP Complied Java Source

DROP JAVA SOURCE "SCHEMA"."JAVASOURCENAME";

E.g.

DROP JAVA SOURCE "RMAX"."RmaxFileHandler";

Binding Variables with EXECUTE IMMEDIATE of PL/SQL Block

Useful Links:

> Binding Variables with EXECUTE IMMEDIATE of PL/SQL Block

Get Logged in Database/Environment Name in IFS

SELECT Sys_Context('USERENV', 'DB_NAME') FROM DUAL

Get file names in an Oracle Directory using Java in Oracle PL/SQL

How to read multiple files/folders from an Oracle Directory using Java in Oracle PL/SQL?

1. Create a COMPILE JAVA SOURCE NAMED using below code (.cdb in IFS)

--
-- Module : MODULE_NAME
--
-- Filename : FILE_NAME
--
-- Purpose : Access files in oracle directory
--
--  Date    Sign     History
--  ------  -------  --------------------------------------------------------
--  
-----------------------------------------------------------------------------

SET SERVEROUTPUT ON SIZE 32000

PROMPT Starting FILE_NAME...

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "RmaxFileHandler" AS
import java.io.*;
import java.util.*;

public class RmaxFileHandler {
    private static String SEMICOLON = ";";
    private static int VARCHAR2_MAX_LENGTH = 3000;
    private static int VARCHAR2_SAFETY_BUFFER = 1000;

    // Private Functions - START
    private static ArrayList getFilePaths(String oracleDirectoryPath, String folderSeparator, boolean includeSubFolders) {
        ArrayList filePathList = new ArrayList();

        try {
            String searchFolderSeparator = getSearchFolderSeparator(folderSeparator);

            //Creating a File object for directory
            File directoryPath = new File(oracleDirectoryPath);

            //List of all files and directories
            String contents[] = directoryPath.list();

            for(int i=0; i getRelativeFilePaths(String oracleDirectoryPath, String folderSeparator, boolean includeSubFolders) {
        ArrayList relativeFilePaths = getFilePaths(oracleDirectoryPath, folderSeparator, includeSubFolders);
        String searchFolderSeparator = getSearchFolderSeparator(folderSeparator);

        // set correct folder separator in oracleDirectoryPath
        oracleDirectoryPath = oracleDirectoryPath.replace(searchFolderSeparator, folderSeparator);

        for (int i = 0; i < relativeFilePaths.size(); i++) {
            // get file path
            String filePath = relativeFilePaths.get(i);

            // set relative file path
            String relativeFilePath = filePath.replace(oracleDirectoryPath, "");

            // get file paths
            relativeFilePaths.set(i, relativeFilePath);
        }

        return relativeFilePaths;
    }

    private static ArrayList getFileNames(String oracleDirectoryPath, String folderSeparator, boolean includeSubFolders) {
        ArrayList filePathList = new ArrayList();

        try {
            String searchFolderSeparator = getSearchFolderSeparator(folderSeparator);

            //Creating a File object for directory
            File directoryPath = new File(oracleDirectoryPath);

            //List of all files and directories
            String contents[] = directoryPath.list();

            for(int i=0; i getFolderNames(String oracleDirectoryPath, String folderSeparator, boolean includeSubFolders) {
        ArrayList filePathList = new ArrayList();

        try {
            String searchFolderSeparator = getSearchFolderSeparator(folderSeparator);

            //Creating a File object for directory
            File directoryPath = new File(oracleDirectoryPath);

            //List of all files and directories
            String contents[] = directoryPath.list();

            for(int i=0; i filePaths, Integer lastFilePathId) {
        int startIndex = -1;
        String strFilePathList = "";

        if (lastFilePathId == -1) {
            startIndex = 0;
        } else {
            startIndex = (lastFilePathId + 1);
        }

        for (int i = startIndex; i < filePaths.size(); i++) {
            // Prevent directory listing expanding if we will blow VARCHAR2 limit.
            if ((strFilePathList.length() + filePaths.get(i).length() + VARCHAR2_SAFETY_BUFFER) > VARCHAR2_MAX_LENGTH) {
                strFilePathList += "$$" + (i - 1) + "$$";
                break;
            }

            strFilePathList += filePaths.get(i) + SEMICOLON;
        }

        return strFilePathList;
    }
    // Private Functions - FINISH


    // Public Functions - START
    public static String getFilePathsToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFilePathId) {
        ArrayList filePaths = getFilePaths(oracleDirectoryPath, folderSeparator, false);
        return concatFilePaths(filePaths, lastFilePathId);
    }

    public static String getAllFilePathsToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFilePathId) {
        ArrayList filePaths = getFilePaths(oracleDirectoryPath, folderSeparator, true);
        return concatFilePaths(filePaths, lastFilePathId);
    }

    public static String getRelativeFilePathsToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFilePathId) {
        ArrayList relativeFilePaths = getRelativeFilePaths(oracleDirectoryPath, folderSeparator, false);
        return concatFilePaths(relativeFilePaths, lastFilePathId);
    }

    public static String getAllRelativeFilePathsToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFilePathId) {
        ArrayList relativeFilePaths = getRelativeFilePaths(oracleDirectoryPath, folderSeparator, true);
        return concatFilePaths(relativeFilePaths, lastFilePathId);
    }

    public static String getFileNamesToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFileNameId) {
        ArrayList filePaths = getFileNames(oracleDirectoryPath, folderSeparator, false);
        return concatFilePaths(filePaths, lastFileNameId);
    }

    public static String getAllFileNamesToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFileNameId) {
        ArrayList filePaths = getFileNames(oracleDirectoryPath, folderSeparator, true);
        return concatFilePaths(filePaths, lastFileNameId);
    }

    public static String getFolderNamesToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFolderNameId) {
        ArrayList filePaths = getFolderNames(oracleDirectoryPath, folderSeparator, false);
        return concatFilePaths(filePaths, lastFolderNameId);
    }

    public static String getAllFolderNamesToClob(String oracleDirectoryPath, String folderSeparator, Integer lastFolderNameId) {
        ArrayList filePaths = getFolderNames(oracleDirectoryPath, folderSeparator, true);
        return concatFilePaths(filePaths, lastFolderNameId);
    }
    // Public Functions - FINISH
};
/

SET SERVEROUTPUT OFF;

PROMPT Finished with FILE_NAME...

2. Create Oracle Package Spec (.api in IFS)

-----------------------------------------------------------------------------
--
--  Logical unit: RmaxFileHandler
--
--  Date    Sign     History
--  ------  -------  --------------------------------------------------------
--  
-----------------------------------------------------------------------------

DEFINE MODULE        = RMAX
DEFINE LU            = RmaxFileHandler
DEFINE PKG           = RMAX_FILE_HANDLER_API


PROMPT Creating &PKG specification

CREATE OR REPLACE PACKAGE &PKG IS

module_  CONSTANT VARCHAR2(25) := '&MODULE';
lu_name_ CONSTANT VARCHAR2(25) := '&LU';

-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PRIVATE METHODS ----------------------------
-----------------------------------------------------------------------------

FUNCTION Get_File_Paths_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_path_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getFilePathsToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';

FUNCTION Get_All_File_Paths_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_path_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getAllFilePathsToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_Relative_File_Paths_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_path_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getRelativeFilePathsToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_All_Rel_File_Paths_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_path_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getAllRelativeFilePathsToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_File_Names_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_name_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getFileNamesToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_All_File_Names_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_file_name_id_     IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getAllFileNamesToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_Folder_Names_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_folder_name_id_   IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getFolderNamesToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';


FUNCTION Get_All_Folder_Names_Java__ (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2,
   last_folder_name_id_   IN NUMBER ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'RmaxFileHandler.getAllFolderNamesToClob (java.lang.String, java.lang.String, java.lang.Integer) return java.lang.String';

-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PROTECTED METHODS --------------------------
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PUBLIC METHODS -----------------------------
-----------------------------------------------------------------------------

-- Get full file paths of the specified oracle directory
FUNCTION Get_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get full file paths of the specified oracle directory and subdirectories
FUNCTION Get_All_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get relative file paths of the specified oracle directory
FUNCTION Get_Relative_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get relative file paths of the specified oracle directory and subdirectories
FUNCTION Get_All_Relative_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get file names of the specified oracle directory
FUNCTION Get_File_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get file names of the specified oracle directory and subdirectories
FUNCTION Get_All_File_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get folder names of the specified oracle directory
FUNCTION Get_Folder_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- Get folder names of the specified oracle directory and subdirectories
FUNCTION Get_All_Folder_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB;


-- A sample test procedure for above all functions
PROCEDURE Sample_Test;

-----------------------------------------------------------------------------
-------------------- FOUNDATION1 METHODS ------------------------------------
-----------------------------------------------------------------------------

PROCEDURE Init;


END &PKG;
/
SHOW ERROR

UNDEFINE MODULE
UNDEFINE LU
UNDEFINE PKG
-----------------------------------------------------------------------------

3. Create Create Oracle Package Body (.apy in IFS)

-----------------------------------------------------------------------------
--
--  Logical unit: RmaxFileHandler
--
--  Purpose: LU for Handle Files in Oracle Directory
--
--  Date    Sign     History
--  ------  -------  --------------------------------------------------------
--  
-----------------------------------------------------------------------------

DEFINE MODULE        = RMAX
DEFINE LU            = RmaxFileHandler
DEFINE PKG           = RMAX_FILE_HANDLER_API

-----------------------------------------------------------------------------
-------------------- PACKAGES FOR METHODS -----------------------------------
-----------------------------------------------------------------------------

PROMPT Creating &PKG implementation

CREATE OR REPLACE PACKAGE BODY &PKG IS

-----------------------------------------------------------------------------
-------------------- GLOBAL LU CONSTANTS ------------------------------------
-----------------------------------------------------------------------------

JAVA_GET_FILE_PATHS           CONSTANT VARCHAR2(30) := 'Get_File_Paths_Java__';
JAVA_GET_ALL_FILE_PATHS       CONSTANT VARCHAR2(30) := 'Get_All_File_Paths_Java__';
JAVA_GET_REL_FILE_PATHS       CONSTANT VARCHAR2(30) := 'Get_Relative_File_Paths_Java__';
JAVA_GET_ALL_REL_FILE_PATHS   CONSTANT VARCHAR2(30) := 'Get_All_Rel_File_Paths_Java__';
JAVA_GET_FILE_NAMES           CONSTANT VARCHAR2(30) := 'Get_File_Names_Java__';
JAVA_GET_ALL_FILE_NAMES       CONSTANT VARCHAR2(30) := 'Get_All_File_Names_Java__';
JAVA_GET_FOLDER_NAMES         CONSTANT VARCHAR2(30) := 'Get_Folder_Names_Java__';
JAVA_GET_ALL_FOLDER_NAMES     CONSTANT VARCHAR2(30) := 'Get_All_Folder_Names_Java__';

-----------------------------------------------------------------------------
-------------------- LU SPECIFIC IMPLEMENTATION METHOD DECLARATIONS ---------
-----------------------------------------------------------------------------

FUNCTION Retrieve_File_Paths___ (
   java_function_         IN VARCHAR2,
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 ) RETURN CLOB;
  
-----------------------------------------------------------------------------
-------------------- LU SPECIFIC IMPLEMENTATION METHODS ---------------------
-----------------------------------------------------------------------------

FUNCTION Retrieve_File_Paths___ (
   java_function_         IN VARCHAR2,
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 ) RETURN CLOB
IS
   file_paths_       CLOB;
   last_file_path_   NUMBER;
   has_more_files_   NUMBER;
   
   CURSOR get_last_file_path IS
      SELECT TO_NUMBER(SUBSTR(SUBSTR(file_paths_, (INSTR(file_paths_, '$$') + 2)), 1, (LENGTH(SUBSTR(file_paths_, (INSTR(file_paths_, '$$') + 2))) - 2)))
      FROM   DUAL;
   
   FUNCTION Get_Correct_File_Paths (
      temp_file_paths_ IN CLOB ) RETURN CLOB
   IS
   BEGIN
      RETURN SUBSTR(temp_file_paths_, 1, (INSTR(temp_file_paths_, '$$') - 1));
   END;
BEGIN
   last_file_path_ := -1;
   
   IF (java_function_ = JAVA_GET_FILE_PATHS) THEN
      file_paths_ := Get_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_ALL_FILE_PATHS) THEN
      file_paths_ := Get_All_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_REL_FILE_PATHS) THEN
      file_paths_ := Get_Relative_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_ALL_REL_FILE_PATHS) THEN
      file_paths_ := Get_All_Rel_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_FILE_NAMES) THEN
      file_paths_ := Get_File_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_ALL_FILE_NAMES) THEN
      file_paths_ := Get_All_File_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_FOLDER_NAMES) THEN
      file_paths_ := Get_Folder_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   ELSIF (java_function_ = JAVA_GET_ALL_FOLDER_NAMES) THEN
      file_paths_ := Get_All_Folder_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
   END IF;
   
   WHILE (INSTR(file_paths_, '$$') > 0) LOOP
      OPEN  get_last_file_path;
      FETCH get_last_file_path INTO last_file_path_;
      IF (get_last_file_path%NOTFOUND) THEN
         last_file_path_ := -1;
      END IF;
      CLOSE get_last_file_path;
      
      file_paths_ := Get_Correct_File_Paths(file_paths_);
      
      IF (java_function_ = JAVA_GET_FILE_PATHS) THEN
         file_paths_ := file_paths_ || Get_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_ALL_FILE_PATHS) THEN
         file_paths_ := file_paths_ || Get_All_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_REL_FILE_PATHS) THEN
         file_paths_ := file_paths_ || Get_Relative_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_ALL_REL_FILE_PATHS) THEN
         file_paths_ := file_paths_ || Get_All_Rel_File_Paths_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_FILE_NAMES) THEN
         file_paths_ := file_paths_ || Get_File_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_ALL_FILE_NAMES) THEN
         file_paths_ := file_paths_ || Get_All_File_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_FOLDER_NAMES) THEN
         file_paths_ := file_paths_ || Get_Folder_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      ELSIF (java_function_ = JAVA_GET_ALL_FOLDER_NAMES) THEN
         file_paths_ := file_paths_ || Get_All_Folder_Names_Java__(oracle_directory_path_, NVL(folder_separator_, '/'), last_file_path_);
      END IF;
   END LOOP;
   
   RETURN file_paths_;
END Retrieve_File_Paths___;

-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PRIVATE METHODS ----------------------------
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PROTECTED METHODS --------------------------
-----------------------------------------------------------------------------


-----------------------------------------------------------------------------
-------------------- LU SPECIFIC PUBLIC METHODS -----------------------------
-----------------------------------------------------------------------------

-- Get full file paths of the specified oracle directory
FUNCTION Get_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_FILE_PATHS, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_File_Paths;


-- Get full file paths of the specified oracle directory and subdirectories
FUNCTION Get_All_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_ALL_FILE_PATHS, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_All_File_Paths;


-- Get relative file paths of the specified oracle directory
FUNCTION Get_Relative_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_REL_FILE_PATHS, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_Relative_File_Paths;


-- Get relative file paths of the specified oracle directory and subdirectories
FUNCTION Get_All_Relative_File_Paths (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_ALL_REL_FILE_PATHS, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_All_Relative_File_Paths;


-- Get file names of the specified oracle directory
FUNCTION Get_File_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_FILE_NAMES, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_File_Names;


-- Get file names of the specified oracle directory and subdirectories
FUNCTION Get_All_File_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_ALL_FILE_NAMES, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_All_File_Names;


-- Get folder names of the specified oracle directory
FUNCTION Get_Folder_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_FOLDER_NAMES, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_Folder_Names;


-- Get folder names of the specified oracle directory and subdirectories
FUNCTION Get_All_Folder_Names (
   oracle_directory_path_ IN VARCHAR2, 
   folder_separator_      IN VARCHAR2 DEFAULT '/' ) RETURN CLOB
IS
BEGIN
   RETURN Retrieve_File_Paths___(JAVA_GET_ALL_FOLDER_NAMES, oracle_directory_path_, NVL(folder_separator_, '/'));
END Get_All_Folder_Names;


-- A sample test procedure for above all functions
PROCEDURE Sample_Test 
IS
   file_paths_        CLOB;
   file_path_value_   VARCHAR2(4000);
   directory_path_    VARCHAR2(1000);
   start_pos_         NUMBER;
   delimiter_pos_     NUMBER;
   delimiter_         VARCHAR2(1) := ';';
   counter_           NUMBER;
   
   CURSOR get_directory_path IS 
      SELECT directory_path
      FROM   all_directories
      WHERE  directory_name = 'NAME_OF_ORACLE_DIRECTORY';
   
   CURSOR get_file_path IS
      SELECT regexp_substr(file_paths_, '[^;]+', 1, level) AS split_value
      FROM dual
      CONNECT BY regexp_substr(file_paths_, '[^;]+', 1, level) IS NOT NULL;
BEGIN
   OPEN  get_directory_path;
   FETCH get_directory_path INTO directory_path_;
   CLOSE get_directory_path;
   
   file_paths_ := Rmax_File_Handler_API.Get_File_Paths(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_All_File_Paths(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_Relative_File_Paths(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_All_Relative_File_Paths(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_File_Names(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_All_File_Names(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_Folder_Names(directory_path_, '/');
   --file_paths_ := Rmax_File_Handler_API.Get_All_Folder_Names(directory_path_, '/');
   
   --Dbms_Output.Put_Line(file_paths_);
   
   start_pos_     := 1;
   delimiter_pos_ := 1;
   counter_       := 1;
   
   WHILE (delimiter_pos_ <= LENGTH(file_paths_)) LOOP
      delimiter_pos_   := INSTR(file_paths_, delimiter_, start_pos_);
      file_path_value_ := SUBSTR(file_paths_, start_pos_, (delimiter_pos_ - start_pos_));
      
      Dbms_Output.Put_Line(LPAD(counter_, 5, '0') || '. ' || file_path_value_);
      
      IF (delimiter_pos_ = LENGTH(file_paths_)) THEN
         EXIT;
      END IF;
      
      start_pos_ := delimiter_pos_ + 1;
      counter_   := counter_ + 1;
   END LOOP;
END Sample_Test;

-----------------------------------------------------------------------------
-------------------- FOUNDATION1 METHODS ------------------------------------
-----------------------------------------------------------------------------
-- Init
--   Dummy procedure that can be called at database startup to ensure that
--   this package is loaded into memory for performance reasons only.
-----------------------------------------------------------------------------

PROCEDURE Init
IS
BEGIN
   NULL;
END Init;

END &PKG;
/
SHOW ERROR

UNDEFINE MODULE
UNDEFINE LU
UNDEFINE PKG
-----------------------------------------------------------------------------

4. Testing

BEGIN
  -- Call the procedure
  Rmax_File_Handler_API.Sample_Test;
END;

Useful Links:

> File Handling From PL/SQL

Related Links:

> Get file names in Oracle Directory in IFS without using Complied Java Source

BULK COLLECT / TYPE Record in Oracle

DECLARE 
   sql_stmt_   VARCHAR2(32000);
   
   CURSOR get_data IS
      SELECT column_1, 
             column_2, 
             column_3, 
             column_4, 
             column_5
      FROM   some_tab
      WHERE  ROWNUM <= 10; -- ROWNUM is optional
   
   -- create type using a cursor
   TYPE temp_rec_type IS TABLE OF get_data%ROWTYPE INDEX BY BINARY_INTEGER;
   temp_rec_   temp_rec_type;
BEGIN
   -- Way 1 - Load data using normal cursor open fecth
   --OPEN  get_data;
   --FETCH get_data BULK COLLECT INTO temp_rec_;
   --CLOSE get_data;
   
   -- Way 2 - Load data using executing sql statement via EXECUTE IMMEDIATE
   sql_stmt_ := 'SELECT column_1, 
                        column_2, 
                        column_3, 
                        column_4, 
                        column_5
                 FROM   some_tab
                 WHERE  ROWNUM <= 10';
   
   EXECUTE IMMEDIATE sql_stmt_ BULK COLLECT INTO temp_rec_;
   
   -- loop of fetch data
   FOR i_ IN 1..temp_rec_.COUNT LOOP
      Dbms_Output.Put_Line(temp_rec_(i_).column_1 || ';' ||
                           temp_rec_(i_).column_2 || ';' ||
                           temp_rec_(i_).column_3 || ';' ||
                           temp_rec_(i_).column_4 || ';' ||
                           temp_rec_(i_).column_5 ||);
   END LOOP;
END;

Useful Links:

> Bulk Processing with PL/SQL

Create Sequence in IFS

DECLARE
   seq_exists_   NUMBER;
   sql_stmt_     VARCHAR2(2000);
   
   CURSOR check_exists IS
      SELECT 1
      FROM   all_objects
      WHERE  object_name = 'SEQUENCE_NAME_SEQ'
      AND    object_type = 'SEQUENCE';
BEGIN
   OPEN  check_exists;
   FETCH check_exists INTO seq_exists_;
   IF (check_exists%NOTFOUND) THEN
      seq_exists_ := 0;
   END IF;
   CLOSE check_exists;
   
   IF (seq_exists_ = 0) THEN
      Database_SYS.Create_Sequence('SEQUENCE_NAME_SEQ', 'MINVALUE 1 START WITH 1 INCREMENT BY 1');
   END IF;
END;
/