Tip

Create a DML script using a view

Developers and DBAs often use third-party utilities or complex packages to generate backup scripts for table data. Using DBMS_OUTPUT has buffer output limitations. Packages using UTL_FILE can write to the database server, but the generated DML script is not immediately available. Third-party utilities have cost and availability issues. Oracle export/import utilities are good but they also have compatibility issues. Now, with this view, you can have your own DML script for any table that does not contain LONG or LOB columns. The only requirement is to have SQL*Plus on the client machine.

This view was originally created by me to move data in plain DML across different databases. With minor changes it can be used to create DML script portable across databases from different vendors. This has been tested on Oracle 8.1.x and Oracle 9.x.

Creating the DML script for a table(s) or whole schema is a matter of one select statement using a view. Once you create this view in the schema(s) of your choice, you are ready to generate the table data INSERT script.

Usage: After setting the environment in SQL*Plus, spool the output to a temporary script. Execute the temporary script. This will generate the DML script that can be saved:

For Single table       : SELECT text from v_generate_data 
                         WHERE table_name = <quoted name of table>;
For List of tables     : SELECT text FROM v_generate_data 
                         WHERE table_name

    Requires Free Membership to View

IN <quoted list of tables>; For Whole schema : SELECT text FROM v_generate_data;

Add "suppress_col_name = 'Y'" to the above SELECT statements to suppress the list of columns in the INSERT statement.

Features of this utility are:

  1. No dependence on dbms_output, utl_file and buffer size.
  2. Eliminates dependence on third party tools for data generation DMLs.
  3. Generated script will have COMMIT after every 1000 records or your choice, and at the end.
  4. Insert DML can be generated for all the tables in a schema or table(s) of your choice.
  5. Column names can be suppressed from the DML script, if size of the output file is of concern.
  6. DML script with <table_name>.sql can be created when the example script with this tip is used.
  7. Automatically filters out tables containing LOB, LONG or LONG RAW columns.

Limitations:

  1. DO NOT use suppress_col_name = 'N' in WHERE clause. Only valid clause is suppress_col_name = 'Y' or no clause.
  2. Text fields containing more than two successive carriage returns may call for minor alterations to the generated script to remove additional blank lines.

Here is the script for creating the Data Generation view:

