home

select s.PADDR,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.PROGRAM, p.SPID
from v$session s, v$process p
where p.addr = s.paddr
and s.sid in (select b.sid
from fnd_concurrent_requests a,
     v$session b
where a.request_id in (664865)
and a.oracle_session_id = b.audsid)

sql notes from Oracle 2 Day Developer's Guide 11g Release 1 (11.1)

Searching for Patterns in Data

SELECT first_name "First", last_name "Last", job_id "Job"
  FROM employees
  WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i'); 

SELECT first_name "First", last_name "Last"
  FROM employees
  WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

Replacing a Data Pattern

SELECT first_name "First"
       ,last_name "Last"
       ,phone_number "Old Number"
       ,REGEXP_REPLACE(phone_number
       ,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})'
       ,'(\1) \2-\3') "New Number"
  FROM employees
  WHERE department_id = 90;

Returning a Substring

SELECT street_address
      , REGEXP_SUBSTR(street_address
      ,'[[:digit:]-]+', 1, 1) "Street Numbers"
  FROM locations;

Returning a Location of a Substring

SELECT street_address
       , REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position"
  FROM locations;

Returning the Number of Occurrences of a Substring

SELECT street_address
       , REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces"
  FROM locations;

Truncating Numeric Data

SELECT first_name "First"
      ,last_name "Last"
      ,TRUNC(salary/30, 0) "Daily Compensation"
  FROM employees;

Changing the Case of Character Data

SELECT UPPER(first_name) "First upper"
       ,LOWER(last_name) "Last lower"
       ,INITCAP(email) "E-Mail"
  FROM employees;

Concatenating Character Data

SELECT e.first_name || ' ' || e.last_name "Name"
       ,l.city || '
       , ' || c.country_name "Location"
  FROM employees e, departments d, locations l, countries c
  WHERE e.department_id=d.department_id AND
        d.location_id=l.location_id AND
        l.country_id=c.country_id
  ORDER BY last_name;



The results of the query appear.
Name Location
----------------------------- --------------------------------------------
Ellen Abel Oxford, United Kingdom
Sundar Ande Oxford, United Kingdom
Mozhe Atkinson South San Francisco, United States of America
...
106 rows selected

Using Conditional Functions

SELECT first_name || ' ' || last_name "Name"
       ,hire_date "Date Started", salary "Current Pay",
CASE
  WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0)
  WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0)
  WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0)
  ELSE salary END "Proposed Salary"
FROM employees;

Using the DECODE Function

SELECT first_name || ' ' || last_name "Name"
       ,job_id "Job", salary "Current Pay",
   DECODE(job_id
          ,'PU_CLERK', salary * 1.10
          ,'SH_CLERK', salary * 1.15
          ,'ST_CLERK', salary * 1.20
          ,salary
         ) "Proposed Salary"
 FROM employees;