Problem solve Get help with specific problems with your technologies, process and projects.

Insert without a column list

Our coding standards do not allow inserts without a column list in production code, but some may have slipped in anyhow. We want to add a few new columns to most of our tables, and have them populated with default values, but we want to make sure that won't break any production code. We are writing a Perl script to search our source (PL/SQL) code for cases of insert without a column list, but it occurred to me someone may have already written a utility to look for cases like this. Know of anything?

While I'm sure writing a perl script to do the searching will get the job done, if your source code is PL/SQL then why not just use the data dictionary view named DBA_SOURCE to query the TEXT column to do your search? You should be able to write a procedure to loop through DBA_SOURCE using an INSTR function to see if an INSERT....VALUES clause exsits without a column list. You'd likely be looking for the keyword INSERT and then need to find a VALUES clause that wasn't preceded by an end parenthesis ). If you found such an occurrence, you could simply write out the NAME of the code block and the LINE in the code on which you found the possible error (I'd probably write it to a temporary table created for this purpose).

That way, you could get the list you need to tell you where you may have "non-standard" code.

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.