Query to find Execution Method

The Execution_method_code field in the fnd_concurrent_programs table gives a single alphabet value that determines the Execution Method Code for that concurrent program type.

Note: In this example – taking the Executable Name of Autoinvoice Master Program i.e., RAXMTR

SELECT DECODE (execution_method_code,
               'X', 'FlexRpt',
               'F', 'FlexSql',
               'H', 'Host',
               'S', 'Immediate',
               'K', 'Java Concurrent Program',
               'J', 'Java Stored Procedure',
               'M', 'Multi Language Function',
               'P', 'Oracle Reports',
               'I', 'PL/SQL Stored Procedure',
               'E', 'Perl Concurrent Program',
               'B', 'Request Set Stage Function',
               'L', 'SQL*Loader',
               'Q', 'SQL*Plus',
               'R', 'SQL*Report',
               'Z', 'Shutdown Callback',
               'A', 'Spawned')
           Execution_Method
  FROM FND_EXECUTABLES
 WHERE executable_name = 'RAXMTR'; --- Concurrent Program Executable Name

The above query gives the Execution Method name for the Executable using the Execution_Method_code technically.

Also, these values are stored in the “CP_EXECUTION_METHOD_CODE” lookup. And can be derived using the query below.

SELECT lookup_code      Exection_method_code,
       meaning          execution_method,
       enabled_flag     Enabled_Flag
  FROM fnd_lookup_values
 WHERE LOOKUP_TYPE = 'CP_EXECUTION_METHOD_CODE' AND language = 'US'; 

MODULO

MODULO function returns the remainder value when a number (Dividend) is divided by another number (divisor).

Format: MOD (dividend, divisor).

Example1:

SELECT MOD (100, 9) FROM DUAL;

Result: 1

Example2:

SELECT MOD (-100, 9) FROM DUAL;

Result: -1

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns an numeric value of the number of months between the two input dates of the function.

Format: MONTHS_BETWEEN (datetime_expression1, datetime_expression2)

Example1:

SELECT MONTHS_BETWEEN ('10-OCT-2022', '10-OCT-2018') FROM DUAL;

Result: 48 (gives that there are 48 months in between the two dates).

Example2:

SELECT MONTHS_BETWEEN ('10-OCT-2018', '10-OCT-2022') FROM DUAL;

Result: -48 (the first date is a lower (earlier) date than the second date).

Example3:

select months_between (SYSDATE+1000,SYSDATE) FROM DUAL;

Result: 32.8709677419355 (a non-integer result).

NEXT_DAY

NEXT_DAY function returns the date of the first instance of the particular day of the week after the specified date.

Format: NEXT_DAY (input_date, day_of_the_week);

Note: For the examples below – the SYSDATE is: 10/30/2018 2:08:06 PM

Example1:

select NEXT_DAY (SYSDATE, 'WEDNESDAY') FROM DUAL;

Result: 10/31/2018 2:08:06 PM

Example2: (takes lower case of the day)

select NEXT_DAY (SYSDATE, 'monday') FROM DUAL;

Result: 11/5/2018 2:08:06 PM

Example3: (the function allows a little typo in the name of the day).

select NEXT_DAY (SYSDATE, 'monnnnnnday') FROM DUAL;

Result: 11/5/2018 2:08:06 PM

Example4: (if the day is not a day of the week at all – then it throws an error).

SELECT NEXT_DAY (SYSDATE, 'testday') FROM DUAL;

Result: Error.

LAST_DAY

LAST_DAY function returns the last day of the month in which the input date falls in.

The syntax of this function is: LAST_DAY(datetime-expression).

Example:

The Sysdate in this example is: 10/29/2018 8:24:07 PM

SELECT LAST_DAY (SYSDATE) FROM DUAL;

Result: 10/31/2018 8:24:07 PM

Example 2:

The Sysdate in this example is: 10/29/2018 8:24:46 PM

SELECT LAST_DAY (SYSDATE-300) FROM DUAL;

Result: 1/31/2018 8:24:46 PM

ADD_MONTHS

Add_months function returns a DATETIME value – that adds ‘n‘ number of months from the input date provided.

The syntax of ADD_MONTHS function is:  ADD_MONTHS (input_date, n) where:

• input_Date – is the date from which the function is to be applied on.
• n – n is the integer value of number of months to add from the input date

SELECT ADD_MONTHS (SYSDATE, 10) FROM DUAL;

(Sysdate is: 10/29/2018 7:52:53 PM)

Logic: Adds 10 months to the sysdate

Result: 8/29/2019 7:52:53 PM

