Lesson 7	Deleting from an object table
Objective	Write SQL to Delete Rows from an Object Table.

Write SQL to Delete Rows from an Object Table

To delete records from an object table, the DML statements can be simple DELETE statements. Alternatively, they can be based on a query. For example, to delete a single record from the PRODUCT_OBJ_TABLE table, use a DELETE statement as simple as this:

DELETE FROM product_obj_table
WHERE product_id = 34;

This simple DELETE statement deletes a record for PRODUCT_ID with a value of 34. To delete records based on a query, the query must be within the WHERE clause of the DELETE statement. This is an example of such a DELETE statement:

DELETE FROM product_obj_table
WHERE package_id = (SELECT REF(pot) 
  FROM product_obj_table pot
WHERE pot.product_id = 39);

Deleting multiple records from a table depends on the WHERE clause, which selects the records from the object table. Here is an example:

DELETE FROM sale_header
WHERE cust_ref = (SELECT REF(cot) 
FROM customer_obj_table cot
WHERE cot.cust_id = 26);

Delete Records

In this example, we delete the records from the SALE_HEADER table based on its CUST_REF, which is queried from the CUSTOMER_OBJ_TABLE table.
The following Slide Show offers guidelines and examples on variations in deleting records from object tables:

1) Records can be deleted from object tables by two methods. The first method uses a WHERE clause, which is not a sub-query.

2) For the second method you must specify a subquery within the WHERE clause.

Delete Table Variations

Pre-Compilation

PL/SQL objects are precompiled. All dependencies are checked prior to execution, making program execution much faster. Dependencies are not related to data. They are on other database objects, such as tables, views, synonyms, and other program structures. As such, DML that is run in a PL/SQL block stands no chance of changing a dependency that would cause a program failure. DDL, on the other hand, which supports 1) CREATE, 2) DROP, and 3) ALTER commands, as well as permission control statements GRANT and REVOKE, can change the dependencies during execution, if allowed. For example, if we have a block that first drops a table and then attempts to update that same table, it would of course fail to execute properly. That dependency cannot be checked ahead of time, though. Until the time of execution, the UPDATE would look as if it would be successful, since the table currently exists. It fails only when the block is run because of the dropped object. DDL statements are therefore not allowed directly in PL/SQL.
DML statements require an explicit COMMIT before changes become permanent. DML also supports ROLLBACK and SAVEPOINT to revert changes prior to commit when they should not be permanent

DELETE

DELETE statements remove data, following the same transaction rules as INSERTs and UPDATEs. Syntax for a DELETE statement is

DELETE FROM table_name
[WHERE where_clause | WHERE CURRENT OF cursor]

The table_name can be any table, synonym, or updatable view where the user has DELETE permissions. If no WHERE clause is provided, all records will be deleted. The where_clause can be any column in the table compared to any expression. The WHERE CURRENT OF clause works with UPDATEs and DELETEs, and says to operate against the current record from the cursor. The following example performs a DELETE from the AUTHORS table:

Available online as part of Delete.sql
SET SERVEROUTPUT ON
DECLARE
v_author AUTHORS%ROWTYPE;
BEGIN
SELECT *
INTO v_author
FROM authors
WHERE id = 54;
DELETE FROM authors
WHERE id = v_author.id;
DBMS_OUTPUT.PUT_LINE('Author '||v_author.first_name
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
/
The delete is successful:
Author Charles Moffett has been deleted

Deleting Object Table - Exercise

Click the Exercise link below to try your hand at writing INSERT and UPDATE statements.
Deleting Object Table - Exercise
In the next lesson, we will wrap up this module.