CodeSnip: How to Create an Oracle Insert Script Using Substitution Function in UNIX?
Published: 17 Mar 2008
This article examines the creation of an insert script in Oracle by making use of the substitution function in UNIX. Deepankar begins with a short introduction followed by the various parameters of the function. The author provides a detailed analysis of the code along with relevant screenshots.
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 22584/ 32


By using the above mentioned method, a user can create a useful insert script for inserting millions of records into a table in oracle. When the user is provided with millions of records to insert into a table, they can be easily inserted through an insert script comprising of an equal number of insert statements. The use of SQL loader can make the task easy, but the goal can be achieved even without it. This method can be taken as a substitution for SQL loader. It uses a substitution utility of UNIX.


1.    Unix Operating System

2.    A list of raw data arranged in a sequential manner.

Substitution Functions in UNIX

This is a VI editor command specific to UNIX operating system. It is usually used to substitute texts in VI editor. The basic formula is described below.

parameter1,parameter2s/parameter3 (text to be altered or location involved)/parameter4(final text to be put)

parameter1: Indicates the first line from which the substitution will take place.

0/1 indicates the first line of any file in VI editor. 2 indicate the second line and so on.

Parameter2: Indicates the last line up to which the substitutions will occur. (the count of the final line)

S: Stands for the substitution.

Parameter3: Indicates the part of the text in each line to be substituted or the place of substitution (usually prior to the beginning (^) or after end ($) of the line).

Parameter4: This indicates the final text that the user wishes to put in desired places.


Listing 1

:0,$s/^/Insert into <table_name> values (‘

Listing 2

:0,$s/$/ ’);


The following figure shows the raw data that has been put into a file.

Figure 1

Here there are 500 records that the user has been given initially in a excel sheet. They have been put in to a file and the file has been opened in VI editor for processing. In order to prepare the insert script, the syntax for the script {i.e. insert into <table_name> values(‘ } has to be placed  in each line prior to the record. Listing 1 shows just how to do the same.

The following figure shows the initial part of the line being processed.

Figure 2


Now pressing the Enter key will do the substitutions shown in the following figure.

Figure 3

Now listing 2 comes into the picture as shown below.

Figure 4

Pressing the Enter key will substitute the last part of the records making the script ready to be run.

Figure 5


The insert script is now ready to be executed. Following the method, millions of records can be easily processed to prepare a useful script. The rule also applies for any DML operation.

User Comments

Title: use sql loader   
Name: culon
Date: 2008-03-18 7:44:34 PM
use oracle sqlloader , it is MUCH faster

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2021  |  Page Processed at 2021-04-13 6:07:47 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search