Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jazz.

Asked: March 07, 2018 - 5:11 pm UTC

Last updated: March 14, 2018 - 6:25 am UTC

Version: Oracle 11g R2

Viewed 1000+ times

You Asked

I am trying converting SQL Server T-SQL queries to Oracle based queries.

In SQL Server there is an ability to use the OUTPUT Clause within a query. See example below.

BEGIN TRANSACTION

DELETE TableA
OUTPUT "DELETED".*
INTO "TESTARCHIVE"."dbo"."TableA"
FROM TableA TA
WHERE TA.Id > 2

COMMIT TRANSACTION

This query deletes from 1 database and the rows that are deleted are pushed into another database ("TESTARCHIVE").
In Oracle's case, it would probably be equivalent to another schema let say.

I know that Oracle has no OUTPUT clause syntax, but instead has "RETURNING INTO" functionality. However, it doesn't appear to work in similar fashion from my research. You cannot bind to a full row and be setting the context to another schema's variable's don't work.

I looked into the Bulk Collect approach, but that seems like I would have to build a collection and then loop through the results and insert into another database. Not the greatest, but maybe I can create a routine
which would accept a table name and a collection of rowids and use it generically?

DECLARE
TYPE tab_rowid IS TABLE OF ROWID;
l_r tab_rowid;
BEGIN
DELETE TableA WHERE id > 2
RETURNING ROWID BULK COLLECT INTO l_r;
FOR i IN 1 .. l_r.count LOOP
-- Insert into another schema to use a dblink ?
END LOOP;
END;

(I don't think code would cut it!)


Yes, this can be done via "triggers" and possibly a dblink, but I don't want to add triggers to all the tables in which this particular technique was utilized.

Is there any feature? A Package which can do something similar in Oracle. It is convenient having rows deleted
from one database and automatically inserted into another.

Thank you for help.

and Connor said...

Would something like this suffice ?

SQL> create table t1 ( x int, y int, z int );

Table created.

SQL>
SQL> create table scott.t1 as select * from t1;

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rownum, rownum
  3  from dual connect by level <= 100;

100 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from t1;

  COUNT(*)
----------
       100

1 row selected.

SQL>
SQL> declare
  2    type array is table of t1%rowtype;
  3    l_rows array;
  4  begin
  5    delete from t1
  6    where x < 50
  7    returning x,y,z
  8    bulk collect into l_rows;
  9
 10    forall i in 1 .. l_rows.count
 11      insert into scott.t1 values l_rows(i);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from t1;

  COUNT(*)
----------
        51

1 row selected.

SQL> select count(*) from scott.t1;

  COUNT(*)
----------
        49

1 row selected.

SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Intersting approach

jazz, March 12, 2018 - 4:49 am UTC

Ok, so using bulk collect and "forall" after the fact would allow for me to mimic the OUTPUT clause approach.

I am wondering thou, if I will be deleting 1 million rows, the memory consumption could become a tad high. I will test this approach out. I could break out larger deletes into little ones I guess.

Thank you much for this approach and timely answer.
Connor McDonald
March 13, 2018 - 2:11 am UTC

For a millions you could chunk this up a little, eg

SQL> create table t1 ( x int, y int, z int );

Table created.

SQL> create table t2 as select * from t1;

Table created.

SQL>
SQL> insert into t1
  2  select rownum, rownum, rownum
  3  from dual connect by level <= 100;

100 rows created.

SQL>
SQL>
SQL> declare
  2    cursor c is select rowid rid, t1.* from t1;
  3    type array is table of c%rowtype index by pls_integer;
  4    l_rows array;
  5    l_idx int;
  6  begin
  7    open c;
  8    loop
  9      fetch c bulk collect into l_rows limit 1000;
 10      exit when l_rows.count = 0;
 11
 12      forall i in 1 .. l_rows.count
 13         insert into t2 values (
 14            l_rows(i).x,
 15            l_rows(i).y,
 16            l_rows(i).z );
 17      forall i in 1 .. l_rows.count
 18        delete from t1
 19        where rowid = l_rows(i).rid;
 20    end loop;
 21  end;
 22  /

PL/SQL procedure successfully completed.


Nifty Feature

jazz, March 14, 2018 - 2:50 am UTC

That is a nice/clever feature being able to use the limit to 'chunk' away at the rows. Brilliant!

In the example, it's using T1 and T2, but in real life, i have many tables in which I would need to do this on and it must all be within a transaction (all tables must delete and insert or none). Do you feel it would be ok to slot them all in between the begin and end as you have shown?

If I create a script with multiple declares and cursors I could not control the transactional aspect of the queries.

In SQL Server I just have to use an approach like:

Begin Transaction
# all my queries with the output clause here!
Commit Transaction

Thank you again for these great answers!
Connor McDonald
March 14, 2018 - 6:25 am UTC

You'll notice there was no "commit" statement in my code.

Commit/rollback are unaffected/unrelated by the chunking etc.

You simply do all of your work, for as many tables as you like, and then at the end, just do "commit"

Thanks

jazz, March 14, 2018 - 6:12 pm UTC

Ahh, got it!

Thank you again!


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library