Fine Grained Access

Oracle allows you to set general access to functions in the database. You can also take the access privs down to the table level. This is fine grained access (FGA). When it is in place, SQL statements get dynamically modified to restrict the results.

Setting up conditional access requires the use of an application context and policy. The application context is a pair of attribute and value pairs. These can be set with the SET_CONTEXT procedure of the DBMS_SESSION package. You then define a policy on a table using the DBMS_RLS package. The policy grants or revokes access to table data based on one or more attributes of the application context.

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.

Passing My Test

We interview a lot of candidates for developer positions at my company. I only look for three technical competencies: C++ programming, Windows framework programming, and SQL skills. Let me focus a little on the third trait.

Now I don't need an Oracle expert for my team. We already have that. But I do need somebody who knows how to construct basic SQL statements. I need to see evidence of hands-on experience in this area. So I asked about the clauses in a SELECT statement.

I want to hear that a candidate knows that GROUP BY treats the rows selected as one record or group. I also get impressed if the candidate can explain that HAVING restricts the results of GROUP BY.

With that I have given away one third of the keys to our kingdom. Study up your C++ and Microsoft Foundation Classes programming, and you have yourself a job.

Oracle Certified Professional

Today I started reading up to study to be an OCP. My plan is to be an Oracle Certified Profession in PL/SQL programming. I read the first and second chapters in the Oracle training for this exam. I will be sharing what I learned later.

For now I continue discussing some things I learned reading a book on Oracle 11g. Triggers are associated with a single table. The trigger gets dropped automatically if the table is dropped.

An Oracle user have some other names. It is called an account. It is also called a schema. This means the user has a password for authentication. The user potentially owns some database tables, as well as other database objects.

Next time I will discuss some standard power user accounts in Oracle, as well as the clauses in a SELECT statement.

11g Background Processes

The Oracle 11g database has familiar processes running in the background. Most of these processes were there in prior version of the database. However it seems like there are new names for these well known processes. Let's go over them.

There is the database writer called dbw0. Previously this was known as dbwr. There is also the log writer which writes to the redo logs. Pmon is the process monitor. Finally the archiver copies online redo logs to secondary storage (disk).

Obviously there are a lot of other background processes that make up the database instance. I am not as interested in them as I am a PL/SQL programmer, not a DBA. Next time I will go over simple ideas such as triggers and users.

Oracle Database 11g

I recently picked up a book from the library titled Oracle Database 11g A Beginner's Guide. I was excited because I thought I could learn the new PL/SQL features in 11g. Well this book did not specialize in PL/SQL. It also did not specialize in just 11g technologies. The book covered everything in Oracle 11g. That is just too broad. But I did learn a thing or two.

Oracle first ran on a Digital VAX system. That is strange. I assumed it first ran on some flavor of UNIX. An instance is a set of processes in memory. I frequently hear about the Redo Logs from DBAs. These are essentially transaction logs.

Another common term DBAs use is the tablespace. That is a fancy way of describes a disk file the databases uses. Another file of interest is the Spfile. It is the server parameter file. This file has startup parameters in it.

I will be blogging about this 11g book for a little bit. Next time I will cover the new names for the background processes in Oracle 11g.