Using Regular Expression function (Regex) in SQL - Part IV

• Consecutive Occurrences

Let’s try to search for two consecutive occurrences of letters from a-z in the following example.

select regexp_substr('Australiaaaaaa','([a-z])\1', 1,1,'i') from dual;

Output: aa
([a-z]) character set a-z
\1 consecutive occurrence of any character in the class [a-z]
1 starting from 1st character in the string
1 First occurrence
i stands for case insensitive

Now let’s try to search for three consecutive occurrences digits from 6 to 9 in the following example.

Using Regular Expression function (Regex) in SQL - Part III

• Search and replace white spaces

select regexp_replace('Each     One   Teach      One','[[:blank:]]{2,8}',' ') from dual;

Output: Each One Teach One
In the above example we have replaced multiple white spaces (2 to 8) in the source string with single space.
Let’s now try to understand the pattern [[:blank:]]{2,8}
Now that you are familiar with the patterns you may be wondering about the missing (^$) anchors in the pattern. These have been skipped purposefully. In the source string we wanted to search for multiple spaces anywhere in the string. So we need not specify the start (^) and end ($) anchors.
Note: ^[[:blank:]]{2,8}$ would mean a pattern consisting of min of 2 spaces to a max of 8 and no other
characters and we won’t find a match for the above input. 

• Validate email

Using Regular Expression function (Regex) in SQL - Part II

• Validate a string for lower case alphabets only

select case when regexp_like('Mohit' ,'^([[:lower:]]{3,12})$') then 'Match Found' else 'No Match Found' end as output from dual;
Output: No Match Found
If we change the input to “mohit”, we get a match. In the above example we tried to check if the source string contains only lower case alphabets with a string length varying from minimum of 3 to max of 12.
The pattern is similar as above but {3,12}. {3,12} specifies at least 3 alphabets but not more than 12

• Case Sensitive Search

Using Regular Expression function (Regex) in SQL - Part I

Everyday most of us deal with multiple string functions in Sql. May it be for truncating a string, searching for a substring or locating the presence of special characters.

The regexp functions available in Oracle can help us achieve the above tasks in a simpler and faster way. I have tried to illustrate the behavior of the regexp functions with few illustrations below. The patterns can be used with any of the regular expression functions.

• Validate a string for alphabets only

select case when regexp_like('Oracle' ,'^[[:alpha:]]{6}$') then 'Match Found' else 'No Match Found' end as output from dual;

How to define a PL/SQL custom package - I

While working on an ERP implementation project, there is a very high chance that you might require to create custom pl/sql packages to meet business requirements. Through the below (and subsequent posts) I’d try and explain how to create a simple pl/sql package

Package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification ("spec") and a body. Spec is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

Date / Time related queries

Get the first day of the month

Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date column/value

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month" FROM DUAL

SQL Query - Form Personalisation

Below is the list of tables that are populated when any Forms Personalisation is done

• FND_FORM_CUSTOM_RULES
• FND_FORM_CUSTOM_SCOPES
• FND_FORM_CUSTOM_ACTIONS
• FND_FORM_CUSTOM_PARAMS
• FND_FORM_CUSTOM_PROP_VALUES
• FND_FORM_CUSTOM_PROP_LIST

Table Joins in SQL – Part II

How Many Comparisons Do You Need?

When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once.

SQL Query - Find columns of any table

The below query will fetch column details of and database table :

SELECT 

column_id,

column_name,

data_type,

data_length,

data_precision

FROM dba_tab_columns

WHERE table_name = 'PER_ALL_PEOPLE_F'  -- enter the table name

order by column_id

SQL Query - Accounting Flexfield Details

The below query will fetch the details of accounting flexfield structure:

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name

SQL Query - Form Personalisation Details

The below SQL query will fetch details of form level personalisations :

SELECT   ffcr.function_name
       , ffcr.form_name
       , fft.user_form_name
       , fat.application_name application
       , ffcr.sequence seq
       , ffcr.description

SQL Query - Database and Application Info

Below are some of the queries that can be used to get the database and Application information.

>> Get Product Version

SELECT product
     , VERSION
     , status
FROM   product_component_version

>> Get Applications Version and Patch Information

SQL Query - Responsibility list having a specific Concurrent Request

This SQL query can be used to find out which concurrent program attached to which all responsibility

select responsibility_name
from    fnd_responsibility_tl rsp_tl,
        fnd_responsibility fr,
        fnd_request_groups frg,
        fnd_request_group_units frgu,
        fnd_concurrent_programs_tl fcpt

SQL Query - Display Database Version and Server Operating System Name

The below sql query can be used to get database and other details

SELECT banner FROM v$version

SQL Query - List of users with access to a specific responsibility

The below sql query can be used to get the list of users who have access to a specific given responsibility

SELECT
  fu.user_name,
  fu.description,
  frt.responsibility_name,

SQL Queries - Payslips

Here are two queries which can be used for fetching employee salary details. The user has to replace the classifications and element names with appropriate ones. The user can also add more elements in query no.2 to display more elements

1. SQL query to display employee salary (classification wise)

Define Organizations - Part IV

Frequently Asked Questions (FAQs)

How can I find the LOV used by a segment in the Information Type I select from the ‘Others’ button?

Query up the DFF ‘Org Developer DF’ and select the context whose name matches the name of the Information type you selected from the ‘Others’ button. The Segments screen lists the fields defined for that information type. The LOV will come from the validation used by the relevant segment.