Employee supervisor Hierarchy - Oracle applications

DECLARE
   CURSOR cur_main
   IS
      SELECT DISTINCT person_id, full_name
        FROM per_all_people_f
       WHERE PERSON_ID = 1234;                                    -- person id

   CURSOR cur_sup (p_person_id NUMBER)
   IS
          SELECT DISTINCT paf.person_id,
                          ppf.full_name,
                          LEVEL,
                          ppf.email_address
            FROM per_all_people_f ppf, per_all_assignments_f PAF
           WHERE 1 = 1 AND ppf.person_id = PAF.PERSON_ID
      CONNECT BY PRIOR paf.supervisor_id = ppf.person_id
      START WITH paf.person_id = 1234                             -- person id
        ORDER BY LEVEL;
BEGIN
   DBMS_OUTPUT.enable (1000000);

   BEGIN
      --dbms_output.put_line('Approver','Level','Supervisor');
      FOR cur_main_rec IN cur_main
      LOOP
         FOR cur_sup_rec IN cur_sup (cur_main_rec.person_id)
         LOOP
            DBMS_OUTPUT.put_line (
               cur_sup_rec.full_name || ' ' || CUR_SUP_REC.PERSON_ID);
         END LOOP;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error in updation ' || SQLERRM);
   END;
END;

Oracle - Fetching all columns for a given table or finding all table which has a column name

The below query will help you in situations when you want to dynamically fetch the columns for a specific table or when  you simply need the list of all tables which have a column like you are looking for.

SELECT OWNER,
       TABLE_NAME,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       NULLABLE,
       LAST_ANALYZED,
       CHAR_USED,
       HISTOGRAM
  FROM all_tab_columns

Oracle - List all available DB links in the database

List all available DB links in the database:

SELECT * FROM dba_db_links

Oracle - Performance Tuning SQL code using DBMS_SQLTUNE

The below write up gives a step by step approach to performance tune a query using the Oracle built in Package : DBMS_SQLTUNE 

Though this wont replace a manual performance tuning effort, it can get you started in the right direction or help you spot the obvious that you had always missed.

Please note this package required special licensing requirements to be used

STEP 1: Create a Tuning Task

DECLARE
DECLARE
  my_sqltext CLOB;
  task_name VARCHAR2(30);
BEGIN
  my_sqltext := 'SELECT DISTINCT PARENT_CUST_DOC_ID FROM APPS.XX_AR_EBL_CONS_HDR_HIST WHERE EXTRACT_BATCH_ID = 234';
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
                                   user_name => 'APPS',
                                   scope => 'COMPREHENSIVE',
                                   time_limit => 60,
                                   task_name => 'sql_tuning_cons_ebill4');

END;

STEP 2: Execute the Task

begin
dbms_sqltune.execute_tuning_task ( 'sql_tuning_cons_ebill4');
end;

STEP 3: Check if the task has completed

SELECT task_name, status FROM dba_advisor_log WHERE task_name= 'sql_tuning_cons_ebill4';

STEP 4: Generate Report 

select dbms_sqltune.report_tuning_task('sql_tuning_cons_ebill4') from dual;

The suggestion below is to analyze the table and then create a index on extact_batch_id which gives a 98% benefit.

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_cons_ebill4
Tuning Task Owner                 : APPS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 07/16/2010 18:37:22
Completed at                      : 07/16/2010 18:37:22
Number of Statistic Findings      : 1
Number of Index Findings          : 1

-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : dt7baa656pgxr
SQL Text   : SELECT DISTINCT PARENT_CUST_DOC_ID FROM
             APPS.XX_AR_EBL_CONS_HDR_HIST WHERE EXTRACT_BATCH_ID = 234

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
  Table "XXFIN"."XX_AR_EBL_CONS_HDR_HIST" and its indices were not analyzed.

  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table and its indices.
    execute dbms_stats.gather_table_stats(ownname => 'XXFIN', tabname =>
            'XX_AR_EBL_CONS_HDR_HIST', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO', cascade => TRUE);

  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table and its indices
    in order to select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 98.06%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index XXFIN.IDX$$_5D6710001 on
    XXFIN.XX_AR_EBL_CONS_HDR_HIST("EXTRACT_BATCH_ID");

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3839049120

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                         |     2 |    52 |   103   (1)| 00:00:02 |
|   1 |  HASH UNIQUE       |                         |     2 |    52 |   103   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL| XX_AR_EBL_CONS_HDR_HIST |     2 |    52 |   102   (0)| 00:00:02 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("EXTRACT_BATCH_ID"=234)

2- Using New Indices
--------------------
Plan hash value: 3594741815

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |     1 |    26 |     2  (50)| 00:00:01 |
|   1 |  HASH UNIQUE                 |                         |     1 |    26 |     2  (50)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| XX_AR_EBL_CONS_HDR_HIST |     1 |    26 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX$$_5D6710001         |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EXTRACT_BATCH_ID"=234)

-------------------------------------------------------------------------------

Downloading the proper patch of Jdeveloper for OAF version

Once you have determined the version of OAF you have as explained here, Search Oralce support(Metalink)  for Note ID 416708.1 to determine the suitable patch for your Jdeveloper version.

Note that the patch itself is a Jdeveloper copy with OAF extensions.

You wont need anything else for starting your OAF development.

Determining the version of OAF (Oracle Application Framework)

In any OAF self service page click on the “About this Page” link. This should be on the bottom left of the page.

Click on the “Technology Components” tab and this should give you all the details of the OA Framework version.

Oracle Applications - API to quickly reset application password

API to quickly reset Oracle applications password

declare
x boolean;
begin
 x:=fnd_user_pkg.changepassword('user_name','welcome@123');
end;