Script To register the Custom Table In Oracle Application using AD_DD_REGISTER_TABLE

SET SERVEROUTPUT ON;

DECLARE
   l_appl_short_name                            VARCHAR2(40) := 'XXSSN';
   l_tab_name                                   VARCHAR2(32) := 'XXJG_ADDL_INFO_DFF_TBL';  -- Change the table name if you require
   l_tab_type                                   VARCHAR2(50) := 'T';
   l_next_extent                                NUMBER := 512;
   l_pct_free                                   NUMBER;
   l_pct_used                                   NUMBER;
BEGIN
   -- Unregister the custom table if it exists
   ad_dd.delete_table( p_appl_short_name => 'XXSSN', p_tab_name => l_tab_name);

   -- Register the custom table
   FOR tab_details IN (SELECT table_name
                            , tablespace_name
                            , pct_free
                            , pct_used
                            , ini_trans
                            , max_trans
                            , initial_extent
                            , next_extent
                         FROM dba_tables
                        WHERE table_name = l_tab_name)
   LOOP
      DBMS_OUTPUT.put_line(
            'Registering Table : '
         || l_tab_name);
      ad_dd.register_table(
         p_appl_short_name                         => l_appl_short_name
       , p_tab_name                                => tab_details.table_name
       , p_tab_type                                => l_tab_type
       , p_next_extent                             => NVL(tab_details.next_extent, 512)
       , p_pct_free                                => NVL(tab_details.pct_free, 10)
       , p_pct_used                                => NVL(tab_details.pct_used, 70));
   END LOOP;

   -- Register the columns of custom table
   FOR all_tab_cols IN (SELECT column_name
                             , column_id
                             , data_type
                             , data_length
                             , nullable
                          FROM dba_tab_columns
                         WHERE table_name = l_tab_name)
   LOOP
      DBMS_OUTPUT.put_line(
            'Registering Column : '
         || all_tab_cols.column_name);
      ad_dd.register_column(
         p_appl_short_name                         => l_appl_short_name
       , p_tab_name                                => l_tab_name
       , p_col_name                                => all_tab_cols.column_name
       , p_col_seq                                 => all_tab_cols.column_id
       , p_col_type                                => all_tab_cols.data_type
       , p_col_width                               => all_tab_cols.data_length
       , p_nullable                                => all_tab_cols.nullable
       , p_translate                               => 'N'
       , p_precision                               => NULL
       , p_scale                                   => NULL);
   END LOOP;

   FOR all_keys IN (SELECT constraint_name
                         , table_name
                         , constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P'
                       AND table_name = l_tab_name)
   LOOP
      ad_dd.register_primary_key(
         p_appl_short_name                         => l_appl_short_name
       , p_key_name                                => all_keys.constraint_name
       , p_tab_name                                => all_keys.table_name
       , p_description                             => 'Register primary key'
       , p_key_type                                => 'S'
       , p_audit_flag                              => 'N'
       , p_enabled_flag                            => 'Y');

      FOR all_columns IN (SELECT column_name
                               , position
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         ad_dd.register_primary_key_column(
            p_appl_short_name                         => l_appl_short_name
          , p_key_name                                => all_keys.constraint_name
          , p_tab_name                                => all_keys.table_name
          , p_col_name                                => all_columns.column_name
          , p_col_sequence                            => all_columns.position);
      END LOOP;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/

Script to Explode the DBA_DEPENDENCIES Hierachy till Nth Level

    SELECT DISTINCT referenced_name
                  , referenced_type
                  , LEVEL
                  ,    LPAD( ' ', 2 * (LEVEL - 1))
                    || name
                  , owner
                  , name
                  , TYPE
      FROM (SELECT *
              FROM dba_dependencies
             WHERE 1 = 1
               AND (referenced_name LIKE 'XX%'
                 OR referenced_name LIKE 'XX_1%')
               AND (name LIKE 'XX_%'
                 OR name LIKE 'XX_1%')
               AND name <> referenced_name)
START WITH referenced_name =<<&p_object_name>>
CONNECT BY NOCYCLE referenced_name = PRIOR name
  ORDER BY LEVEL

How to Handle the Date Parameter for Value set (FND_STANDARD_DATETIME) in Oracle R12

1. Define the Concurrent Program With Parameter as Date and attach the FND_STANDARD_DATETIME and in the PLSQL Block pass the Parameter to the Function defined below.

DECLARE
errbuf varchar2(240) := NULL;
retcode number := 0;
p_date varchar2(30) := '&1';
input_date date := FND_CONC_DATE.STRING_TO_DATE('&1');
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Writing to output file' );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Parameter 1 = ' || nvl(p_date,'NULL'));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Converted Date is '||input_date);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Writing to Logfile to test Date Parameters to
SQL*Plus');
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameter 1 = ' || nvl(p_date,'NULL'));
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Converted Date is '||input_date);
END;
/

