You can use DML to insert, update, and delete records from object tables, in much the same way that you can use it on relational tables.
However, there are some subtle differences, which are listed below:
- An object table may contain a column that is associated with an object type. In such case, you must specify the object type name to insert or update the records.
- Updating a record within an object table requires the use of table name aliases.
- In the case of object tables that contain references to other objects, you must use the REF operator to insert and update the records.
There are two ways to insert and update the records within object tables:
- Specify all the values for inserting or updating the records
- Query within an INSERT or UPDATE statement to perform the DML operation
If you perform DML (Data Manipulation Language) actions inside of a Java Stored Procedure, Oracle places these restrictions on how the procedure or function can be used:
- It cannot be called from a SELECT statement or parallelized DML statement.
- It cannot be called from an INSERT, UPDATE, or DELETE statement if the statement modifies the same tables as the Stored Procedure
- It cannot be called from a SELECT, INSERT, UPDATE, or DELETE statement if it performs any transaction management
(for example, calling commit() or rollback()). By extension, it cannot execute any DDL (Data Definition Language) statements since these are automatically committed by Oracle.
- It cannot be called from a SELECT, INSERT, UPDATE, or DELETE statement if it performs system or session control functions.
Attempts to violate these restrictions are met with a run-time error.
In the next lesson, we will look at writing DML statements to insert records into object tables.