SELECT ADD_MONTHS (SYSDATE, -10) FROM DUAL;

(Sysdate is: 10/29/2018 7:53:46 PM)

Logic: Adds 10 months to the sysdate

Result: 12/29/2017  7:53:46 PM

INITCAP

INITCAP function returns a text expression, wherein it return the first letter of every  word in the string in UPPERCASE and all other letters in lowercase.

Example:

SELECT INITCAP ('oracle world is awesome') FROM DUAL;

Result: Oracle World Is Awesome

Example 2:

SELECT INITCAP (SUBSTR ('oracle world is awesome', 8)) FROM DUAL;

Result: World Is Awesome

INSTR

INSTR Function:

To understand INSTR function, it has 4 components in that function:

INSTR (String, sub-string, starting character position, occurrence of the character);

• String: The string that is being used to search in.
• Sub-String: The character or the group of characters that is being searched for.
• Starting character position: The starting position from where the string has to be searched from
• Occurence: The nth occurence of the Character or the group of characters.

For Example:

select instr ('test_element_is_titanium','t',1,3) from dual;

Here, in the string, we are trying to find the position of the 3rd occurence of the alphabet ‘t’ starting from the first position. Result: 12

Example 2:

select instr ('test_element_is_titanium','t',5,3) from dual;

Here, we are trying to find the position of the 3rd occurence of the alphabet ‘t’ starting from the 5th position. Result: 19

Example 3:

It is case sensitive.

select instr ('Test_Element_is_Titanium','t',1,1) from dual;

Here, we are trying to find the position of 1st occurence of the alphabet – lower case ‘t’ – starting from the 1st position. Result: 4

Example 4:

select instr ('test_element_is_titanium','t',1,8) from dual;

If the INSTR function is not able to find the occurence, then it results in zero (0). Since there is not 8th occurence of alphabet ‘t’ Result: 0

Real Life Usage: 

In real life, there may be a situation where we may have a string from which we are asked to retrieve the Customer Name (bolded alphabets in the string below).

String is: ‘12345678;Test_Customer_1;1234 Main Street;New York;94949;US’

The logic will be to identify the characters between the occurrence of the first semi-colon and the second semi-colon using INSTR and then using those positions in the substr function.

SELECT SUBSTR (
           '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           + 1,
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 2)
           - INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           - 1)
  FROM DUAL;

Run the query above – Which gives the result as – Test_Customer_1 

SUBSTR

SUBSTR (Sub String) Functions are used to retrieve a portion of a given string. The Return value is a string of the same data type as the string.

General Usage: 

The format of SUBSTR function is: SUBSTR (String, Starting position, length).

• String – The string from which we will be retrieving the result.
• Starting position – Position from which we want to retrieve the result.
• Length – Optional – The length of the string from the Starting Position.

Consider the below example:

select substr ('This is a test sentence', 16, 8) from dual;

Here:

• String is – This is a test sentence
• Starting Position – 16
• Length – 8.

Thus, what the sql does is – it calculates the 16th position from the start, and retrieves the next 8 characters in the string. Which gives the result as “sentence”.

The Starting position can be negative too.

select substr ('This is a test sentence', -16, 8) from dual;

In the above scenario, the sql engine sees that the value is negative, and retrieves the 8 characters backward starting from the 16 position in the string. Which gives the result as “ a test ” (that may include the spaces).

Real World Use: In the real world, there may be a requirement where we have a string and we have to derive particular part of a string. In these scenarios, we can use SUBSTR. Consider a real world scenario, like below, a string with Customer Number, Name, Address, city, Zip and Country and delimited by a semi-colon. The string is:

• ‘12345678;Test_Customer_1;1234 Main Street;New York;94949;US‘
• ‘5678;Test_Customer_2;367 Parallel Street;California;38383;US‘

And the requirement is to derive the Customer Name.

The logic is to get the value between the First and Second semi-colons.

Using the Instr function – we get the position of the first semi-colon and the second semi-colon, and using SUBSTR, derive the values between the first and second semi-colon – which gives the Customer name. The below query is thus used:

SELECT SUBSTR (
           '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           + 1,
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 2)
           - INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           - 1)
  FROM DUAL;

Run the query above – Which gives the result as – Test_Customer_1 (It may look a little tricky, but its simple when looked into).

DML Functions

DML (Data Manipulation Languages) Functions are used in day to day Oracle coding which would return a value when a corresponding action is triggered. They Initiate Action and Return a Value. Most of these DMS functions are standard text and calculation functions.

Some (not all of them are listed) of the very common DML functions that we use in Day to Day coding activities are below:

• SUBSTR
• INSTR