Script to Register Table with Oracle Application

DECLARE
   v_appl_short_name   VARCHAR2 (40) := 'XX';-- Application Short name
   v_tab_name          VARCHAR2 (32) := 'XX_TABLE';
                                      -- 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
   -- Unregister the custom table if it exists
   ad_dd.delete_table (p_appl_short_name      => 'XX',---- Application Short Name
                       p_tab_name             => v_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 = v_tab_name)
   LOOP
      ad_dd.register_table (p_appl_short_name      => v_appl_short_name,
                            p_tab_name             => tab_details.table_name,
                            p_tab_type             => v_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 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             => 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 = 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;