Sometimes you want to look for data that is not present in your database just to do a differential action without temporary tables. So in order to select values not present in an Oracle table, you need to use the dual option or the sys.dbms_debug_vc2coll option. Let’s cover each option here!
Selecting values not present in an Oracle table with DUAL
As per Wikipedia: “The DUAL table is a special one-row, one-column table present by default in Oracle and than other database installations. In Oracle, the table has a single VARCHAR2(1) column called DUMMY that has a value of ‘X’. It is suitable for use in selecting a pseudo column such as SYSDATE or USER.”.
This option was shared in AskTom blog where you can combine multiple selects from dual with unions, creating the “virtual” table with data that doesn’t exist in your database. Once all unions execute, the conditions will run and the data that doesn’t exist will be presented. This option also let’s you define multiple columns, but your query will be very long:
Selecting values not present in an Oracle table with sys.dbms_debug_vc2coll
Oracle has this functionality that allows you to transform a comma separated list into a list of rows, just like a select from a table.
If you go for this option, your query will take less lines. In case you want to return only one column from your select, this is definetely the option to go for. It doesn’t let you return multiple columns from your “virtual” table, so you can take as the limitation here. Also it accepts up to 1000 entries, so be careful or you need to add an UNION to append more rows to your result set.
Although we have those limitations, this option is much more easier to read. It’s even possible to use the String List Wizard tool to generate the inputs for your query.
That’s it! Hopefully this quick post can help you on writing queries that select values not present in an Oracle table.
References:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532039900346897206
Leave a Reply