PL/SQL Procedure

CREATE OR REPLACE PROCEDURE simple_pls (
errbuf out varchar2,
retcode out varchar2,
in_date in varchar2
)
is
input_date date;
begin
input_date := FND_CONC_DATE.STRING_TO_DATE(in_date);
fnd_file.put_line (fnd_file.output,'TEST to OUTPUT file');
fnd_file.put_line (fnd_file.output,'Original Date is '||in_date);
fnd_file.put_line (fnd_file.output,'Converted Date is '||input_date);
fnd_file.put_line (fnd_file.log,'Test to LOG File');
fnd_file.put_line (fnd_file.log,'Original Date is '||in_date);
fnd_file.put_line (fnd_file.log,'Converted Date is '||input_date);
end;
/

Script to Compile Invalid Objects in a Schema

This procedure recompile invalid objects in a given schema or all invalid objects in the database.

Parameter :
 schema     (IN) - Schema in which to recompile invalid objects  If NULL, all invalid objects in the database are recompiled.

BEGIN
UTL_RECOMP.recomp_serial(p_schema_name);
END;

DESCRIPTION:
This procedure is the main driver that recompiles invalid objects
in the database (or in a given schema) in parallel in dependency
order. It uses information in dependency$ to order recompilation
of dependents after parents.

NOTES:
The parallel recompile exploits multiple CPUs to reduce the time taken to recompile invalid objects. However, please note that recompilation writes significant amounts of data to system tables,
so the disk system may be a bottleneck and prevent significant speedups.

   PARAMETERS:
Threads    (IN) - Number of recompile threads to run in parallel If NULL, 0, or negative, RECOMP_PARALLEL computes a default degree of parallelism as the product of Oracle parameters "cpu_count" and "parallel_threads_per_cpu". On a Real Application Clusters installation, the degree of parallelism is the sum of individual settings on each node in the cluster.
Schema     (IN) - Schema in which to recompile invalid objects If NULL, all invalid objects in the database
                         are recompiled.
Flags      (IN) - Option flags supported (as described above).
   

BEGIN
UTL_RECOMP.recomp_parallel(p_schema_name);
END;

How to use comma separated string build dynamically and pass to IN clause of select statement

In some cases, we get a comma separated string as output (say from another select statement) that we would need to pass to the IN clause of a select statement.

This article explains how to achieve that using regexp_substr (DB >=10g).

For example, assume a select statement returns the following

'SMITH,ALLEN,WARD,JONES'

Now, we would need to pass this to another select statement as IN clause and get the output.

SQL> select * from emp where ename in ('SMITH,ALLEN,WARD,JONES');

no rows selected

Well, this is not our expected output. We expect the query to return 4 rows.

This can be achieved by splitting the comma separated string to individual strings and pass it to the IN clause.

Oracle provides regexp_substr function, which comes handy for this scenario.

First, we will form a query, that splits this comma separated string and gives the individual strings as rows.

 

SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  2  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES

The above query iterates through the comma separated string, searches for the comma (,) and then splits the string by treating the comma as delimiter. It returns the string as a row, whenever it hits a delimiter.

 

We can pass this query to our select statement to get the desired output.

 

SQL> select * from emp where ename in (
  2  select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  3  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );

 

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

Now, the query returns what we expected.