学步园

-- #############################################################################################
--
-- %Purpose:    Generate INSERT statements for existing data in a table
--
-- Author:      christoph.bohl@akadia.com
--
-- #############################################################################################
--
PROMPT ==========================================================================
PROMPT Generate INSERT statements for existing data in a table
PROMPT ==========================================================================
PROMPT
PROMPT You'll be prompted for the following:
PROMPT - table_name: The name of the table to generate statements (case insensitive)
PROMPT - col1:       The name of a column you want to fill with fixed data (case insensitive)
PROMPT .             - [ENTER]: do not use this functionality
PROMPT - col1_value: The value for the column above (case sensitive)
PROMPT .             - Enter String Values within two single quotes: ''example''
PROMPT .             - [ENTER]: do not use this functionality
PROMPT - col2:       The name of a column you want to fill with fixed data (case insensitive)
PROMPT .             - [ENTER]: do not use this functionality
PROMPT - col2_value: The value for the column above (case sensitive)
PROMPT .             - Enter String Values within two single quotes: ''example''
PROMPT .             - [ENTER]: do not use this functionality
PROMPT

set feedback off
set trimspool on
set linesize 255

CREATE OR REPLACE PROCEDURE genins(p_table IN varchar
                                  ,p_default_col1 VARCHAR default null
                                  ,p_default_col1_value VARCHAR default null
                                  ,p_default_col2 VARCHAR default null
                                  ,p_default_col2_value VARCHAR default null)
IS
  --
  l_column_list   VARCHAR(2000);
  l_value_list    VARCHAR(2000);
  l_query         VARCHAR(2000);
  l_cursor        INTEGER;
  ignore NUMBER;
  --
  FUNCTION get_cols(p_table VARCHAR)
  RETURN VARCHAR
  IS
    l_cols VARCHAR(2000);
    CURSOR l_col_cur(c_table VARCHAR) IS
            SELECT column_name
            FROM   user_tab_columns
            WHERE  table_name = upper(c_table)
            ORDER BY column_id;
  BEGIN
    l_cols := null;
    FOR rec IN l_col_cur(p_table)
    LOOP
      l_cols := l_cols || rec.column_name || ',';
    END LOOP;
    RETURN substr(l_cols,1,length(l_cols)-1);
  END;
  --
  FUNCTION get_query(p_table IN VARCHAR
                    ,p_default_col1 VARCHAR
                    ,p_default_col1_value VARCHAR
                    ,p_default_col2 VARCHAR
                    ,p_default_col2_value VARCHAR)
  RETURN VARCHAR
  IS
    l_query VARCHAR(2000);
      CURSOR l_query_cur(c_table VARCHAR
                        ,c_default_col1 VARCHAR
                        ,c_default_col1_value VARCHAR
                        ,c_default_col2 VARCHAR
                        ,c_default_col2_value VARCHAR) IS
        SELECT decode(column_name,c_default_col1,''''||replace(c_default_col1_value,'''','''''')||'''',
               decode(column_name,c_default_col2,''''||replace(c_default_col2_value,'''','''''')||'''',
              'decode('||column_name||',null,''null'','||
               decode(data_type
               ,'VARCHAR2','''''''''||'||column_name ||'||'''''''''
               ,'DATE'    ,'''to_date(''''''||to_char('||column_name||',''YYYY-MM-DD HH24:MI:SS'')||'''''',''''YYYY-MM-DD HH24:MI:SS'''')'''
               ,column_name
               ) || ')' )) column_query
          FROM user_tab_columns
         WHERE table_name = upper(c_table)
        ORDER BY column_id;
  BEGIN
    l_query := 'SELECT ';
    FOR rec IN l_query_cur(p_table, p_default_col1, p_default_col1_value, p_default_col2, p_default_col2_value)
    LOOP
      l_query := l_query || rec.column_query || '||'',''||';
    END LOOP;
    l_query := substr(l_query,1,length(l_query)-7);
    RETURN l_query || ' FROM ' || p_table;
  END;
  --
BEGIN
  l_column_list  := get_cols(p_table);
  l_query        := get_query(p_table,upper(p_default_col1),p_default_col1_value
                                     ,upper(p_default_col2),p_default_col2_value);
  l_cursor := dbms_sql.open_cursor;
  DBMS_SQL.PARSE(l_cursor, l_query, DBMS_SQL.native);
  DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_value_list, 2000);
  ignore := DBMS_SQL.EXECUTE(l_cursor);
  --
  LOOP
    IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
      DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_value_list);
      DBMS_OUTPUT.PUT_LINE('INSERT INTO '||p_table||' ('||l_column_list||')');
      DBMS_OUTPUT.PUT_LINE('  VALUES ('||l_value_list||');');
    ELSE
      EXIT;
    END IF;
  END LOOP;
END;
/

set serveroutput on size 1000000
exec genins('&table_name','&col1','&col1_value','&col2','&col2_value');
set serveroutput off

drop procedure genins;
set feedback on

Example

We need an INSERT script from the complete DEPT table

SQL> SELECT * FROM dept;

    DEPTNO DNAME                  LOC
       ---------- --------------              --------
                10 ACCOUNTING     NEW YORK
                20 RESEARCH          DALLAS
                30 SALES                   CHICAGO
                40 OPERATIONS      BOSTON

Now generate the INSERT script:

sqlplus scott/tiger
SQL> start genins.sql

Enter value for table_name: dept
Enter value for col1: <RETURN>
Enter value for col1_value: <RETURN>
Enter value for col1: <RETURN>
Enter value for col1_value: <RETURN>

The generated SQL code looks as follows:

INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (30,'SALES','CHICAGO');
INSERT INTO dept (DEPTNO,DNAME,LOC)
VALUES (40,'OPERATIONS','BOSTON');

Fill a new column with a Default Value

Add a new column MODDATE to the table DEPT, and initialize the new column with SYSDATE in the generated script.

SQL> alter table dept add (moddate DATE);
SQL> start genins.sql

Enter value for table_name: dept
Enter value for col1: moddate
Enter value for col1_value: SYSDATE
Enter value for col1: <RETURN>
Enter value for col1_value: <RETURN>

INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (10,'ACCOUNTING','NEW YORK',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (20,'RESEARCH','DALLAS',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (30,'SALES','CHICAGO',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (40,'OPERATIONS','BOSTON',SYSDATE);

You can see, that the new column MODDATE is initialized with SYSDATE in the generated output.

Change a Column Value to a Default Value

Suppose, you want to change the LOC column to the Default Value 'THUN'.

SQL> start genins.sql

Enter value for table_name: dept
Enter value for col1: moddate
Enter value for col1_value: SYSDATE
Enter value for col2: loc
Enter value for col2_value: ''THUN''

You can see, that the existing column LOC is initialized with 'THUN' in the generated output.

INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (10,'ACCOUNTING','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (20,'RESEARCH','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (30,'SALES','THUN',SYSDATE);
INSERT INTO dept (DEPTNO,DNAME,LOC,MODDATE)
VALUES (40,'OPERATIONS','THUN',SYSDATE);

Conclusion

The script GENINS.SQL may help you to generate INSERT scripts for existing data in a table on test system to initialize the same table on another system. You have the possibility to initialize existing columns (max 2 columns) with a Default Value.

Restriction

Due a limitation of the package DBMS_OUTPUT (Line length overflow, limit of 255 bytes per line), the generated VALUE (....) list can actually have a maximal size of 255 characters.