Registering Custom Table in Apps
1. Input is your custom table name. Execute these 4 queries
2. Spool the records
3. Execute the spooled records in apps
4. Commit;
select ‘EXEC ‘||’AD_DD.REGISTER_TABLE(”XXCUST”, ”’||TABLE_NAME||”’,”T”,8,10,90);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME||”’,’||COLUMN_ID||’,”’||DATA_TYPE||”’,’||DATA_LENGTH||’,”’||NULLABLE||”’,”N”);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY(”XXCUST”,”’||INDEX_NAME||”’,”’||TABLE_NAME||”’,”’||ITYP_NAME||”’,”S”,”Y”,”Y”);’
FROM ALL_INDEXES
WHERE table_name = :TABLE_NAME
and uniqueness = ‘UNIQUE’
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY_COLUMN(”XXCUST”,”’||A.INDEX_NAME||”’,”’||A.TABLE_NAME||”’,”’||A.COLUMN_NAME||”’,’||A.COLUMN_POSITION||’);’
FROM ALL_IND_COLUMNS A, ALL_INDEXES B
WHERE A.TABLE_NAME = :TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND B.UNIQUENESS = ‘UNIQUE’
/
To delete the registered Tables, columns
select ‘EXEC ‘||’AD_DD.DELETE_TABLE(”XXCUST”, ”’||TABLE_NAME);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/
Leave a Reply
Want to join the discussion?Feel free to contribute!