Originally posted by gandolf989
I put my reply in at the same time you did. I didn't think of the example as a bind variable. I was under the assumption that bind variables were preceded with a colon and were buried in a text string to make the string look generic to Oracle. So I stand corrected on that issue.
Just some additional information. If you write a static SQL statement like what was in the initial question, it will be compiled to use bind variables. If you look at the statement in the shared pool after it executes, you will see that the variable name was replaced with a bind variable placeholder, i.e. :b1.

Another reason that the static SQL approach is better than the initally suggest approach of using dynamic SQL (execute immediate) is scalability. Whenever you use execute immediate to do SQL, you effectively are opening a cursor, parsing the cursor, executing the SQL and then closing the cursor. All within the context of that single execute immediate statement. If you use static SQL in a database procedure, the PL/SQL engine will manage the cursor for you and will only parse it once per session. Subsequent calls to the procedure will only cause the cursor to be executed again. Excessive parsing can negatively impact your scalability.