Q What are the general steps in using any of the operations?
A You need to open a cursor, parse the statements to be executed at runtime, bind any variables if necessary, define columns if using SELECT statements, execute the query, retrieve the data from the buffer into variables, and finally close the cursor.
Q How can you determine if the last operation was a success?
A In addition to using exceptions, you can use the LAST_SQL_FUNCTION_CODE function to verify that the operation performed successfully, based upon the return code.

Workshop

You can now review your knowledge of the DBMS_SQL package, starting with a quiz, followed by some challenging exercises. You will practice writing queries, DML and DDL statements, and anonymous blocks with the DBMS_SQL package. You can find the answers in Appendix A, "Answers."

Quiz

1. For DML and DDL statements, and also for queries, what punctuation must not occur at the end of the query?
2. For anonymous blocks, is all punctuation required?
3. What is the largest size a PL/SQL variable can hold in kilobytes?
4. Why is processing queries and SQL with the DBMS_SQL package considered dynamic?

Exercises

1. Write a SQL query to show all records and all fields to the screen where the value of MyRow is greater than or equal to 2.
2. Write a SQL query to delete all records with MyRow equal to 4.
3. Write the DML code to update the record where MyRow is equal to 1. Change the description to say It Worked.
4. Write the DDL code required to drop the table called MyTable.