Oracle Magazine, Nov/Dec 2017
Database Developer PL SQL By the way one nice advantage of DBMS_ SQL with bulk operations is that the elements are appended to the collection With static SQL BULK COLLECT the contents of the collection are completely replaced by the rows from the latest fetch SUMMARY The PL SQL language makes it easy to implement dynamic SQL requirements When it comes to querying multiple rows of data from a dynamic query you can choose between EXECUTE IMMEDIATE OPEN FOR and DBMS_ SQL DBMS_ SQL should be used only for the most dynamic situations such as when you dont know at compile time how many columns you are selecting OPEN FOR makes the most sense when you are fetching a small number of rows because you can avoid the extra coding involved with using arrays EXECUTE IMMEDIATE is the simplest construct but you must use arrays and BULK COLLECT if you might fetch more than one row Steven Feuerstein is a developer advocate for Oracle specializing in PL SQL Feuersteins books including Oracle PL SQL Programming videos and more than 1500 quizzes at the Oracle Dev Gym devgym oracle com provide in depth resources for Oracle Database developers ORACLE MAGAZINE NOVEMBER DECEMBER 2017 74 PHOTOGRAPHY BY ANDREA MANDEL NEXT STEPS LEARN more about dynamic SQL in PL SQL EXPLORE dynamic SQL scripts on the Live SQL website VIEW and RUN this articles code on the Live SQL website
You must have JavaScript enabled to view digital editions.