Oracle Magazine, Jan/Feb 2018
Database Developer PL SQL There are three very important reasons to follow this rule Security Dynamic SQL opens the door to SQL injection which can lead to data corruption and the leaking of sensitive data It is impossible to inject malicious code into a static SQL statement Performance Although the overhead of executing dynamic SQL has gone way down over the years it is certainly still faster to use static SQL Its also easier to optimize static SQL because you can analyze explain plans before they execute in production and modify your SQL accordingly right from your code editor Maintainability The code you write to support dynamic SQL is more literally more code and harder to understand and maintain Dont make your life and the lives of developers coming after you any more difficult than it has to be In this article I explore various inappropriate or unnecessary uses of dynamic SQL that I have come across over the years If as you read a section you hear alarms ringing in your head along the lines of Gee that looks like something I wrote last week or Wait a minute thats exactly what Joe wants to put into production next week its time to take action and convert that dynamic SQL to static SQL Your users and your coworkers will thank you CHANGING VALUES DO NOT DYNAMIC SQL MAKE You need to write dynamic SQL when you dont have all the information you need to construct and parse the statement at compile time You do not however need to know the values of bind variables for example the specific department ID or the name of the customer in order to parse a SQL statement Consider the following function ORACLE MAGAZINE JANUARY FEBRUARY 2018 74
You must have JavaScript enabled to view digital editions.
