Sunday, February 2, 2014

Register Custom Table Using Script In Oracle Applications

In my last post I talked about how to register table in Oracle Application. The steps involved are too much and will take lot of time if we have to create multiple tables or a single table is having too many columns.
To Save time you can use below script which will do the job for you. I am considering the your table is already created.
DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'C_APPS';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'C_FND_INTERFACES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN   -- Start Register Custom Table
   -- Get the table details in cursor
   FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                          FROM dba_tables
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name        => table_detail.table_name,
                            p_tab_type        => vc_tab_type,
                            p_next_extent     => NVL(table_detail.next_extent, vc_next_extent),
                            p_pct_free        => NVL(table_detail.pct_free, vc_pct_free),
                            p_pct_used        => NVL(table_detail.pct_used, vc_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Start Register Columns
   -- Get the column details of the table in cursor
   FOR table_columns IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => table_columns.column_name,
                             p_col_seq              => table_columns.column_id,
                             p_col_type             => table_columns.data_type,
                             p_col_width            => table_columns.data_length,
                             p_nullable             => table_columns.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns
   -- Start Register Primary Key
   -- Get the primary key detail of the table in cursor
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_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'
                                 );
      -- Start Register Primary Key Column
      -- Get the primary key column detial in cursor
      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
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_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 Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
END;
Let’s go and validate in application.
Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.
Image 001 Image 002
See Also : Registering Custom Table Using Script

3 comments :

  1. p_audit_flag => 'N' should be 'Y' in case you would like to enable audit on this table!

    ReplyDelete
    Replies
    1. declare
      i integer;
      begin
      ad_dd.update_primary_key('SCHEMA_NAME','PRIMARY_KEY_NAME','TABLE_NAME','Updating Audit Flag','S','Y','Y');
      commit;
      end;

      Delete
  2. At about 23,000 retailers throughout the state, together with comfort stores and gasoline stations, ought to purchase|you should buy} scratchers 카지노 and fill out tickets for drawings, such as MegaMillions and PowerBall. The survey protocol for the Baseline General Population Survey and the Baseline Online Panel Survey of Massachusetts was reviewed and permitted by NORC’s Institutional Review Board and by the University of Massachusetts Amherst Institutional Review Board. In the meantime, please save the date of May 23-25, 2023, and keep tuned to our web site,social media, and join our mailing record for the newest updates. Fees for companies required hereunder shall be the identical as supplied for sheriffs and clerks for like and related companies in other instances and issues.

    ReplyDelete

SeachBox