Home > Database, Oracle > One of Best Way to Archive Table with “Returning”

One of Best Way to Archive Table with “Returning”

The RETURNING clause is used to return specific columns from rows manipulated by DML statements.  When DML statements manipulate multiple rows, the data returned can be loaded into a collection using a bulk operation.  The returning_bulk_collect.sql script provides an example of this functionality.

returning_bulk_collect.sql

SET SERVEROUTPUT ON
DECLARE
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_tab  t_object_id_tab;
BEGIN
DELETE FROM bulk_collect_test
RETURNING object_id BULK COLLECT INTO l_tab;

  DBMS_OUTPUT.put_line(‘Deleted IDs : ‘ || l_tab.count || ‘ rows’);

  ROLLBACK;
END;
/

The returning_bulk_collect.sql script defines a collection, deletes the data from the test table and returns the OBJECT_IDs of the deleted rows.  To preserve the data, the script performs a rollback, allowing for multiple runs.  The output from this script shows that the collection is populated as expected.

SQL> @returning_bulk_collect.sql
Deleted IDs : 61202 rows

The FORALL command can be used in conjunction with a DML statement with a RETURNING clause.  In this situation, both the DML and the return data are bulk operations.  The forall_returning_bulk_collect.sql script provides an example of this functionality.

forall_returning_bulk_collect.sql

SET SERVEROUTPUT ON
DECLARE
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;

  l_in_tab   t_object_id_tab;
l_out_tab  t_object_id_tab;
BEGIN
— Populate collection use in forall.
SELECT object_id
BULK COLLECT INTO l_in_tab
FROM   bulk_collect_test
WHERE  rownum < 101; 

  FORALL i IN l_in_tab.first .. l_in_tab.last
DELETE FROM bulk_collect_test
WHERE  object_id = l_in_tab(i)
RETURNING object_id BULK COLLECT INTO l_out_tab;

  DBMS_OUTPUT.put_line(‘Starting IDs : ‘ || l_in_tab.count || ‘ rows’);
DBMS_OUTPUT.put_line(‘Deleted IDs  : ‘ || l_out_tab.count || ‘ rows’);

  ROLLBACK;
END;
/

The forall_returning_bulk_collect.sql script defines two collections, one to support the FORALL statement and one to support the RETURNING clause.  The first collection is populated to provide IDs of rows to be deleted. Next the bulk delete is performed and the data returned into the second collection.  Then the size of both collections is reported.  The output from this script is displayed below.

SQL> @forall_returning_bulk_collect.sql
Starting IDs : 100 rows
Deleted IDs  : 100 rows

PL/SQL procedure successfully completed.

Advertisements
Categories: Database, Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: