1

I'm looking for a way to insert records into a 'black hole'; i.e. execute a query for a certain table successfully, but make the records disappear. Something like INSERT INTO NULL, if you will. MySQL has the BLACKHOLE engine, can Oracle do something similar?

The only thing I could come up with was:

  • creating a view with SELECT NULL FROM DUAL
  • creating a trigger with INSTEAD OF INSERT and BEGIN NULL; END;

Is this okay?

Obviously, the optimal way is not to execute the insert statement, but I have no control over this - I have to fix this in the DB.

What's the best way to handle this? (the solution with the least impact on performance)

4
  • The instead of trigger is the way to go. Don't try and insert into dual. It breaks things terribly on some versions of Oracle
    – Philᵀᴹ
    Commented May 14, 2014 at 11:53
  • If you do this for testing purposes, you could also wrap the inserts into a transaction, then rollback at the end. I think it's still faster than the trigger.
    – ddaniel
    Commented May 14, 2014 at 12:00
  • I too think the view is the way to go. But I'd also be reluctant to base that view on the dual table. Just create a dummy table instead.
    – user1822
    Commented May 14, 2014 at 12:13
  • Maybe a temporary table on Oracle (which works much differently than on eg SQL Server) would work for you? The performance of this is likely to be much better than the trigger solution which will have a lot of row-by-row overhead. If you need to discard millions of rows, the temporary table approach will be much faster. Commented May 14, 2014 at 12:22

1 Answer 1

3

One way you could do something like this on Oracle is to use a temporary table with ON COMMIT DELETE ROWS. The rows will "disappear" at the end of the transaction. If you're using a host environment such as JDBC with autocommit, this will make it look like what you describe. If performance is important, this approach will be much faster than the "discarding trigger" approach.

Syntax:

CREATE GLOBAL TEMPORARY TABLE my_table (
  column1 INTEGER,
  column2 VARCHAR2(100),
  ...
)
ON COMMIT DELETE ROWS;

Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN11633

4
  • Unfortunately, the table needs to be persistent... Commented May 14, 2014 at 12:31
  • Oracle temporary tables are persistent! It's just the contents that are temporary. Commented May 14, 2014 at 12:32
  • If the structures are persistent, this is most definitely a very elegant solution :) What happens if you don't commit? Commented May 15, 2014 at 7:30
  • The records will remain visible until commit or rollback -- but only to your session, not to any others. Records in temporary tables are never visible to other sessions -- even with on commit preserve rows (then the rows "disappear" on logout). Like I said, this may or may not work for you, but would be the way I would try to solve the problem, if possible. NB records are stored (ie: temporarily) in the default temporary tablespace of the user, or you can explicitly assign a tablespace at table creation time. Commented May 15, 2014 at 7:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.