Oracle Magazine, Nov/Dec 2017
Database Developer PL SQL In this article I will explore how to use PL SQL features to execute dynamic SELECT statements that return multiple rows It turns out that there are three different ORACLE MAGAZINE NOVEMBER DECEMBER 2017 61 ways to do this EXECUTE IMMEDIATE with BULK COLLECT OPEN FOR either with BULK COLLECT or with a loop DBMS_ SQL either using arrays with its own approach to bulk processing or with a loop Before diving into the details however it is incumbent upon me to offer several warnings regarding dynamic SQL and to urge you to make sure that you use the dynamic SQL features of PL SQL only when you need them There are several good reasons to avoid unnecessary dynamic SQL Security Dynamic SQL opens up the door to SQL injection which can lead to data corruption and the leaking of sensitive data Performance While the overhead of executing dynamic SQL has gone way down over the years it is certainly still faster to use static SQL Maintainability The code you write to support dynamic SQL is more literally more code and harder to understand and maintain Sometimes the misuse of dynamic SQL is obvious Consider the following FUNCTION name_ from_ id id_ in IN INTEGER RETURN VARCHAR2 AUTHID DEFINER IS l_ the_ name the_ table the_ name TYPE BEGIN EXECUTE IMMEDIATE select the_ name
You must have JavaScript enabled to view digital editions.
