Thursday 8 September 2011

Does Oracle have a tool for formatting PLSQL

Question:
Does Oracle have a product that formats PL/SQL procedures?
Answer:
Oracle itself does not offer any formatting or pretty-print capabilities for PL/SQL programs, but many of the most popular IDEs do. For example, TOAD and SQL Navigator from Quest offer integrated and highly customizable code formatting. PL/SQL Developer, another very popular tool, offers its own "PL/SQL Beautifier."
By the way, formatting your PL/SQL is not just handy for improved readability. It can also actually improve the performance of your code! It is not at all uncommon to have developers write the same logical SQL statement repeatedly in their application code, but with different physical representations (such as line breaks and extra spaces in varying locations within the string). These physical differences will result in excessive and unnecessary parsing. Format all your code before it goes into production, and you will avoid such problems, at least for those cases where the only differences between the SQL statements line in white space, variations in case of keywords, etc.
While on this topic of avoiding physical variations on the same logical statement, I have some good news to share:
Prior to Oracle9i, the PL/SQL compiler would automatically reformat or "canonicalize" (make the statement conform to the "orthodox" or standardized format) any static SQL statements in your code, thus ensuring that SQL statements that only differ by white space or other formatting aspects would have a single physical representation when executed.
In the original release of Oracle9i, however, Oracle introduced a single common SQL parser for SQL and PL/SQL, and simultaneously stopped reformatting SQL statements when our code was compiled. That's the bad news.
The good news is that in Oracle Database 10g Release 1, this canonicalization of static SQL was, for the most part, restored—and back-ported to Oracle9i version 9.2.0.5.
The new state of canonicalization does not quite match the coverage of Oracle 8.1.7 managed. In particular, if there is no whitespace surrounding tokens such as + - * / = ( ) ' || then the reformatting will be not be performed properly.
Still, it is way better than what we have initially experienced with Oracle9i, which is no reformatting at all.

No comments:

Post a Comment