Oracle Magazine, Jan/Feb 2018
Database Developer PL SQL But a single function does not an application make Either this function is being called or it should be dropped If it is being called you should check to see where and how the function is used and see if that usage justifies the dynamic SQL implementation You could do a text search through your code via an editor such as Sublime or an object search in Oracle SQL Developer You could also use the PL Scope in Oracle Database if you gathered identifier information across your source code Suppose that after doing this analysis you find that the function is called twice as follows l_ name name_ from_ id table_ in TABLE1 id_ in l_ id l_ recent_ name name_ from_ id table_ in TABLE2 id_ in l_ most_ recent_ id Now you could shrug and say Yep there are two different table names I need to use dynamic SQL And then you could get to work on changing the function so that it is not quite so vulnerable to a SQL injection attack with among other things the DBMS_ ASSERT package But that would be a mistake What you should think and say is this What Just two different tables I dont need dynamic SQL for that Thats just being lazy And then you could and I will in this article pursue one of two alternatives Create two different functions Change to static SQL inside that single function Two different functions Really why not write two different functions Its so easy and fast to write PL SQL functions that call SQL With a small amount of work I can fork that single function into two as follows ORACLE MAGAZINE JANUARY FEBRUARY 2018 80
You must have JavaScript enabled to view digital editions.
