How to query an Oracle table that might be missing some columns?

August 2, 2013 6:02 PM

We have a very large series of SQL scripts that extract data from hundreds of existing Oracle tables for transformation and loading in to a different database. This set of scripts is being end-of-lifed due to its sheer horribleness and unmaintainability - but it DOES work for our current generation of systems. Suddenly, we have a need to run it against a very old copy of the original Oracle tables, where many of the tables don't have all the columns that the SQL scripts expect. Option 1: to adjust the SQL scripts will take a very long time - more than we have. Option 2: another proposal is to retrofit the expected columns into the old database and fill them with null values - but this will also take a long time, and also upset the QA status of the old database, which is currently in ok shape. Re-qualifying the database alone would take months in our industry, due to the paperwork and the nature of the business. Is there a way to tell Oracle (ideally via some global flag) to ignore the fact that certain columns are missing in the source tables, and to return null values in their place? That would work fine for us, because the target database is designed to cope with them.
posted by blue_wardrobe to Computers & Internet (5 answers total)

Would you be able to write a PL_SQL script that would create a view on the table? I'm assuming you have a standard table definition that some of the older tables are missing. So the PL_SQL script would set the view column to reference the table if the column existed (by querying the Oracle table all_tab_cols) and would set the view column name to null if the table column name did not exist.
posted by DetriusXii at 6:29 PM on August 2, 2013

Yes, views. It sounds like you know what tables you're dealing with, and what columns are missing, you just don't want to edit those complicated scripts. So, for table t_x you define v_x which defines all the missing columns to be null, or a suitable default value. You do have a naming problem to sort out - the scripts obviously refer to t_x, not v_x, so maybe you want to rename t_x to be t_x_old and define the view using the old name? Maybe somebody with more recent Oracle experience will have a more clever solution for the naming problems.
posted by mr vino at 7:21 PM on August 2, 2013

seconding views. And if you can, do it on a copy of the database on a spare server somewhere so you're free to mess with it as much as you want while leaving the original intact. That way you can rename tables and put the named views in their place
posted by DreamerFi at 2:20 AM on August 3, 2013

The solution to the naming issue is synonyms. The original tables are owned by user z and named table t_x and the temporary view that adds the missing columns is named v_x. You run the transformations as user a. In user a's schema you create a synonym of t_x that actually connects to z.v_x instead. The scripts still use all the same references by they actually connect to the views instead of the tables because the user running the scripts has a synonym of the tablename pointing to the view instead.
posted by Lame_username at 2:21 AM on August 3, 2013

If views and synonyms aren't also considered to upset the QA status, then this would be the likely solution, but unfortunately it still involves a lot of work - you have to manually create the views for each of the hundreds of tables, and unless the column differences are consistent, this is going to take a long time, as you realize.

Here's a couple more options, trying to think outside the box:

1: Take the existing old schema, and expdp / impdp into a new schema name. Depending on how anal your QA team is, this sould preserve the sanctity of the existing database. You could then exercise your second option to add the necessary null columns to the 'dummy' schema, and you would have to modify your sql scripts to reference the new schema name, though this might be a simple find/replace. If a dummy schema on the same instance is problematic, see if you're able to clone the whole instance somewhere temporarily. This would also save you from having to find/replace schema references in the SQL script.

2: If your infrastructure and security standards allow for it, you could create a dblink between the new and old databases, and use that link to help write some creative pl/sql that would create views based on the differences in the metadata of each column, ala DetriusXii's suggestion. If you can't use dblinks, you could get really creative and use pl/sql on the new database to generate a new set of pl/sql on the old database that would create the views. It's ugly, and not quick depending on the efficiency of your pl/sql experts, but quicker than manually coding hundreds of views. (If you are not the DBA, be prepared for pushback on this idea from the DBA you ask. Many DBAs, including myself, don't like dblinks because of the security implications they open up, particularly in 10g.)

Short answer to your actual question, no, there is no flag to set that would return a null value in place of a column in your query that doesn't exist in the underlying table.
posted by SquidLips at 10:27 AM on August 3, 2013