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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.