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