CREATE or REPLACE VIEW v_generate_data
AS
SELECT table_name,DECODE(column_id,1,'Y',decode(sign(column_id-1000),-1,'N','Y')) 
suppress_col_name,text FROM
(
SELECT a.table_name, 1 column_id,'SELECT '||qt||'INSERT INTO "'||a.table_name||'"'||qt text
  FROM (SELECT table_name,MAX(DECODE(data_type,'LOB','Y','LONG','Y',
  'LONG RAW','Y','N')) lob_table,MAX(column_id) max_column_id
          FROM user_tab_columns
      GROUP BY table_name) a,(SELECT CHR(39) qt,CHR(10) lf FROM dual) b
 WHERE a.table_name NOT IN (SELECT view_name FROM user_views)
   AND a.lob_table='N'
 UNION
SELECT a.table_name, 10+a.column_id,
       '||'||qt||DECODE(a.column_id,1,'('||LOWER(a.column_name)||',',
       c.max_column_id,LOWER(a.column_name)||')',LOWER(a.column_name)||',')||qt
  FROM user_tab_columns a,(SELECT CHR(39) qt,CHR(10) lf FROM dual) b,
       (SELECT table_name,MAX(DECODE(data_type,'BLOB','Y','CLOB','Y',
        'BFILE','Y','LONG','Y','LONG RAW','Y','N')) lob_table,MAX(column_id)
         max_column_id
          FROM user_tab_columns
      GROUP BY table_name) c
 WHERE a.table_name = c.table_name AND c.lob_table = 'N'
   AND a.table_name NOT IN (SELECT view_name FROM user_views)
 UNION
SELECT a.table_name, 1000+a.column_id, DECODE(a.column_id,1,'||'||qt||' VALUES ('||qt||'||',null)
       ||DECODE(DECODE(data_type,'NUMBER',0,'FLOAT',0,'DATE',2,1),0,'DECODE(',2,'DECODE(','qt||'
       ||'REPLACE(REPLACE(REPLACE('||LOWER(a.column_name)||',
       '||qt||qt||qt||qt||',qt||'||qt||'||CHR(39)||'||qt||'||qt),
       '||qt||';'||qt||','||'CHR(39)||'||qt||'||'
       ||qt||'||CHR(39)||'||qt||';
       '||qt||'||CHR(39)||'||qt||'||'||qt||'||CHR(39)),
        CHR(10)||CHR(10),CHR(10))')
       ||DECODE(DECODE(data_type,'NUMBER',0,'FLOAT',0,'DATE',2,1),0,LOWER
       (a.column_name)||',null,'||qt||'null'||qt||','||LOWER(a.column_name)||')',
       2,LOWER(a.column_name)||',null,'||qt||'null'||qt||','||'qt||'||LOWER
       (a.column_name)||'||qt)','||qt')
       ||DECODE(a.column_id,c.max_column_id,'||'||qt||');'||qt||
      '||DECODE(MOD(rownum,1000),0,lf||'||qt||'COMMIT;'||qt||',null)'
       ||' FROM "'||a.table_name||'",(SELECT CHR(39) qt,CHR(10) lf 
       FROM dual) b;'||lf
       ||'SELECT '||qt||'COMMIT;'||qt||' FROM dual;','||'||qt||','||qt||'||') text
  FROM user_tab_columns a,(SELECT CHR(39) qt,CHR(10) lf FROM dual) b,
       (SELECT table_name,MAX(DECODE(data_type,'LOB','Y','LONG','Y','LONG
        RAW','Y','N')) lob_table,MAX(column_id) max_column_id
          FROM user_tab_columns
      GROUP BY table_name) c
 WHERE a.table_name = c.table_name AND c.lob_table = 'N'
   AND a.table_name NOT IN (SELECT view_name FROM user_views)
)
ORDER BY table_name,column_id
/

Here is a sample output of the view. Save and execute this to generate data DML script.

SELECT 'INSERT INTO "SERVER_ERRORS"'
||'(username,'
||'terminal,'
||'os_user,'
||'program,'
||'error_message,'
||'error_ts)'
||' VALUES ('||qt||REPLACE(REPLACE(REPLACE(username,'''',qt||'||CHR(39)||
'||qt),';',CHR(39)||'||'||CHR(39)||';'||CHR(39)||'||'||CHR(39)),CHR(10)
||CHR(10),CHR(10))||qt||','||qt||REPLACE(REPLACE(REPLACE(terminal,'''',qt||
'||CHR(39)||'||qt),';',CHR(39)||'||'||CHR(39)||';'||CHR(39)||'||
'||CHR(39)),CHR(10)||CHR(10),CHR(10))||qt||','||
qt||REPLACE(REPLACE(REPLACE(os_user,'''',qt||'||CHR(39)||'||qt),';',
CHR(39)||'||'||CHR(39)||';'||CHR(39)||'||'||CHR(39)),CHR(10)||CHR(10),
CHR(10))||qt||','||
qt||REPLACE(REPLACE(REPLACE(program,'''',qt||'||CHR(39)||'||qt),';',
CHR(39)||'||'||CHR(39)||';'||CHR(39)||'||'||CHR(39)),CHR(10)||CHR(10),
CHR(10))||qt||','||
qt||REPLACE(REPLACE(REPLACE(error_message,'''',qt||'||CHR(39)||'||qt),';',
CHR(39)||'||'||CHR(39)||';'||CHR(39)||'||'||CHR(39)),CHR(10)||CHR(10),
CHR(10))||qt||','||
DECODE(error_ts,null,'null',qt||error_ts||qt)||');
'||DECODE(MOD(rownum,1000),0,lf||'COMMIT;',null) FROM server_errors,
(SELECT CHR(39) qt,CHR(10) lf FROM dual) b;
SELECT 'COMMIT;' FROM dual;

Sample output of the DML script:

INSERT INTO "SERVER_ERRORS"(username,terminal,os_user,program,error_message,error_ts)
VALUES ('APP_DEV','MYTERM','SATYA','brioqry.exe','ORA-00907: missing right parenthesis ','02-SEP-03');

INSERT INTO "SERVER_ERRORS"(username,terminal,os_user,program,error_message,error_ts) 
VALUES ('APP_DEV','TERM1','USER1','brioqry.exe','ORA-01818: 
'||CHR(39)||'HH24'||CHR(39)||' precludes use of meridian indicator','02-SEP-03');

Example SQL script for generating data for whole schema:

REM Script for generating DML script for whole schema.
REM Save this script and execute for whole schema dump. 
REM Use this only for small schemas.
SET ECHO OFF PAGES 0 HEAD OFF FEED OFF LINES 400 TRIMSPOOL ON
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
SPOOL temp.sql
SELECT text FROM v_generate_data; 
-- WHERE suppress_col_name = 'Y' 
-- to be added if you want to suppress column names in the Insert DML script.
SPOOL OFF
SPOOL data_dml.sql
@temp
SPOOL OFF
SET ECHO ON PAGES 20 HEAD ON FEED ON LINES 80 TRIMSPOOL OFF

Example script for generating data for single table.

REM Script for generating DML script for single table.
REM Save this script on your local machine and call it from 
REM the schema where the Schema Data Generation View is created.
REM Use this script for dumping single table data.
ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
CL SCR
UNDEFINE tab_name
UNDEFINE flag
DEFINE ext='.sql'
SET echo off
PROMPT
PROMPT ENTER THE TABLE NAME FOR GENERATING DML SCRIPT ...: 
SET term off
PROMPT &&tab_name
SET term on
PROMPT
PROMPT DO YOU WANT TO INCLUDE COLUMN NAMES OF THE TABLE IN THE SCRIPT ?: 
SET term off
PROMPT &&flag
SET term on
PROMPT
SET ECHO OFF PAGES 0 HEAD OFF FEED OFF LINES 400 TRIMSPOOL ON  
SET VERIFY OFF
SPOOL temp.sql
SELECT text FROM v_generate_data 
WHERE table_name = UPPER('&&tab_name') 
AND DECODE(UPPER('&&flag'),'Y',suppress_col_name,'N') = 
DECODE(UPPER('&&flag'),'Y','Y','N');
SPOOL off
SET VERIFY ON
SPOOL &&tab_name&&ext
@temp
SPOOL off
SET ECHO ON PAGES 20 HEAD ON FEED ON LINES 80 TRIMSPOOL OFF  
PROMPT Press ENTER to exit 
SET term off
PROMPT &&exit
EXIT

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.