How can I insert records 'into null' in Oracle?

Question

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)

• Philᵀᴹ: 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
• ddaniel: 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.
• user1822: 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.
• Colin 't Hart: 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.

Answer 1

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

• Vincent Van Den Berghe: Unfortunately, the table needs to be persistent...
• Colin 't Hart: Oracle temporary tables are persistent! It's just the contents that are temporary.
• Vincent Van Den Berghe: If the structures are persistent, this is most definitely a very elegant solution :) What happens if you don't commit?
• Colin 't Hart: 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.