Oracle – How to Insert Records into Null

insertnulloracle

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)

Best Answer

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

Related Solutions

How to do the following task in Oracle

You should probably consider creating the Global Temp table once ... outside of this entire process ... creating it "on the fly" like this doesn't really make any sense.

Note also that the data is by default transaction based ... so those records you're inserting will tend to "dissappear" quite quickly (ie once you hit a commit).

  SQL> CREATE GLOBAL TEMPORARY TABLE temp(CID NUMBER);

  Table created.

  SQL> insert into temp ( select level from dual connect by level <= 10);

  10 rows created.

  SQL> Select * from temp;

         CID
  ----------
           1
           2
           3
           4
           5
           6
           7
           8
           9
          10

  10 rows selected.

  SQL> commit;

  Commit complete.

  SQL> Select * from temp;

  no rows selected

  SQL>

You don't need the PL/SQL block for this, and in fact creates more overhead than it's worth.

MS Access – How to Allow Part of a Key to Be Null and Enforce Uniqueness

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.

User-Actions taken during a given Period ( defined by the composite key of Year-Week )
User-Actions taken during an unknown Period

My initial impression is that the unknown period must needs be a different dataset. Surely if you don't know when an action has taken place you also do not yet know enough to move it from its staging environment to the table where User-Action-Period is the unique identifier. I don't know MS Access well enough to comment on the specifics of null keying in that environment, but I strongly recommend defining your data model for yourself in such a way that you know if you need to include the "equivalent-to-null" keyed records on the same table.

From your description, it seems that the problem occurs when attempting to insert multiple instances of a User-Action where the Period occurred is not known. Once again, if you do not know enough to uniquely identify the committed time of the User-Action, it is appropriate to stage the metrics you are recording about the same User-Action elsewhere until you can appropriately identify the relationship this User-Action has to the other similar User-Actions that occurred at another key-able time Period.

I see two paths forward:

If only one User-Action with "unknown" time-key is permissible

Example: The action is "pending" or "to-be-completed". The user will always be submitting data for a future / not-completed period.

Solution: Pick your favorite method of keying the time period as "equivalent-to-null" and make the key columns not-nullable. You may continue to store these datasets in the same table

Gotcha: If the user submits an action with a committed period while an unknown period exists for the same User-Action, you may run into a situation where data is committed where the period is known but other data already committed for an unknown period ( which happens to be the same one ) is left in the "unknown period" state and lost for that User-Action-Time record and the user doesn't immediately know why.

Multiple User-Actions with "unknown" time-keys are permissible

Example: Users are able to uniquely identify the same action type with a different identifier for an unknown Period.

Solution: You must define another way to key the data ( where the time the action occurs is a metric of the other multi-keyed record ). Allow the users to submit data to this separately stored dataset and merge it into your User-Action-Time period dataset using appropriate logic. It's more work, but there's no way around it if the datasets are bona-fide separate.