Table Registration API

DECLARE
   v_appl_short_name   VARCHAR2 (40) := '&appl_short_name'; --'PA';
   v_tab_name          VARCHAR2 (32) := '&table_name';--'XX_PA_BILL_RATES_EMP_CUSTOM'; -- Change the table name if you require
   v_tab_type          VARCHAR2 (50) := 'T';
   v_next_extent       NUMBER        := 512;
   v_pct_free          NUMBER;
   v_pct_used          NUMBER;
BEGIN
   -- Unregistering the custom table if it exists
   ad_dd.delete_table (p_appl_short_name=>v_appl_short_name,
                       p_tab_name => v_tab_name);

   -- Registering the custom table
   FOR i_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 = v_tab_name)
   LOOP
      ad_dd.register_table (p_appl_short_name             => v_appl_short_name,
                            p_tab_name                    => i_tab_details.table_name,
                            p_tab_type                    => v_tab_type,
                            p_next_extent                 => NVL (i_tab_details.next_extent, 512),
                            p_pct_free                    => NVL (i_tab_details.pct_free, 10),
                            p_pct_used                    => NVL (i_tab_details.pct_used, 70)
                           );
   END LOOP;

   -- Registering the columns of our custom table
   FOR i_all_tab_cols IN (SELECT column_name
                              ,column_id
                              ,data_type
                              ,data_length
                              ,nullable
                          FROM all_tab_columns
                         WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_column (p_appl_short_name             => v_appl_short_name,
                             p_tab_name                    => v_tab_name,
                             p_col_name                    => i_all_tab_cols.column_name,
                             p_col_seq                     => i_all_tab_cols.column_id,
                             p_col_type                    => i_all_tab_cols.data_type,
                             p_col_width                   => i_all_tab_cols.data_length,
                             p_nullable                    => i_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 = v_tab_name)
   LOOP
      ad_dd.register_primary_key (p_appl_short_name             => v_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=> v_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;
END;