Let's Play with SQL

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