Table Modification   «Prev 

Insert Rows using Sql Plus

  1. With SQL*Plus, connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
  2. You are now connected to the database. Next, build an INSERT statement to insert a record into the CUSTOMER_OBJ_TABLE table. The values for the record include a customer with an ID of 1000, name of Jane Foster, residing at 1234 Main Street, Orlando FL 23345. Jane last updated this record on 01/12/2000. We will insert NULL into the PHONE_LIST column, which is a varray-based column.
  3. Build an INSERT statement to insert a record into the PET_CARE_LOG_OBJ_TABLE table. The value for the record are PRODUCT_ID, which references PRODUCT_OBJ_TABLE for PRODUCT_ID of value 34' Set LOG_TEXT to The vitamins have a shelf life of 45 days and LAST_UPDATE_DATETIME to today’s date.
  4. COMMIT the inserted records
  5. SQL*Plus now commits the records of your DML statement. This is the end of the simulation. Click Exit.

Starting with Oracle9i Database Release 2, you can also perform a record-level insert, simplifying the above INSERT statement into nothing more than this:
INSERT INTO rain_forest_history
(country_code, analysis_date, size_in_acres, species_lost)
VALUES rain_forest_rec;

INSERT Statement Extension

The PL/SQL extension to the SQL INSERT statement lets you specify a record name in the values_clause of the single_table_insert instead of specifying a column list in the insert_into_clause. Effectively, this form of the INSERT statement inserts the record into the table; actually, it adds a row to the table and gives each column of the row the value of the corresponding record field.
insert_into_clause ::=



Typically a table name. For complete information, see Oracle Database SQL Language Reference.
t_alias An alias for dml_table_expression_clause.
values_clause record: Name of a record variable of type RECORD or %ROWTYPE. record must represent a row of the item explained by dml_table_expression_clause. That is, for every column of the row, the record must have a field with a compatible data type. If a column has a NOT NULL constraint, then its corresponding field cannot have a NULL value.