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

How to call dialog in private solution in C# in IFS Applications?

SessionModalDialog(Pal.GetActiveInstanceName("dlgInvoicPeriodAllocation"), Ifs.Fnd.ApplicationForms.Int.Explorer.ExplorerForm, SalString.Null)

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;

Adding new item to default attr in client in IFS Applications

Override vrtDataRecordFetchEditedUser

public override SalNumber vrtDataRecordFetchEditedUser(ref SalString lsAttr)
{
	return this.DataRecordFetchEditedUser(ref lsAttr);
}

public new SalNumber DataRecordFetchEditedUser(ref SalString lsAttr)
{
	#region Actions
	using (new SalContext(this))
	{
		Ifs.Fnd.ApplicationForms.Int.PalAttrAddNumber("ACTIVITY_SEQ", colnActivitySeq.Number, ref lsAttr);
		Ifs.Fnd.ApplicationForms.Int.PalAttrAdd("DATE_TYPE", colsDateType.Text, ref lsAttr);
	}
	return 0;
	#endregion
}

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;

Navigate to Feature Form / Open Window using iURL in IFS Applications

To demonstrate the functions, opening quick report window is used.

WAY 1 - Using iURL

private void menuFrmMethods_menuTest_Execute(object sender, FndCommandExecuteEventArgs e)
{
    iURL.Clear();
    iURL.SetProtocol("ifswin");
    iURL.SetProgId("Ifs.Application.QuickReporting.QuickReportDetail");
    iURL.iParameters.SetAttribute("action", "get");
    iURL.iParameters.SetAttribute("key1", this.sCQuickReportId);
    iURL.Go();
}

WAY 2 - Using NavigationService.Navigate

Open new quick report window

public void OpenNewQuickReport()
{
    Ifs.Fnd.Core.FndUrlAddress url = new Ifs.Fnd.Core.FndUrlAddress("ifswin:Ifs.Application.QuickReporting.QuickReportDetail?action=new");
    this.NavigationService.Navigate(url, false, true);
}

Open existing quick report

public void OpenQuickReport(string sCQuickReportId)
{
    Ifs.Fnd.Core.FndUrlAddress url = new Ifs.Fnd.Core.FndUrlAddress("ifswin:Ifs.Application.QuickReporting.QuickReportDetail?action=get&key1=" + sCQuickReportId);
    this.NavigationService.Navigate(url, false, true);
}

How to export File Type and File Template to a .ins in IFS Applications?

To export the created File Type and File Template fully there is an existing File Template named ExtFileInsCreate (NOTE: This uses the procedure called External_File_Utility_API.File_Type_Create_Export). We could use this file template to create a .txt/.ins file that contains the full .ins script to create the File Type, File Template, etc. fully.

Steps to Create .ins

Other Utility File Templates

DescribeInput
File template used when creating an example file describing how to input data for a specific file template.

ExtFileExpImp
File template used when creating an export or import of a file type and all related data. It's also possible to export all related data to a file template without the data related to the file type.

ExtFileInsCreate
File template used when creating insert instructions for a file type and all related data. It's also possible to create insert instructions for related data to a file template without the data related to the file type.

RemoveTrans
File template used when ordering a job that removes transactions in the External File Transactions storage.

ExtFileFunctionList
File template used to create a list of valid column functions

Useful Links:

> External Files Interface

What the maximum number of records that can be exported via Excel export in IFS Applications? and How to modify it?

What the maximum number of records that can be exported via Excel export?

How to modify it?

NOTE: The maximum output channel limit is  100,000

Useful Links:

> What is the maximum number of records can be exported via Excelex port?

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

Change a User's Password in Oracle, Unlock Oracle User

1. To unlock an account:

ALTER USER account ACCOUNT UNLOCK;

2. To reset the password:

ALTER USER user_name IDENTIFIED BY new_password;

Useful Links:

> Using SQL*Plus to Unlock Accounts and Reset Passwords