Record Based Insert And Update In Oracle

You can also use records that are based on %ROWTYPE declarations against the table to which the insert and update are made, or on an explicit record TYPE that is compatible with the structure of the table.

Record Based Insert Example:

DECLARE
my_book books%ROWTYPE;
BEGIN
my_book.isbn := '1-123456-123-1';
my_book.title := 'Fox Infotech Blogs';
my_book.summary := 'Fox Examples';
my_book.author := 'V. Kapoor';
my_book.page_count := 300;
INSERT INTO books VALUES my_book;
END;
/
Notice that you do not include parentheses around the record specifier. If you use this format:

INSERT INTO books VALUES (my_book); -- With parentheses, INVALID!

then you will get an ORA-00947: not enough values exception, since the program is expecting a separate expression for each column in the table.

Record Based Update Example:

You can also perform updates of an entire row using a record. The following example updates a row in the books table with a %ROWTYPE record. Notice that I use the keyword ROW to indicate that I am updating the entire row with a record:

UPDATE books
SET ROW = my_book
WHERE isbn = my_book.isbn;
END;
/
There are some restrictions on record-based updates:

You must update an entire row with the ROW syntax. You cannot update a subset of columns (although this may be supported in future releases). Any fields whose values are left NULL will result in a NULL value assigned to the corresponding column.

You cannot perform an update using a subquery.

And, in case you are wondering, you cannot create a table column called ROW.