donderdag 12 november 2015

Create table using dynamic sql

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.