There are a number of restrictions on the use of cursor expressions:
You cannot use a cursor expression with animplicit cursor because no mechanism is available to fetch the nested cursor INTO a PL/SQL data structure.
Cursor expressions can appear only in the outermostSELECT list of the query specification.
You can place cursor expressions only in a SELECT statement that is not nested in any other query expression, except when it is defined as a subquery of the cursor expression itself.
A cursor expression can be used as an argument to table functions, called within the FROM clause of a SELECT statement.
Cursor expressions cannot be used when declaring a view.
You cannot perform BIND and EXECUTE operations on cursor expressions when using the CURSOR expression in dynamic SQL.
Chapter 15. Dynamic SQL and Dynamic PL/SQL
Dynamic SQL refers to SQL statements that are constructed and executed at runtime. Dynamic is the opposite of static. Static SQL refers to SQL statements that are fixed at the time a program is compiled. Dynamic PL/SQL refers to entire PL/SQL blocks of code that are constructed dynamically, then compiled and executed.
Time for a confession: I have had more fun writing dynamic SQL and dynamic PL/SQL programs than just about anything else I have ever done with the PL/SQL language. By constructing and executing dynamically, you gain a tremendous amount of flexibility. You can also build extremely generic and widely useful reusable code.
So what can you do with dynamic SQL and dynamic PL/SQL?[1] Here are just a few ideas:
[1] For the remainder of this chapter, any reference to "dynamic SQL" also includes dynamic PL/SQL blocks, unless otherwise stated.
Execute DDL statements
You can only execute queries and DML statements with static SQL inside PL/SQL. What if you want to create a table or drop an index? Time for dynamic SQL!
Build back-ends for web-based applications
These might allow users to specify which columns they want to see and vary the order in which they see the data. In other words, you can support full ad hoc querying and updating applications.
Write a generic string parsing engine
Such a parsing engine might accept a delimited list and deposit the elements of that list into your collection.
Ever since Oracle Version 7.1, we PL/SQL developers have been able to use the built-in DBMS_SQL package to execute dynamic SQL. In Oracle8i, we were given a second option for executing dynamically constructed SQL statements: native dynamic SQL (NDS). The DBMS_SQL package is examined in depth in the book Oracle Built-in Packages (described in Chapter 1). In this chapter, we'll concentrate on NDS, which is easier to write and faster to execute. Also, unlike DBMS_SQL, NDS is a native part of the PL/SQL language, so it makes sense to cover it here. There are times, however, when you will want to use DBMS_SQL. You will find at the end of this chapter a comparison of the two approaches and recommendations for selecting the implementation path for your dynamic SQL needs.
