DML and PL/SQL

Today I received my CD-ROM self study software from Oracle. I have not installed it yet. I am trying to finish my self study book first. This evening I read the chapter on PL/SQL in the book. Specifically they went over how you do DML within PL/SQL blocks.

The basic PL/SQL SELECT statement only allows you to retrieve one row into a variable. Any more rows coming back from your query will cause an exception in your code.

When you do issue a SELECT in a PL/SQL block, you are causing an implicit cursor to be defined. This cursor is controlled by PL/SQL. It is opened, your statement SQL is run, then it is closed. This all happens automatically. For this cursor, SQL%OPEN is always defined as FALSE.

Here is a gotcha that I have personally experienced. A function cannot issue DML. I guess they don't want you to have side effects from some code which is only supposed to return a value. This is similar to the fact that you cannot issue DML in a SELECT statement either.

There are only two more chapters in my self study book. Then I move on to the self study software from Oracle. I will keep you posted with all the goodies that I learn.