Rights, Hints, and Bulk Collect

I am back to recounting what I learned at my hand on instructor led Oracle training. The first topic deals with who is authorized to execute database objects like functions or procedures. The default method is called definer’s rights. This means that the rights of the user who created (compiled) the function or procedure is used when determining what the code can do. This authorization is checked at the function and procedure level.

On a totally different topic, you can provide Oracle with a NOCOPY hint when specifying parameters to a function or procedure. This is truly a hint that Oracle may end up ignoring. This hint implies that the developer recommends the parameters be passed by reference. The benefit is a speed increase especially when the parameters are large. However there is a danger when using this. If your program blows up in the procedure or function, the state of your data may be undefined.

Finally I want to briefly mention the BULK COLLECT mechanism. I have seen top notch Oracle developers use this feature. It has always mystified me. Perhaps it has something to do with how the words BULK COLLECT sound. Anyway this technique is used when exchanging data between the database and a collection.

Normally you would iterate through a collection one record at a time and exchange the data with the database. This however is a slow technique as you are switching context between the PL/SQL and SQL governors. The BULK COLLECT let’s you do all the work at once. It is good when you are dealing with a lot of data (a big table). It is also easier to code than to manually iterate through the records yourself.

Dynamic SQL and Such

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.

SQL Execution

Part of the reason to attend in person training is to pick up the secrets that you cannot read in a book. I got plenty of that at my last Oracle hands on training class. In addition to that, I got some pieces of information that were beyond the scope of the class. However they were nevertheless very interesting and potentially useful. Today I want to discuss a sample of one topic that I have learned. That is what Oracle does when it is working towards executing a SQL statement.

The SQL execution portion is separate from the PL/SQL processor in the database. This SQL execution portion is governed by a common subsystem which handles all SQL requests, whether they come from the result of PL/.SQL or a user typing SQL at the SQL*Plus command prompt.

Here is the order of things that happen when Oracle prepares to run a SQL statement. First there are syntax checks. Then there are semantic checks. Third there are checks whether all the permissions are in place for the objects being queried. Then the cost based optimizer (CBO) is consulted on how to best approach the query for performance purposes. The CBO step has a number of sub-steps. Finally the row selector identifies the results.

There are many parts to the CBO step. Indexes are analyzed to determine an access path to the data. Then the join order is evaluated if more than one table is involved. Then the plan costs are counted and evaluated. Obviously Oracle is going to want to choose a plan that has the least costs.

My instructor for PL/SQL programming told me there was a whole separate class for performance tuning of Oracle. In that class he would delve deeper into the SQL execution process. A coworker of mine is encouraging me to attend this class and get tested on it. However I think my heart is with PL/SQL development. Therefore my next class will be an advanced PL/SQL development class. However it is nice to know that there is a whole world of information to learn about within the Oracle universe.

Built In Packages

Oracle has quite a few built in packages available for PL/SQL developers to use. Some packages are optional for the database and must be manually installed. You can find information on the built in packages at Tahiti dot Oracle dot com. Look under Application Development, then SQL and PL/SQL, and finally PL/SQL Packages and Types Reference.

If you are not planning any commercial uses for it, Oracle allows you to download, install, and use any version of their databases. The Express Edition of Oracle is a good choice for basic Oracle database needs. It was created in response to the open source databases available now. It competes with MySQL for example. It has a 3TB maximum size limit. And it is completely free for any type of use.

Normally the exceptions raised by a package are defined by it. However generic errors sometimes cause a package to raise an exception that it does not define. One sample package is UTL_FILE. As you can imagine, it is used for file input/output. Note that you will probably not want to use this package to output reports. There is other software specialized for that purpose.

Another package available is UTL_MAIL. It works with an SMTP server. The package itself does not actually send email. The SMTP server does that. Note that this package is not installed by default during an Oracle install. You must manually install and configure it to work with your SMTP server.

OCA Certificate

This week I received my certificate from Oracle. I am now an Oracle PL/SQL Developer Certified Associate (OCA). This demonstrates that I did the work and have the capability to do Oracle PL/SQL.

Now Oracle is encouraging me to continue and get an Oracle Certified Professional (OCP) credential. For PL/SQL developers, I believe that you only have to pass one more exam to get to the OCP level. That assumes that you already have some received some training. My company supports its employees for such certification. So I think I might go for it.

Since I am officially an OCA, I can display the OCA logo from Oracle. Now I don't think I will go as far as putting it on my business cards. That logo is reserved for my own company. However I might add it to my email signature. I have seen a lot of Oracle DBAs do that when they get their OCP.

I plan to continue to share the interesting facts I learned during my hands on training class. Be on the lookout for future posts with this info.


Oracle packages are broken down into two parts. These are the specification and the body. The specification is just a declaration for external users of the package. The body is the code of the package. There is only one copy of the package code in memory for all users.

The package body needs to match the specification for the exported functions and procedures. It is possible for a package to not have a body. This scenario happens when the package does not have any functions or procedures. A concrete example is a package that just defines constants.

You have the ability to overload functions and procedures within a package. This is not unique to Oracle. Overloading is when you have multiple functions/procedures with the same name. The one that gets executed is the one that has the matching number and types of arguments as how you call it.

There is a special optional section of code where the package can be initialized. It must come at the end of the package. It is called the first time a session calls the package. Next time I will talk a little bit more about packages. I will specifically go into some built in packages from Oracle.