Oracle Magazine, Nov/Dec 2017
Database Developer PL SQL 16 22 Loop through the result set using the same FETCH and EXIT WHEN statements ORACLE MAGAZINE NOVEMBER DECEMBER 2017 71 you would use with explicit cursors 24 Close the cursor now that you are done with it DBMS_ SQL AND MULTIROW QUERYING DBMS_ SQL is a package supplied by Oracle Database to perform dynamic SQL operations Up until Oracle8i it was the only way to execute dynamic SQL in PL SQL When native dynamic SQL commands EXECUTE IMMEDIATE and OPEN FOR were added in Oracle8i DBMS_ SQL became a method of last resort for dynamic SQL This means that it is used now only for the most complex dynamic SQL scenarios generally categorized as method 4 dynamic SQL which is when at the time of compilation you dont know either the number of columns in your SELECT list or the number of bind variables in your statement DBMS_ SQL is a last resort option because you must write lots more code to use it For simple operations such as those shown in the earlier examples DBMS_ SQL is overkill For method 4 dynamic SQL it is a perfect fit You can use DBMS_ SQL to fetch multiple rows either one at a time or in bulk In this article I will show you only the bulk approach DECLARE l_ cursor PLS_ INTEGER DBMS_ SQL open_ cursor l_ names DBMS_ SQL varchar2_ table l_ salaries DBMS_ SQL number_ table l_ fetch_ count PLS_ INTEGER BEGIN DBMS_ SQL parse l_ cursor 1 2 3 4 5 6 7
You must have JavaScript enabled to view digital editions.
