Oracle EBS 弹性域注册客户化表脚本

Oracle EBS 弹性域注册客户化表脚本

涉及到API

AD_DD.REGISTER_TABLE

AD_DD.REGISTER_COLUMN

AD_DD.REGISTER_PRIMARY_KEY

AD_DD.REGISTER_PRIMARY_KEY_COLUMN

DECLARE 

    lc_appl_short_name CONSTANT VARCHAR2(40) DEFAULT 'CUX';

   lc_tab_name       CONSTANT VARCHAR2(32) DEFAULT 'CUX_TEST_CUSTOM_T';

   lc_tab_type       CONSTANT VARCHAR2(50) DEFAULT 'T';

   lc_next_extent    CONSTANT NUMBER DEFAULT 512;

   lc_pct_free       CONSTANT NUMBER DEFAULT 10;

   lc_pct_used       CONSTANT NUMBER DEFAULT 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 = lc_tab_name

                     )

    LOOP

      -- Call the API to register table

     ad_dd.register_table

        (p_appl_short_name => lc_appl_short_name,

         p_tab_name       => table_detail.table_name,

         p_tab_type       => lc_tab_type,

        p_next_extent    => NVL(table_detail.next_extent,lc_next_extent),

         p_pct_free       => NVL(table_detail.pct_free,lc_pct_free),

         p_pct_used       => NVL(table_detail.pct_used,lc_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 = lc_tab_name

                      )

    LOOP

      -- Call the API to register column

     ad_dd.register_column

        (p_appl_short_name => lc_appl_short_name,

         p_tab_name       => lc_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 = lc_tab_name

                 )

    LOOP

      -- Call the API to register primary_key

     ad_dd.register_primary_key

         (p_appl_short_name => lc_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 => lc_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;

    DBMS_OUTPUT.PUT_LINE ('SUCCESS');

  EXCEPTION

    WHEN OTHERS THEN

     DBMS_OUTPUT.PUT_LINE (SQLERRM);

  END;

--- 刘轶鹤