REGEXP_REPLACE

Purpose
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_string with every occurrence of the regular expression pattern replaced with replace_string. The string returned is n the same character set as source_string. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, " Oracle Regular Expression Support".

• source_string is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_string, Oracle Database converts pattern to the datatype of source_string. For a listing of the operators you can specify in pattern, please refer to Appendix C, " Oracle Regular Expression Support".
• replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the blackslash character in replace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to " Oracle Regular Expression Support", Table C-1.
• position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_string.
• occurrence is a nonnegative integer indicating the occurrence of the replace operation:
  
  • If you specify 0, then Oracle replaces all occurrences of the match.
  • If you specify a positive integer n, then Oracle replaces the nth occurrence.
• match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:
  
  • 'i' specifies case-insensitive matching.
  • 'c' specifies case-sensitive matching.
  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.
  • 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
  If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
  If you omit match_parameter, then:
  
  • The default case sensitivity is determined by the value of the NLS_SORT parameter.
  • A period (.) does not match the newline character.
  • The source string is treated as a single line.
    
     

Examples
The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

The following example examines country_name. Oracle puts a space after each non-null character in the string.

SELECT
  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .

The following example examines the string, looking for two or more spaces. Oracle replaces each occurrence of two or more spaces with a single space.

SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

How to add System administrator responsibility from backend

Often oracle apps developers are given only limit access (only certain responsibilities) to the front end application, in such a case you can add a responsibility from back end. We will know how to do this.

 

By using the below pl/sql you can add any responsibility to a user. Here in the below example we are adding the system administrator responsibility.

Syntax:

fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);

Usage:

BEGIN

fnd_user_pkg.addresp ('OPERATIONS','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD',

'Add Sysadmin Responsibility to OPERATIONS user using pl/sql', SYSDATE, SYSDATE + 100);

COMMIT;

DBMS_OUTPUT.put_line ('Responsibility Added Successfully');

EXCEPTION

WHEN OTHERS

  THEN

