Dynamic SQL

One of my pet hates is the inappropriate use of dynamic SQL within ETL processes, for example building a command as VARCHAR2 string then using it in an "execute immediate" statement. Putting commands into strings whether hard-coded ones within the package or procedure, retrieved from some 'code' table in the database or even built on-the-fly can make support, enhancement and documentation tasks more complex - string content don't show up in the "all_dependencies" view and that can make finding the code that manipulates data overly complicated.

I would be a hypocrite to say "never use dynamic SQL". It does have its uses. A few years back I had to write my own materialized view refresh code for the special case of complete refresh of a single partition of a materialized view, or to be more precise the refresh of over 50 different partitioned materialized views - the pragmatic approach was to write a single procedure to use dynamic SQL to do the DML and to handle the refresh of a single partition.

Recently, I came across an even more compelling reason to use dynamic SQL - one of my customers needed to load several thousand flat files per day into their data warehouse. Each file had the same structure, we just needed to load them as fast as possible to a staging table and to persist the name of the original source file with the loaded data for data lineage purposes. Using Oracle external tables we have two viable options - rename the source files one-by-one to be the external table's location name (there is also a similar approach of concatenating the files together to build a 'massive' external table) or to alter the external table location to match each of the incoming file names.

I prefer the ALTER TABLE approach as I don't need to manipulate files in the OS and I can do all that I need in the database (albeit with a little touch of database Java to build a table of files to process). In pseudo code terms I:

insert a directory listing into a temporary table (using a database Java procedure)

for each filename that matches the filename format

    Alter the external table location to be the be the new filename

    insert the content of the external file into the staging table (append)

Loop to the next filename

Of course there are some other bits and pieces going on to log the activity and to move (or rename) files after processing so that they don't get processed again if we need to rerun the process.

By processing both the ALTER TABLE and the INSERT INTO statements as dynamic SQL we avoid the overhead of repeatedly invalidating references to the external table and need to recompile any code that uses the external table;  and that is quite a saving in time over many, many files