1> Function REGEXP_SUBSTR
REGEXP_SUBSTR, a superset of SUBSTR function.
While SUBSTR extracts a string from a specific location in the target, REGEXP_SUBSTR extracts a string that matches a given pattern, specified with a regular expression, from anywhere in the target string.
Recently I have used this function to get a comma separated string into different rows.
like if a table coulmn has value 'Str1,Str2,Str3,Str4' then I was required to get as
Str1
Str2
Str3
Str4
Examples1 : Target string value 'Str1,Str2,Str3,Str4'
SELECT REGEXP_SUBSTR ('Str1,Str2,Str3,Str4', '[^,]+', 1, LEVEL) Val
FROM dual
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('Str1,Str2,Str3,Str4','[^,]*'))+1
Example2 : Table xx_tab has Column xx_col with target string then
WITH Tab AS (SELECT xx_col Col
FROM xx_tab)
SELECT TRIM(REGEXP_SUBSTR (Col, '[^,]+', 1, LEVEL))
FROM Tab
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(Col,'[^,]*'))+1
2> Current Oracle Application/Database version
DataBase:
SELECT *
FROM product_component_version;
OR
SELECT *
FROM v$version;
Application :
SELECT release_name
FROM fnd_product_groups;
3> Getting Patch Related Details
SELECT *
FROM ad_applied_patches ;
Note : it is not storing the Database patches info.
BUG Details
SELECT *
FROM ad_bugs;
PATCH Set Level
SELECT patch_level
,fa.application_short_name
,application_name
FROM fnd_product_installations fp
,fnd_application fa
,fnd_application_tl fat
WHERE patch_level is not null
AND fp.application_id = fa.application_id
AND fp.application_id = fat.application_id
AND fat.language = 'US'
ORDER BY fat.application_name;
4> Total Time Taken To Complete a Concurrent Request
select trunc(mod((nvl(actual_completion_date,sysdate) - nvl(actual_start_date,sysdate))*24,60),.1)||' Hr '||trunc(mod((nvl(actual_completion_date,sysdate) - actual_start_date)*24*60,60),1)||' Mins' "Total Time"
,actual_start_date
,actual_completion_date
,phase_code
,status_code
from fnd_concurrent_requests fcr
where concurrent_program_id = (select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name = :Conc_program_name
and language = 'US')
5> Getting multiple rows of data into a single row (11g)
LISTAGG function -
------------------
This function is doing grouping and concatenation of multiple rows of data into a single row per group.
Syntax : LISTAGG(<Column_name> , [<delimiter>]) WITHIN GROUP (ORDER BY <Column_name>)
>> [] - optional value
Example :
Data in table xx_receipt_tbl:
receipt_number invoice_number
-------------- --------------
A30958 A0005475
A30958 A0005730
A32249 A5013328
A32249 A5014550
A32249 A0053997
A30787 A0004163
Query:
select receipt_number
,listagg(invoice_number,'~') within group (order by invoice_number) invoice_numbers
from xx_receipt_tbl
group by receipt_number
Result:
receipt_number invocie_numbers
-------------- ---------------
A30958 A0005475~A0005730
A32249 A5013328~A5014550~A0053997
A30787 A0004163
6> Trimming/Removing leading digits/characters
>Frequently used when you have to remove leading zeros
Ex:
select trim(leading 'A' from 'ATrimTest')
from dual;
select trim(leading 0 from 0099887766)
from dual;
OR
select replace(ltrim(replace(0099007766, '0', ' ')) , ' ', '0')
from dual
select replace(ltrim(replace('ATrAATest', 'A', ' ')) , ' ', 'A')
from dual
REGEXP_SUBSTR, a superset of SUBSTR function.
While SUBSTR extracts a string from a specific location in the target, REGEXP_SUBSTR extracts a string that matches a given pattern, specified with a regular expression, from anywhere in the target string.
Recently I have used this function to get a comma separated string into different rows.
like if a table coulmn has value 'Str1,Str2,Str3,Str4' then I was required to get as
Str1
Str2
Str3
Str4
Examples1 : Target string value 'Str1,Str2,Str3,Str4'
SELECT REGEXP_SUBSTR ('Str1,Str2,Str3,Str4', '[^,]+', 1, LEVEL) Val
FROM dual
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('Str1,Str2,Str3,Str4','[^,]*'))+1
Example2 : Table xx_tab has Column xx_col with target string then
WITH Tab AS (SELECT xx_col Col
FROM xx_tab)
SELECT TRIM(REGEXP_SUBSTR (Col, '[^,]+', 1, LEVEL))
FROM Tab
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(Col,'[^,]*'))+1
2> Current Oracle Application/Database version
DataBase:
SELECT *
FROM product_component_version;
OR
SELECT *
FROM v$version;
Application :
SELECT release_name
FROM fnd_product_groups;
3> Getting Patch Related Details
SELECT *
FROM ad_applied_patches ;
Note : it is not storing the Database patches info.
BUG Details
SELECT *
FROM ad_bugs;
PATCH Set Level
SELECT patch_level
,fa.application_short_name
,application_name
FROM fnd_product_installations fp
,fnd_application fa
,fnd_application_tl fat
WHERE patch_level is not null
AND fp.application_id = fa.application_id
AND fp.application_id = fat.application_id
AND fat.language = 'US'
ORDER BY fat.application_name;
4> Total Time Taken To Complete a Concurrent Request
select trunc(mod((nvl(actual_completion_date,sysdate) - nvl(actual_start_date,sysdate))*24,60),.1)||' Hr '||trunc(mod((nvl(actual_completion_date,sysdate) - actual_start_date)*24*60,60),1)||' Mins' "Total Time"
,actual_start_date
,actual_completion_date
,phase_code
,status_code
from fnd_concurrent_requests fcr
where concurrent_program_id = (select concurrent_program_id
from fnd_concurrent_programs_tl
where user_concurrent_program_name = :Conc_program_name
and language = 'US')
5> Getting multiple rows of data into a single row (11g)
LISTAGG function -
------------------
This function is doing grouping and concatenation of multiple rows of data into a single row per group.
Syntax : LISTAGG(<Column_name> , [<delimiter>]) WITHIN GROUP (ORDER BY <Column_name>)
>> [] - optional value
Example :
Data in table xx_receipt_tbl:
receipt_number invoice_number
-------------- --------------
A30958 A0005475
A30958 A0005730
A32249 A5013328
A32249 A5014550
A32249 A0053997
A30787 A0004163
Query:
select receipt_number
,listagg(invoice_number,'~') within group (order by invoice_number) invoice_numbers
from xx_receipt_tbl
group by receipt_number
Result:
receipt_number invocie_numbers
-------------- ---------------
A30958 A0005475~A0005730
A32249 A5013328~A5014550~A0053997
A30787 A0004163
6> Trimming/Removing leading digits/characters
>Frequently used when you have to remove leading zeros
Ex:
select trim(leading 'A' from 'ATrimTest')
from dual;
select trim(leading 0 from 0099887766)
from dual;
OR
select replace(ltrim(replace(0099007766, '0', ' ')) , ' ', '0')
from dual
select replace(ltrim(replace('ATrAATest', 'A', ' ')) , ' ', 'A')
from dual
