Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01





Go to previous page Go to beginning of chapter Go to next page

SQL Statements:



Use the INSERT statement to add rows to a table, a view's base table, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or an object view's base table.

Additional Topics


For you to insert rows into a table, the table must be in your own schema or you must have INSERT privilege on the table.

For you to insert rows into the base table of a view, the owner of the schema containing the view must have INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.

If you have the INSERT ANY TABLE system privilege, you can also insert rows into any table or any view's base table.



subquery: see SELECT and subquery.





Keywords and Parameters


Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

See Also: "Hints" and Oracle8i Performance Guide and Reference for the syntax and description of hints 



Specify the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.  

table | view | subquery 

Specify the name of the table or object table, or view or object view, or the column or columns returned by a subquery, into which rows are to be inserted. If you specify a view or object view, Oracle inserts rows into the view's base table.

If any value to be inserted is a REF to an object table, and if the object table has a primary key object identifier, then the column into which you insert the REF must be a REF column with a referential integrity or SCOPE constraint to the object table. 


If table (or the base table of view) contains one or more domain index columns, this statement executes the appropriate indextype insert routine.

Issuing an INSERT statement against a table fires any INSERT triggers defined on the table.

See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines


PARTITION (partition_name) | SUBPARTITION (subpartition_name) 

Specify the name of the partition or subpartition within table (or the base table of view) targeted for inserts.

If a row to be inserted does not map into a specified partition or subpartition, Oracle returns an error.

Restriction: This clause is not valid for object tables or object views. 


Specify a complete or partial name of a database link to a remote database where the table or view is located. You can insert rows into a remote table or view only if you are using Oracle's distributed functionality.  


If you omit dblink, Oracle assumes that the table or view is on the local database.

See Also: "Syntax for Schema Objects and Parts in SQL Statements"for information on referring to database links


Restrictions on the DML_table_expression_clause:


Use the with_clause to restrict the subquery in one of the following ways:


Use the table_collection_expression to inform Oracle that the collection value expression should be treated as a table.

See Also: "Table Collection Examples" 


Specify a subquery that selects a nested table column from table or view


Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.



Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement.


Specify a column of the table or view. In the inserted row, each column in this list is assigned a value from the values_clause or the subquery.

If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If any of these columns has a NOT NULL constraint, then Oracle returns an error indicating that the constraint has been violated and rolls back the INSERT statement.

If you omit the column list altogether, the values_clause or query must specify values for all columns in the table.

See Also: CREATE TABLE for more information on default column values 


Specify a row of values to be inserted into the table or view. You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.



The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE) statement. You can specify this clause for tables and snapshots, and for views with a single base table.


Each item in the expr list must be a valid expression syntax. 


The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list. 


Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value. 

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.



Specify a subquery that returns rows that are inserted into the table. If the subquery selects no rows, Oracle inserts no rows into the table.

The subquery can refer to any table, view, or snapshot, including the target table of the INSERT statement. The select list of this subquery must have the same number of columns as the column list of the INSERT statement. If you omit the column list, then the subquery must provide values for every column in the table.

You can use subquery in combination with the TO_LOB function to convert the values in a LONG column to LOB values in another column in the same or another table. To migrate LONGs to LOBs in a view, you must perform the migration on the base table, and then add the LOB to the view.

See Also:



  • If subquery returns (in part or totally) the equivalent of an existing materialized view, Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery.

    See Also: Oracle8i Data Warehousing Guide for more information on materialized views and query rewrite.

  • If this subquery refers to remote objects, the INSERT operation can run in parallel as long as the reference does not loop back to an object on the local database. However, if the subquery in the DML_query_expression_clause refers to any remote objects, the INSERT operation will run serially without notification.

    See Also: parallel_clause in CREATE TABLE



Inserting Values Examples

The following statement inserts a row into the dept table:


The following statement inserts a row with six columns into the emp table. One of these columns is assigned NULL and another is assigned a number in scientific notation:

INSERT INTO emp (empno, ename, job, sal, comm, deptno) 
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40); 

The following statement has the same effect as the preceding example, but uses a subquery in the DML_query_expression_clause:

INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40); 

Inserting Values with a Subquery Example

The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the bonus table:

   SELECT ename, job, sal, comm 
   FROM emp 
   WHERE comm > 0.25 * sal 

Inserting into a Remote Database Example

The following statement inserts a row into the accounts table owned by the user scott on the database accessible by the database link sales:

INSERT INTO scott.accounts@sales (acc_no, acc_name) 
   VALUES (5001, 'BOWER'); 

Assuming that the accounts table has a balance column, the newly inserted row is assigned the default value for this column (if one has been defined), because this INSERT statement does not specify a balance value.

Inserting Sequence Values Example

The following statement inserts a new row containing the next value of the employee sequence into the emp table:

   VALUES  (empseq.nextval, 'LEWIS', 'CLERK', 
            7902, SYSDATE, 1200, NULL, 20); 

Inserting into a Partition Example

The following example adds rows from latest_data into partition oct98 of the sales table:

   SELECT * FROM latest_data;

Inserting Using Bind Variables Example

The following example returns the values of the inserted rows into output bind variables :bnd1 and :bnd2:

INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK',
                        7902, SYSDATE, 1200, NULL, 20) 
   RETURNING sal*12, job INTO :bnd1, :bnd2;

Returning Values into a Bind Array Example

The following example returns the reference value for the inserted row into bind array :1:

INSERT INTO employee 
   VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz')
   RETURNING REF(employee) INTO :1;

Inserting with TO_LOB Example

The following example copies LONG data to a LOB column in the following existing table:

CREATE TABLE long_tab (long_pics LONG RAW);

First you must create a table with a LOB.

CREATE TABLE lob_tab (lob_pics BLOB);

Next, use an INSERT ... SELECT statement to copy the data in all rows for the LONG column into the newly created LOB column:

INSERT INTO lob_tab (lob_pics)
   SELECT TO_LOB(long_pics) FROM long_tab;

Once you are confident that the migration has been successful, you can drop the long_pics table. Alternatively, if the table contains other columns, you can simply drop the LONG column from the table as follows:

ALTER TABLE long_tab DROP COLUMN long_pics;

Inserting into a BFILE Example

When you INSERT or UPDATE a BFILE, you must initialize it to null or to a directory alias and filename, as shown in the next example. Assume that the emp table has a number column followed by a BFILE column:

   VALUES (1, BFILENAME ('a_dir_alias', 'a_filename'));

Go to previous page Go to beginning of chapter Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.