On the third day of my instructor led Oracle training, we seemed to cover a number of points. We also touched upon the techniques for performing dynamic SQL. So this post may be a bit erratic. I just thought I would get some more info out there for you.Database Control Language (DCL) is the way to control permissions to database objects. These commands begin with GRANT or REVOKE. On a different topic, you must provide bind variables to an EXECUTE IMMEDIATE statement. This allows you to supply positional parameters to the SQL. The name of the bind variables is arbitrary. It is the order that determines which variables are matched with the placeholders in the SQL.
There are two main types of cursors. These are strong and weak cursors. A strong cursor will return data in a record type. The weak cursor will just return unstructured data. There are two major techniques to executing dynamically formed SQL statements. The older technique is to use the DBMS_SQL package. This method has been around a long time. However it is much slower than Native Dynamic SQL (NDS). Note that the OPEN_CURSOR command in DBMS_SQL does not actually open the cursor. It just creates it.
Finally I got a peek at the latest Enterprise Manager for the Oracle database. Since I am a programmer and not a DBA, I do not use the Enterprise Manager. This product is now web based. It can be used to assist with SQL tuning. Next time I will probably write about invoker’s and definer’s rights. Until then be well.