DBMS_OUTPUT.put_line ( ' Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));

ROLLBACK;

END;

Some frequently used queries for oracle apps developers

 

Get all the views that refer a particular table:

SELECT o.object_name, o.status, o.last_ddl_time

  FROM SYS.dba_objects o

 WHERE o.object_type = 'VIEW'

   AND EXISTS (

              SELECT 'x'

                   FROM SYS.dba_dependencies d

           WHERE d.referenced_type = 'TABLE'

                 AND d.TYPE = 'VIEW'

                    AND d.owner = 'APPS'

             AND d.NAME = o.object_name

                 AND d.referenced_name LIKE '<TABLE_NAME>');

Get all the tables with the column name:

SELECT

  TABLE_NAME

FROM

  all_tab_columns

WHERE

  column_name = '<COLUMN_NAME>'

Get SQL help from the Data base itself:

If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base

In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.

select *

from   system.help

where  upper(topic)=upper('string')

Find from which responsibility a concurrent program can be run:

SELECT distinct

  a.user_concurrent_program_name,

  a.description,

  request_group_name,

  e.responsibility_name

FROM

  fnd_concurrent_programs_tl a,

  fnd_request_groups b,

  fnd_request_group_units c,

  fnd_responsibility d,

  fnd_responsibility_tl e

WHERE

  a.concurrent_program_id = c.request_unit_id     

and b.request_group_id = c.request_group_id

and b.request_group_id = d.request_group_id

and d.responsibility_id = e.responsibility_id

and a.application_id = b.application_id

and b.application_id = c.application_id

and d.application_id = e.application_id

and a.concurrent_program_id = :p_conc_program_id

Find all running concurrent requests with their run times:

The below query is useful for analyzing concurrent program run times and identifying their performance

select request_id,

         fcpt.user_concurrent_program_name,

         completion_text,

         actual_start_date,

         actual_completion_date,

         to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration

 from fnd_concurrent_requests fcr,

         fnd_concurrent_programs fcp,

         fnd_concurrent_programs_tl fcpt

where fcr.concurrent_program_id = fcp.concurrent_program_id

    and fcp.concurrent_program_id = fcpt.concurrent_program_id

    and fcr.actual_start_date > sysdate - 1

    order by actual_completion_date - actual_start_date desc

Search all packages for a line of code:

SELECT  *

FROM    ALL_SOURCE

WHERE   TYPE IN ('PACKAGE', 'PACKAGE BODY')

AND     TEXT LIKE '%XXXX%'

The below table shows all the data base object information:

select * from dba_objects where object_name like '%HEADER%' and object_type = 'TABLE'

Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name

select fct.user_concurrent_program_name, 
          fcp.concurrent_program_name,
          fe.execution_file_name,
          fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
        fnd_concurrent_programs fcp,
        fnd_executables fe,
        fnd_lookups fl
where
     upper(fct.user_concurrent_program_name) = upper('concurrent program') 
and     fct.concurrent_program_id = fcp.concurrent_program_id
and     fe.executable_id = fcp.executable_id
and     fl.lookup_code = fe.execution_method_code
and     fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'

List of all active workflow users and their roles

select  wu.name user_name,

        wr.name role_name

from    wf_users wu,

        wf_user_roles wur,

        wf_roles wr

where   wu.name = wur.user_name

and     wur.role_name = wr.name

and     wu.status = 'ACTIVE'

and     wr.status = 'ACTIVE'

and     wr.orig_system = 'WF_LOCAL_ROLES'

order by wu.name,

        wr.name

Concurrent requests raised in the last day

select request_id,

       decode(parent_request_id, -1, 'None') as parent_request_id,

       fcpt.user_concurrent_program_name,

       decode(  fcr.status_code,

                'A', 'Waiting',

                'B', 'Resuming',

                'C', 'Normal',

                'D', 'Cancelled',

                'E', 'Error',

                'F', 'Scheduled',

                'G', 'Warning',

                'H', 'On Hold',

                'I', 'Normal',

                'M', 'No Manager',

                'Q', 'Standby',

                'R', 'Normal',

                'S', 'Suspended',

                'T', 'Terminating',

                'U', 'Disabled',

                'W', 'Paused',

                'X', 'Terminated',

                'Z', 'Waiting') as status,       

       decode(  fcr.phase_code,

                'C', 'Completed',

                'I', 'Inactive',

                'P', 'Pending',

                'R', 'Running') as phase,

       fu.user_name,

       fr.responsibility_name,              

       completion_text,

       argument_text,

       request_date,

       requested_start_date,

       actual_start_date,

       actual_completion_date,

       fcp.concurrent_program_name,

       fe.executable_name,

       fe.execution_file_name

from   fnd_concurrent_requests fcr,

       fnd_concurrent_programs fcp,

       fnd_concurrent_programs_tl fcpt,

       fnd_executables fe,

       fnd_responsibility_vl fr,

       fnd_user fu

where  fcr.concurrent_program_id = fcp.concurrent_program_id

and    fcp.concurrent_program_id = fcpt.concurrent_program_id

and    fcp.executable_id = fe.executable_id

and    fcr.responsibility_id = fr.responsibility_id

and    fcr.requested_by = fu.user_id

and    fcr.request_date > sysdate - 1

--and    fcr.concurrent_program_id = XXXX

order by request_id desc

How to find the Trace file generated for a concurrent program

Check out the following blog post for enabling the trace file for a concurrent program.

http://oracleapps4u.blogspot.com/2011/05/enable-trace-file-for-concurrent.html

You can use the following query to locate the trace file, it takes as input the ' Concurrent Request id'

SELECT

'Request id: '||request_id , 
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'), 
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module
from
fnd_concurrent_requests req, 
v$session ses,
v$process proc, 
v$parameter dest, 
v$parameter dbnm,
fnd_concurrent_programs_vl prog, 
fnd_executables execname where req.request_id = &request 
and req.oracle_process_id=proc.spid(+) 
and proc.addr = ses.paddr(+) 
and dest.name='user_dump_dest' 
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id 
and req.program_application_id = prog.application_id 
and prog.application_id = execname.application_id 
and prog.executable_id=execname.executable_id;

The output would be 'directory'/'filename.trc'
eg: 'directory'/ora_3465_act.trc.

In the above directory, do 'ls -l' for '*3465*trc' and any latest file that you see with 3465 as a part of the file name would be the trace file.

Removing Table Lock

Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement 
SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = Table Name;

Use this session id to find SERIAL# by using following SELECT statment 
SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
WHERE NAME = Table Name)

