Chitika Add

Wednesday, July 23

Register Table, Column, Primary Key, Primary key column

DECLARE
  V_APPL_SHORT_NAME varchar2 (40) := 'PER';
  v_tab_name        VARCHAR2 (32) := 'XX_ORACLE-APPSS_BLOGSPOT_COM'; -- 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             => 'PER', 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;

No comments: