# Technology : ORACLE
# Author : Lokanadham Thandlam
#================================================================================
/* STEP1*/
DROP TABLE LOKA_SASI_CUSTOM_TABLE
CREATE TABLE
LOKA_SASI_CUSTOM_TABLE
(
user_id NUMBER PRIMARY KEY,
user_name VARCHAR2 (40),
job VARCHAR2(30),
sal number,
description VARCHAR2 (100)
);
/* STEP2*/
DECLARE
v_appl_short_name VARCHAR2 (40) := 'XXMS';
v_tab_name VARCHAR2 (32) := 'LOKA_SASI_CUSTOM_TABLE';
v_tab_type VARCHAR2 (50) := 'T';
v_next_extent NUMBER := 512;
v_pct_free NUMBER;
v_pct_used NUMBER;
BEGIN
--ad_dd.delete_table
(p_appl_short_name => 'XXMS', p_tab_name => v_tab_name);-- Unregister the
custom table if it exists
-- 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;
/* STEP3: Cross check the table in front end application*/
