On Packages

How do you test a function from the SQL*Plus command line? Well first you must define a variable. Then you can run the function, assigning its value to the variable as such:

EXECUTE :my_var := my_proc(param);

Note the semicolon in front of the variable name. When writing the function, you must remember that you can only use SQL types. They cannot be PL/SQL specific types. You must also use positional notation for parameters. Finally you must be logged in as the owner, or be granted EXECUTE privilege on the function.

Here is a rule that is not specific to calling functions from SQL*Plus. The functions called from the SELECT statement are not allowed to do any DML. That includes anything called by the function, including triggers. This is just like another rule enforced by Oracle. You cannot query the table that is changing in an UPDATE/DELETE statement.

Now let's talk a little more about variables. A package body can have its own variables. These are private to the package. Variables exported in the specification are automatically initialized to NULL by Oracle.

I will end with dependencies. Suppose procedure A depends on procedure B. Also assume that procedure B depends on procedure C. Then we say that procedure A indirectly depends on procedure C. You can use "utldtree.sql" to set up the environment to trace dependencies.

There is still a lot more to learn before I become an Oracle Certified Professional. Come along as I bring you on my journey.

All About Cursors

I am trying to study up to be an Oracle Certified Professional. It is difficult with all the craziness at work these days. To tell the truth, I am also finding that just reading training materials is not enough. I am not learning that much. It might be time to attend some more in person training.

On to the subject for today. That is cursors. You can use a FOR LOOP to make cursor use really easy. However if you do not, and make use of explicit cursors, just make sure you close your cursors when you are done with them.

There are two type of REF CURSORs. These are the strong and weak cursors. Strong cursors return a specific type. Weak cursors can be used with any query. Both REF CURSOR types can be passed to functions and procedures. You can think of the cursor as a pointer.

Let me close by briefly mentioning subtypes. A subtype is a subset of an existing type with some constraints applied. It can be used any place the more general type is it based on can be used. Here is an example of a subtype:

SUBTYPE small_num_type IS NUMBER(4) NOT NULL;

Types of Collections

I continue to read up on advanced PL/SQL features. This is to get to the next level of Oracle certification. A recent chapter I read was about collections. These includes associative arrays, nested tables, and varrays.

An associative array is faster than a database table. You create one define as a TABLE OF INDEXED BY PLS_INTEGER. You can loop through it with the FIRST and NEXT functions. Access individual records and column like this: my_var(i).my_field.

A nested table is a table within a table. This does seem a bit weird at first. You make a column in the outer table of type TABLE OF . The outer table must be created with the NESTED TABLE keyword. You can insert many records in the sub table. This is done at the time of inserting recording in the outer table.

Finally a VARRAY is created in a two stage process. First you define a type such as CREATE TYPE m_type AS VARRAY(10) OF . Then you can create a table with a column of your newly defined VARRAY type.

There are a couple ways to set the data in these collections. You can use a constructor. Maybe I will give you an example of that later. You can also get the data from the database. Finally you can assign one collection to another one.

Get ready as next time I am going to cover cursors. This includes REFCURSORS.

Java and C Integration with Oracle

I am brushing up to eventually take the exams to become an Oracle Certified Professional. One thing I need to learn is the techniques to mix C and Java code with my PL/SQL.

There are two ways a cross language call can go. One is the callout, where PL/SQL calls with a C or Java function. The converse is the callback, where C/Java calls PL/SQL.

An extproc is one process per session that manages such calls. The extproc needs to be configured with the listener for it all to work correctly. I am just speaking at a high level now and will not delve into the details.

Let me briefly focus on Java here as it seems to be more popular. Java is stored and runs in the Oracle database. You use the loadjava command at the operating system level. You can specify either the Java class or source file with loadjava.

Next time I will briefly review good stuff like associative arrays, nested tables, and varrays.

Materialized View

The final topic I read about in my Oracle 11g book was materialized views. These were originally referred to as snapshots. They improve performance. They are like views in that they are built using a query of one or more tables. However they retain the query results from a specific moment in time. Unlike normal views, they do not get refreshed automatically every time you access the view.

We use materialized views on my project. There is a lot of data to roll up that our applications cannot do in real time. However the customers do not need an up to the minute accurate rollup. They want a reasonable approximation. Therefore we have two separate materialized views. The application will use one or the other, depending on which one is fresher. We alternate refreshing these two materialized views twice a day. It really works.

I am contemplating going for my Oracle Certified Professional certification. That will require a lot of learning. Specifically I want to be certified in Advanced PL/SQL. So I will be sharing what I learn with you.

ASM and Partitioning

I have to confess that I skipped a lot of chapter in the last Oracle 11g book that I read. This is because I am not a DBA. Most topics I defer to our actual DBA team. But it is good to know a thing or two about Oracle database internals.

One example is ASM. It stands for Automatic Storage Management. This is a file and disk manager for Oracle files. Note that you don't need Real Application Clusters (RAC) to take advantage of ASM.

Partitioning is not a new topic. We have been using table partitioning in my project for many years now. This is the process of breaking a table into smaller chunks to improve performance. There are difference types of partitioning such as range, list, hash, composite, reference, and more.

Range partitioning is where you choose a column, and the values in the column determine which partition the records are placed. You define a range of values that the column takes on for each partition.

Important Users

I am not a DBA. So I have never logged into a database as SYS or SYSTEM. And I probably never will. That does not mean I should not know anything about them.

In Oracle 11g, both the SYS and SYSTEM accounts have the DBA role. In addition, SYS has the SYSDBA role. Did that help you? Me neither. Time to get certified as an Oracle DBA.

Here is something I can relate to. Oracle 11g has a technology called streams. This is used to replicate data. You have the database data in a server and it needs to get into another remote location. Stream it over there. Obviously there is more to it than that. But this is the idea.

Another area of weakness in my Oracle knowledge is shared server processes. Oracle recommends that you have one shared server process for every 10 connections into the database. We have a lot of users (connections). Therefore we will most likely want a lot of shared server processes. Luckily we have a lot of hardware to back the database.

Next time I will provide an overview of ASM.