Register a concurrent program using the below package and run the program in the source instance. Program output will generate a script that can be used to migrate the table and related components to the target instance.
Program will have two input parameters, Custom Schema Name where the table has been created and the table name.
CREATE OR REPLACE PACKAGE xx_xdfgen_pkg AUTHID CURRENT_USER
AS
REM *********************************************************
REM
REM PACKAGE SPECIFICATION: XX_XDFGEN_PKG.pls
REM
REM PURPOSE: Generate the script to create the below objects.
REM 1. Table and its constraints
REM 2. Index
REM 3. Synonym and Editionable View
REM 4. Grant
REM 5. Register table and its columns
REM
REM MODIFICATION HISTORY:
REM *********************
REM DATE VER CREATED BY DESCRIPTION
REM *********** ***** ************ *****************
REM 15-FEB-2022 1.0 Gaurav Kumar Initial
REM *********************************************************
PROCEDURE generate_table(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2);
END xx_xdfgen_pkg;
/
CREATE OR REPLACE PACKAGE BODY xx_xdfgen_pkg
AS
REM *********************************************************
REM
REM PACKAGE BODY: XX_XDFGEN_PKG.plb
REM
REM PURPOSE: Generate the script to create the below objects.
REM 1. Table and its constraints
REM 2. Index
REM 3. Synonym and Editionable View
REM 4. Grant
REM 5. Register table and its columns
REM
REM MODIFICATION HISTORY:
REM *********************
REM DATE VER CREATED BY DESCRIPTION
REM *********** ***** ************ *****************
REM 15-FEB-2022 1.0 Gaurav Kumar Initial
REM *********************************************************
PROCEDURE debug_log(
p_type NUMBER,
p_msg VARCHAR2)
IS
BEGIN
IF p_type = 1 THEN
dbms_output.put_line(p_msg);
fnd_file.put_line(fnd_file.output,p_msg);
ELSE
dbms_output.put_line(p_msg);
fnd_file.put_line(fnd_file.output,p_msg);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Unhandled exception from DEBUG_LOG procedure - '||SQLERRM);
fnd_file.put_line(fnd_file.output,'Unhandled exception from DEBUG_LOG procedure - '||SQLERRM);
END debug_log;
PROCEDURE generate_table(
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_schema_name IN VARCHAR2,
p_table_name IN VARCHAR2)
IS
l_query VARCHAR2(32767);
l_table_script VARCHAR2(32767);
CURSOR cur_indexes (p_script VARCHAR2)
IS
SELECT owner,
index_name
FROM dba_indexes
WHERE table_name = p_table_name
AND INSTR(p_script,index_name,1,1) = 0;
CURSOR cur_non_apps_grants
IS
SELECT privilege,
grantee
FROM dba_tab_privs
WHERE table_name = p_table_name
AND grantee <> 'APPS'
ORDER BY grantee;
BEGIN
debug_log(1,'/******************************************************');
debug_log(1,' * ENTERED PARAMETERS: *');
debug_log(1,' * Schema Name: '||RPAD(p_schema_name,34) ||'*');
debug_log(1,' * Table Name: '||RPAD(p_table_name,34) ||'*');
debug_log(1,' ******************************************************/');
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', TRUE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE);
debug_log(1,'');
debug_log(1,'');
debug_log(1,'-- Session Setup:');
debug_log(1,'');
debug_log(1,'WHENEVER SQLERROR CONTINUE;');
debug_log(1,'');
debug_log(1,'');
debug_log(1,'-- Table Creation Script:');
l_table_script := DBMS_METADATA.GET_DDL('TABLE',p_table_name,p_schema_name);
debug_log(1,l_table_script);
debug_log(1,'');
debug_log(1,'');
debug_log(1,'-- Index Creation Script:');
FOR i IN cur_indexes (l_table_script)
LOOP
debug_log(1,DBMS_METADATA.GET_DDL('INDEX',i.index_name,i.owner));
debug_log(1,'');
END LOOP;
debug_log(1,'');
debug_log(1,'');
debug_log(1,'-- Synonym and Editionable View Creation Script:');
debug_log(1,'');
debug_log(1,'EXEC AD_ZD_TABLE.UPGRADE ('''||p_schema_name||''', '''||p_table_name||''');');
debug_log(1,'');
debug_log(1,'');
debug_log(1,'-- Table and its columns registeration Script:');
l_query := '
DECLARE
lc_appl_short_name CONSTANT VARCHAR2(40) DEFAULT '''||p_schema_name||''';
lc_tab_name CONSTANT VARCHAR2(32) DEFAULT '''||p_table_name||
''';
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;';
debug_log(1,l_query);
debug_log(1,'/');
debug_log(1,'');
debug_log(1,'-- Grant Creation Script:');
debug_log(1,'-- Execute these Grant scripts from XX Custom Schema:');
debug_log(1,'');
debug_log(1,'grant ALL on XX.'||p_table_name||' to APPS with grant option;');
debug_log(1,'');
debug_log(1,'grant ALL on XX.'||p_table_name||'# to APPS with grant option;');
debug_log(1,'');
debug_log(1,'/');
debug_log(1,'');
debug_log(1,'-- Execute these Grant scripts from APPS Schema:');
debug_log(1,'');
FOR k IN cur_non_apps_grants
LOOP
debug_log(1,'EXEC AD_ZD.GRANT_PRIVS ('''||k.privilege||''', '''||p_table_name||''', '''||k.grantee||''');');
debug_log(1,'');
END LOOP;
debug_log(1,'/');
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
debug_log (2, 'Unhandled exception from GENERATE_TABLE procedure - '||SQLERRM);
END generate_table;
END xx_xdfgen_pkg;
/
No comments:
Post a Comment