SessionModalDialog(Pal.GetActiveInstanceName("dlgInvoicPeriodAllocation"), Ifs.Fnd.ApplicationForms.Int.Explorer.ExplorerForm, SalString.Null)Friday, July 29, 2022
How to call dialog in private solution in C# in IFS Applications?
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 FunctionMonths 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;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 InterfaceWhat 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%');Thursday, July 28, 2022
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 valuesMeasuring 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/SQLFind 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 FunctionGet 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/SQLLDROP COMPILE JAVA SOURCE NAMED in ORACLE
Complied Java Source
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "RmaxFileHandler" ASDROP Complied Java Source
DROP JAVA SOURCE "SCHEMA"."JAVASOURCENAME";E.g.
DROP JAVA SOURCE "RMAX"."RmaxFileHandler";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/SQLRelated Links:
> Get file names in Oracle Directory in IFS without using Complied Java SourceWednesday, July 27, 2022
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/SQLCreate 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
Subscribe to:
Posts (Atom)
Popular Posts
-
To export the created File Type and File Template fully there is an existing File Template named ExtFileInsCreate (NOTE: This uses the proc... -
DECLARE t1 timestamp; t2 timestamp; BEGIN t1 := systimestamp; FOR i IN 1..1000 LOOP Dbms_Output.Put_Line(TO_CHAR(i)...
-
To demonstrate the functions, opening quick report window is used. WAY 1 - Using iURL private void menuFrmMethods_menuTest_Execute(objec...