Oracle Magazine, Nov/Dec 2017
Database Developer PL SQL of false dynamic SQL will be the subject of my next article Once you determine that you must switch to dynamic SQL you should study SQL injection and protect your database by reducing attack surfaces The most important steps to take are Whenever possible bind values into your SQL string rather than using concatenation If you must concatenate never trust text entered by a user Check it for inappropriate content such as JavaScript code or common SQL hacks such as DROP TABLE Use the DBMS_ ASSERT package to do some of the checking for you OK so you have determined that you need dynamic SQL and you are going to use it appropriately and safely Lets explore options for dynamic multirow querying EXECUTE IMMEDIATE WITH BULK COLLECT EXECUTE IMMEDIATE is the most popular path to dynamic SQL in PL SQL With it you can execute data definition language DDL statements for example drop a table queries nonquery data manipulation language DML statements such as inserts and deletes and even dynamically constructed PL SQL blocks From the standpoint of queries EXECUTE IMMEDIATE works much like SELECTINTO implicit query With static SQL and a single row fetch you would write ORACLE MAGAZINE NOVEMBER DECEMBER 2017 63 SELECT INTO code like this BEGIN SELECT t my_ column INTO my_ variable FROM my_ table t WHERE single_ row_ condition END ORACLE LIVE SQL Oracles Live SQL provides developers a free and easy online way to test and share SQL and PL SQL application development concepts
You must have JavaScript enabled to view digital editions.
