Create table using dynamic sql
When installing new tables for an eBS customization we have to install/re-install tables usually multiple times. Especially for re-usable products we like to install at multiple customers and sites you don't want all kinds of error messages in your installation script that the table already exists and you also do not want to create a CREATE and an UPDATE script for the same table. You simply want to be able to re-run the script without error messages and that keeps your tables up to date.
This dynamic sql script allows you to create the table and re-run it multiple times. If the columns already exist, it will not do anything and otherwise it adds the columns.
Table creation script
The initial script is for example something like this for a simple table with two columns. It assumes you create it under a custom user and grant it to APPS.CONNECT &&3/&&4
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 240
SET VERIFY OFF
SET FEEDBACK OFF
DECLARE
/****************************************************************************
-- HISTORY
-- ============ === ================ ========================================
-- 11 Jul 2015 1.0 P Koertshuis Created
****************************************************************************/
v_statement VARCHAR2(2000);
v_exists VARCHAR2(10);
v_tab_name VARCHAR2(80) := 'XXX_MY_TABLE';
v_version VARCHAR2(80) := '15.7.1 - 11-Jul-2015';
v_error VARCHAR2(240);
v_up_to_date VARCHAR2(240) := '... Table definition is up to date.';
v_release VARCHAR2(80) := '15.7.1';
v_release_date VARCHAR2(80) := '11-Jul-2015';
v_main_release VARCHAR2(80) := '15.7.1';
v_release_desc VARCHAR2(240) := 'Initial creation';
v_null VARCHAR2(1);
v_info VARCHAR2(240);
CURSOR C_Columns
IS
SELECT 1 Colnr,'ITEM_NUMBER' Column_Name,'ITEM_NUMBER VARCHAR2(100) NOT NULL' Column_Full FROM DUAL
UNION SELECT 2 Colnr,'ITEM_DESCRIPTION' Column_Name,'ITEM_DESCRIPTION VARCHAR2(100)' Column_Full FROM DUAL
ORDER BY 1
;
CURSOR C_Tab
IS
SELECT Column_Name
FROM ALL_TAB_COLUMNS
WHERE Table_Name = v_tab_name
AND Owner = upper ('&&3')
;
BEGIN
---------------------------------------------------------------------------------------------
-- Check whether table exists.
---------------------------------------------------------------------------------------------
SELECT ''
INTO v_exists
FROM ALL_TABLES
WHERE Table_Name = v_tab_name
AND Owner = upper ('&&3')
;
DBMS_OUTPUT.Put_Line ('Table ' || v_tab_name || ' exists. Updating table definition to ' || v_version);
---------------------------------------------------------------------------------------------
-- If exists, update table statement.
---------------------------------------------------------------------------------------------
FOR C IN C_Columns
LOOP
BEGIN
SELECT ''
INTO v_exists
FROM ALL_TAB_COLUMNS
WHERE Table_Name = v_tab_name
AND Upper (Column_Name) = Upper (C.Column_Name)
AND Owner = upper ('&&3')
;
EXCEPTION
WHEN No_Data_Found
THEN
BEGIN
v_statement := 'ALTER TABLE ' || v_tab_name || ' ADD ' || C.Column_Full;
DBMS_OUTPUT.Put_Line ('... Adding column ' || C.Column_Name);
v_up_to_date := NULL;
execute immediate (v_statement);
EXCEPTION
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on updating table ' || v_tab_name || ' with column ' || C.Column_Name || ': ' || v_error);
END;
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on finding column ' || C.Column_Name);
END;
END LOOP;
---------------------------------------------------------------------------------------------
-- Any additional updates
---------------------------------------------------------------------------------------------
/*
v_statement := 'UPDATE ' || v_tab_name ||
' SET MULTI_ORG_FLAG = ''N'' WHERE Org_Id IS NOT NULL AND NVL (MULTI_ORG_FLAG,''Y'') <> ''N'' ';
execute immediate (v_statement);
v_statement := 'UPDATE ' || v_tab_name ||
' SET MULTI_ORG_FLAG = ''Y'' WHERE Org_Id IS NULL AND NVL (MULTI_ORG_FLAG,''N'') <> ''Y'' ';
execute immediate (v_statement);
*/
---------------------------------------------------------------------------------------------
-- Check whether you are installing an older version ..
---------------------------------------------------------------------------------------------
FOR T IN C_Tab
LOOP
IF T.Column_Name NOT IN
('ITEM_NUMBER'
,'ITEM_DESCRIPTION')
THEN
DBMS_OUTPUT.Put_Line ('...Column ' || T.Column_Name || ' is defined in a newer version.');
v_up_to_date := '...Newer version of ' || v_tab_name || ' is already installed. Please uninstall and reinstall to make the module is working as expected.';
END IF;
END LOOP;
IF v_up_to_date IS NOT NULL
THEN
DBMS_OUTPUT.Put_Line (v_up_to_date);
END IF;
commit;
EXCEPTION
WHEN No_Data_Found THEN
BEGIN
---------------------------------------------------------------------------------------------
-- Create table.
---------------------------------------------------------------------------------------------
DBMS_OUTPUT.Put_Line ('Creating table ' || v_tab_name || ' version ' || v_version);
v_statement := 'CREATE TABLE ' || v_tab_name || Chr(10) || '(';
v_exists := ' ';
FOR C IN C_Columns
LOOP
v_statement := v_statement || v_exists || C.Column_Full;
v_exists := ' , ';
END LOOP;
v_statement := v_statement || ')';
execute immediate (v_statement);
commit;
EXCEPTION
WHEN Others tHEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on creating table ' || v_tab_name || ': ' || v_error);
END;
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on finding table ' || v_tab_name || ': ' || v_error);
END;
/
GRANT ALL ON XXX_MY_TABLE TO &&1;
PROMPT
Now while running it, it will also prompt you if you have a table in the system with more columns (a newer version exists). The blue parts are the bits you change if you change your table definition.
For example if you add a column the new script could be something like
CONNECT &&3/&&4
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 240
SET VERIFY OFF
SET FEEDBACK OFF
DECLARE
/****************************************************************************
-- HISTORY
-- ============ === ================ ========================================
-- 11 Jul 2015 1.0 P Koertshuis Created
****************************************************************************/
v_statement VARCHAR2(2000);
v_exists VARCHAR2(10);
v_tab_name VARCHAR2(80) := 'XXX_MY_TABLE';
v_version VARCHAR2(80) := '15.7.1 - 11-Jul-2015';
v_error VARCHAR2(240);
v_up_to_date VARCHAR2(240) := '... Table definition is up to date.';
v_release VARCHAR2(80) := '15.7.1';
v_release_date VARCHAR2(80) := '11-Jul-2015';
v_main_release VARCHAR2(80) := '15.7.1';
v_release_desc VARCHAR2(240) := 'Initial creation';
v_null VARCHAR2(1);
v_info VARCHAR2(240);
CURSOR C_Columns
IS
SELECT 1 Colnr,'ITEM_NUMBER' Column_Name,'ITEM_NUMBER VARCHAR2(100) NOT NULL' Column_Full FROM DUAL
UNION SELECT 2 Colnr,'ITEM_DESCRIPTION' Column_Name,'ITEM_DESCRIPTION VARCHAR2(100)' Column_Full FROM DUAL
UNION SELECT 3 Colnr, 'NEW_COLUMN' Column_Name,'NEW_COLUMN NUMBER NOT NULL' FROM DUAL
ORDER BY 1
;
CURSOR C_Tab
IS
SELECT Column_Name
FROM ALL_TAB_COLUMNS
WHERE Table_Name = v_tab_name
AND Owner = upper ('&&3')
;
BEGIN
---------------------------------------------------------------------------------------------
-- Check whether table exists.
---------------------------------------------------------------------------------------------
SELECT ''
INTO v_exists
FROM ALL_TABLES
WHERE Table_Name = v_tab_name
AND Owner = upper ('&&3')
;
DBMS_OUTPUT.Put_Line ('Table ' || v_tab_name || ' exists. Updating table definition to ' || v_version);
---------------------------------------------------------------------------------------------
-- If exists, update table statement.
---------------------------------------------------------------------------------------------
FOR C IN C_Columns
LOOP
BEGIN
SELECT ''
INTO v_exists
FROM ALL_TAB_COLUMNS
WHERE Table_Name = v_tab_name
AND Upper (Column_Name) = Upper (C.Column_Name)
AND Owner = upper ('&&3')
;
EXCEPTION
WHEN No_Data_Found
THEN
BEGIN
v_statement := 'ALTER TABLE ' || v_tab_name || ' ADD ' || C.Column_Full;
DBMS_OUTPUT.Put_Line ('... Adding column ' || C.Column_Name);
v_up_to_date := NULL;
execute immediate (v_statement);
EXCEPTION
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on updating table ' || v_tab_name || ' with column ' || C.Column_Name || ': ' || v_error);
END;
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on finding column ' || C.Column_Name);
END;
END LOOP;
---------------------------------------------------------------------------------------------
-- Any additional updates
---------------------------------------------------------------------------------------------
/*
v_statement := 'UPDATE ' || v_tab_name ||
' SET MULTI_ORG_FLAG = ''N'' WHERE Org_Id IS NOT NULL AND NVL (MULTI_ORG_FLAG,''Y'') <> ''N'' ';
execute immediate (v_statement);
v_statement := 'UPDATE ' || v_tab_name ||
' SET MULTI_ORG_FLAG = ''Y'' WHERE Org_Id IS NULL AND NVL (MULTI_ORG_FLAG,''N'') <> ''Y'' ';
execute immediate (v_statement);
*/
---------------------------------------------------------------------------------------------
-- Check whether you are installing an older version ..
---------------------------------------------------------------------------------------------
FOR T IN C_Tab
LOOP
IF T.Column_Name NOT IN
('ITEM_NUMBER'
,'ITEM_DESCRIPTION'
'NEW_COLUMN')
THEN
DBMS_OUTPUT.Put_Line ('...Column ' || T.Column_Name || ' is defined in a newer version.');
v_up_to_date := '...Newer version of ' || v_tab_name || ' is already installed. Please uninstall and reinstall to make the module is working as expected.';
END IF;
END LOOP;
IF v_up_to_date IS NOT NULL
THEN
DBMS_OUTPUT.Put_Line (v_up_to_date);
END IF;
commit;
EXCEPTION
WHEN No_Data_Found THEN
BEGIN
---------------------------------------------------------------------------------------------
-- Create table.
---------------------------------------------------------------------------------------------
DBMS_OUTPUT.Put_Line ('Creating table ' || v_tab_name || ' version ' || v_version);
v_statement := 'CREATE TABLE ' || v_tab_name || Chr(10) || '(';
v_exists := ' ';
FOR C IN C_Columns
LOOP
v_statement := v_statement || v_exists || C.Column_Full;
v_exists := ' , ';
END LOOP;
v_statement := v_statement || ')';
execute immediate (v_statement);
commit;
EXCEPTION
WHEN Others tHEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on creating table ' || v_tab_name || ': ' || v_error);
END;
WHEN Others THEN
v_error := SQLERRM;
DBMS_OUTPUT.Put_Line ('Error on finding table ' || v_tab_name || ': ' || v_error);
END;
/
GRANT ALL ON XXX_MY_TABLE TO &&1;
PROMPT
You can also create the script after you have created tables in the database already. In that case use the following code to extract the first part (the UNION) and the second part (the list of columns) and copy them into the script.
Extract unions
To extract the first part use this code. Remove the first union before you copy it into the script.SELECT 'UNION SELECT ' || rownum || ' Colnr,''' || column_name || ''' Column_Name,''' || column_Name || ' ' || data_type || decode (data_type,'DATE',null,'NUMBER', null,'(' || data_length || ')') ||
decode (nullable,'N',' NOT NULL ') || ''' Column_Full FROM DUAL'
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'XXX_MY_TABLE'
;
Extract column list
SELECT ',''' || Column_Name || ''''FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'XXX_MY_TABLE'
;
Execution
To run the script simply use@XXX_MY_TABLE.tbl apps <appspwd> customuser <custompwd>
Geen opmerkingen:
Een reactie posten
Opmerking: Alleen leden van deze blog kunnen een reactie posten.