Use ALTER SYSTEM command to KILL SESSION and this will release the lock. 
ALTER SYSTEM KILL SESSION 'SID,SERIALl#';

This script will report the SQL text of some of the locks currently being held in the database

select s.username username, 

       a.sid sid, 

       a.owner||'.'||a.object object, 

       s.lockwait, 

       t.sql_text SQL

from   v$sqltext t, 

       v$session s, 

       v$access a

where  t.address=s.sql_address 

and    t.hash_value=s.sql_hash_value 

and    s.sid = a.sid 

and    a.owner != 'SYS'

and    upper(substr(a.object,1,2)) != 'V$'

Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:

 SELECT fu.user_id,

fu.user_name,

fur.responsibility_id,
          fr.responsibility_name
FROM fnd_user fu,

fnd_user_resp_groups fur,

fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
AND user_name like ‘OPERATIONS’ --- for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.

order by user_name

Script to find Oracle API's for any module:

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AR_%API%' –- Checking for AR Related APIs
order by
a.owner, a.name

Query used for audit point of view i.e. when a profile is changed and by which user 

SELECT t.user_profile_option_name,

profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date - v.last_update_date "Change Date",

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) "Created By",

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) "Last Update By"

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id =  :p_value

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = 'B'

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = 'B')

ORDER BY user_profile_option_name;

:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)

Check Current Applied Patch

SELECT patch_name, patch_type,

maint_pack_level,

creation_date

FROM applsys.ad_applied_patches

ORDER BY creation_date DESC

Query used to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,

patch_level

FROM apps.fnd_application_vl a,

apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

This query will shows concurrent program processing time

SELECT f.request_id ,

pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)

|| ' HOURS ' ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)

|| ' MINUTES ' ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ' SECS ' time_difference,

DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio

n||']',p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV('Lang')

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;

View all types of request Application wise

SELECT fa.application_short_name,

fcpv.user_concurrent_program_name,

description,

DECODE (fcpv.execution_method_code,

'B', 'Request Set Stage Function',

'Q', 'SQL*Plus',

'H', 'Host',

'L', 'SQL*Loader',

'A', 'Spawned',

'I', 'PL/SQL Stored Procedure',

'P', 'Oracle Reports',

'S', 'Immediate',

fcpv.execution_method_code

) exe_method,

output_file_type,

program_type,

printer_name,

minimum_width,

minimum_length,

concurrent_program_name,

concurrent_program_id

FROM fnd_concurrent_programs_vl fcpv, fnd_application fa

WHERE fcpv.application_id = fa.application_id

ORDER BY description

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,

            Os_Process_ID Osid, 

            Q.Concurrent_Queue_Name Manager,

            P.process_status_code Status,

            To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At

from   Fnd_Concurrent_Processes P, 

            Fnd_Concurrent_Queues Q, 

            FND_V$Process

where  Q.Application_Id = Queue_Application_ID

  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID

  and  Spid = Os_Process_ID

  and  Process_Status_Code not in ('K','S')

order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Print Oracle Apps versions

SELECT substr(a.application_short_name, 1, 5) code,

       substr(t.application_name, 1, 50) application_name,

       p.product_version version

FROM   fnd_application a,

       fnd_application_tl t,

       fnd_product_installations p

WHERE  a.application_id = p.application_id

AND    a.application_id = t.application_id

AND    t.language = USERENV('LANG')