|Oracle8i SQL Reference
Release 3 (8.1.7)
Part Number A85397-01
DROP SEQUENCE to UPDATE, 14 of 27
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.
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
ANY TABLE system privilege, you can also insert rows into any table or any view's base table.
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
Specify the schema containing the table or view. If you omit
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
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
Restrictions on the
table(or the base table of
view) contains any domain indexes marked
BYclause of the
DML_query_expression_clause, ordering is guaranteed only for the rows being inserted, and only within each extent of the table. Ordering of new rows with respect to existing rows is not guaranteed.
OPTION, then you can insert into the view only rows that satisfy the view's defining query.
OFtriggers if the view's defining query contains one of the following constructs:
INSERTstatement will fail unless the
SKIP_UNUSABLE_INDEXESparameter has been set to
with_clause to restrict the subquery in one of the following ways:
ONLYspecifies that the subquery cannot be updated.
OPTIONspecifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery.
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.
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
NULL constraint, then Oracle returns an error indicating that the constraint has been violated and rolls back the
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.
BFILEvalue until you have initialized the
BFILElocator to null or to a directory alias and filename.
The returning clause retrieves the rows affected by a DML (
DELETE) statement. You can specify this clause for tables and snapshots, and for views with a single base table.
returning_clausecan retrieve column expressions using the affected row, rowid, and
REFsto the affected row and store them in host variables or PL/SQL variables.
returning_clausestores values from expressions, rowids, and
REFsinvolving the affected rows in bind arrays.
Each item in the
expr list must be a valid expression syntax.
INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in
data_item is a host variable or PL/SQL variable that stores the retrieved
Each item in the
For each expression in the
RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the
LONGtypes with this clause.
OFtrigger has been defined.
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.
VALUES, the subquery can return zero or more rows, which are then inserted.
VALUES, the subquery must be a scalar subquery. That is, it must return exactly one row with one value.
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.
The following statement inserts a row into the
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
INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp) VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40);
The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the
INSERT INTO bonus SELECT ename, job, sal, comm FROM emp WHERE comm > 0.25 * sal OR job IN ('PRESIDENT', 'MANAGER');
The following statement inserts a row into the
accounts table owned by the user
scott on the database accessible by the database link
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
The following statement inserts a new row containing the next value of the employee sequence into the
The following example adds rows from
latest_data into partition
oct98 of the
The following example returns the values of the inserted rows into output bind variables :
bnd1 and :
INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK', 7902, SYSDATE, 1200, NULL, 20) RETURNING sal*12, job INTO :bnd1, :bnd2;
The following example returns the reference value for the inserted row into bind array :
INSERT INTO employee VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz') RETURNING REF(employee) INTO :1;
The following example copies
LONG data to a LOB column in the following existing table:
First you must create a table with a LOB.
Next, use an
SELECT statement to copy the data in all rows for the
LONG column into the newly created LOB column:
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:
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