Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Archive for the ‘Oracle’ Category

Migrating to a new blog site …

with one comment

A number of friends wanted me to do things that weren’t possible while keeping my blog on’s web site. Things like code formatting, downloads for software, et cetera …

You can find the new blog at:

So, I’ve migrated it to my own domain. I’m in the process of configuring the rest of the domain. I’ll update you through the blog as I complete the process.

I’ve got grand plans (lets hope there’s time). For example, I’m planning to migrate and update the domain too. I’ll plan tutorials on Java, PL/SQL, and PHP initially as well as administration tips, techniques and walk through steps for my favorite platforms. As time allows, I’ve got some stuff comparing SQL across platforms – much beyond the trivial Oracle’s SPOOL is MySQL’s tee (not herbal tea).

While my perspective may change over time, I’m quite fond of Mac OS X, Ubuntu, and Red Hat. I won’t exclude Microsoft because we must live with it, but isn’t CrossOver sweet! I’d actually tried to get Oracle to run through it. I may yet, I’m still playing with it.

I’m trilled that Chris Jones and Justin Kestelyn got me blogg’n. I’ve always wanted to contribute more but the forums have mavens with much more time than I have to answer questions. The blog lets me share ideas and concepts as they travel with me on a daily basis.

Stats on the blog pages may not migrate but if time allows maybe I’ll write a plugin if I can’t find one. Generally, I don’t think anybody cares about the stats. It’s the information to solve problems, and I hope that’s what you find useful in my blog.

By the way, it wasn’t just the ability to have my blog pages look cool on my iPhone that drove the change, but it does look nice!!!


Written by maclochlainn

November 1, 2008 at 10:08 pm

Posted in Generic, Mac, Oracle, Ubuntu

That old $TNS_ADMIN environment variable

leave a comment »

Somebody reminded me that I should include the $TNS_ADMIN variable to avoid unnecessary file searches (minor expense) in the Mac configuration instructions. This eliminates additional searches when running sqlplus and tnsping executables. I’ve updated that blog page with those instructions. It’s not necessary to make it work but it does avoid a read to the local and /etc directories on your Mac OS X for a tns_names.ora file before reading it from the $ORACLE_HOME/network/admin directory.

Written by maclochlainn

November 1, 2008 at 3:22 am

Adapter or not adapter that’s the question

with 2 comments

The Adapter pattern in Object-Oriented OO programming is missing when it comes to how you can read, pass, and manage a PL/SQL system reference cursor. At least, it’s missing for PL/SQL native development. It is available through the OCI but only as a multiple dimensional array of strings.

Here’s an illustration of the Adapter Pattern (courtesy of the Design Patterns book by Gamma, Helm, Johnson, and Vlissides):

There are a few ways to handle reference cursor in PL/SQL. I’ve updated the earlier blog to more completely cover the options. In all cases within a PL/SQL environment, you must know the target type of the PL/SQL record structure. The target for converting a PL/SQL reference cursor is a a PL/SQL record structure.

You can assign a PL/SQL record structure through a PIPELINED table function to an aggregate table, which can be read by SQL. The following query lets you read an aggregate table back into a PL/SQL structure but begs the question of why you’d want to do that. The actual query by itself is in the updated blog entry linked above.

The OCI8 driver lets you take the system reference cursor from a PL/SQL block and translate it to a multidimensional array of strings. The following (borrowed from my book on PHP programming with Oracle) demonstrates how to open a statement and a reference cursor, which lets you manage the OUT mode parameter (covered in this earlier post) variable of a PL/SQL reference cursor. Our thanks should go to the OCI team because they see the world of interactivity.

You can query the results of the reference cursor ($rc), like this:

Moreover, it would be wonderful if Oracle let you implement a full Adapter pattern but there are constructive ways to work with what we’ve got now. You actually get a bit more through the JDBC implementation but that’s for another blog I guess. As to adapter or not adapter, it’s clearly not.

Written by maclochlainn

October 31, 2008 at 11:00 pm

Quick review of PL/SQL formal parameter modes

with one comment

My students wanted a supplement on how variable modes work in PL/SQL, so I figured it would fit nicely in a quick blog entry. If you’re interested, read on …

PL/SQL supports three patterns of variable modes in functions and procedures. The easiest supports a pass-by-value function or procedure, and it is the IN mode of operation. The other two are related but different, and support a pass-by-reference function or procedure. The differences between a function and procedure are straightforward: (1) A function can return a value as an output, which is known as an expression; (2) A function can be used as a right operand; (3) A procedure can’t return a value because it more or less returns a void (borrowing from the lexicon of C, C++, C#, or Java and many other languages), (4) A procedure can be used as a statement by itself in a PL/SQL program while a function can’t. The variables you define in a function or procedure signature (or prototype) are the formal paramters, while the values or variables assigned when calling a function or procedure are the actual parameters.

IN mode:

An IN mode variable is really a copy of the variable, but you can ask to pass a reference. PL/SQL typically obliges when using the IN mode of operation. The following defines a pass-by-value PL/SQL function (other than the return type, you could do the same in a procedure too):

You can test the values of the actual parameter before and after the function call while also testing it inside the function. You can also assign a literal number or string as the actual parameter because the IN mode only requires a value because it discard the variable reference and value when it completes.

There is an exception data type for this IN mode operation, and it is the PL/SQL system reference data type (more on this type can be found in the following post). A PL/SQL reference cursor can only be passed when it is already opened, and it actually passes a reference to the cursor work area in the Private Global Area (PGA).

You can’t assign a value to a formal parameter inside a function when the variable has the default (or IN) mode of operation. Any attempt to do so raises a PLS-00363 with a warning that expression (formal parameter) can’t be used as an assignment target. A test of the function follows:

This seems to be the preferred way to implement functions for beginning programmers.

IN OUT mode:

An IN OUT mode variable is typically a reference to a copy of the actual variable for a couple reasons. If the function or procedure fails the original values are unchanged (this is a departure from the behavior of collections passed as actual parameters in Java). You can assign values to the formal parameter at runtime when using an IN OUT mode variable.

At the conclusion of the function or procedure the internal variable’s reference is passed to the calling program scope and replaces the original reference to the actual parameter. Here’s an example of an IN OUT mode variable in a function.

As you can see the external value is changed inside the function and at completion of the function the external variable passed as an actual parameter is changed:

This seems to be used more frequently in procedures than functions in PL/SQL. However, you can use the approach in either. I’d recommend it for functions that you call through the OCI or Java.

OUT mode:

An OUT mode variable is very much like an IN OUT mode variable with one exception. There is no initial value in it. You must assign a value to an OUT mode variable because it has no value otherwise. If the function or procedure fails, the external variable is unchanged. At the successful conclusion of the function or procedure, the reference for the internal variable replaces the reference to the external scoped variable.

The following shows you the test case:

The OUT mode also has an exception, which relates to CLOB and BLOB datatypes. You can find more about large objects in this presentation made at the Utah Oracle Users Group – Oracle LOBs.

This should be pretty straightforward but if you have suggestions to improve it let me know.

Oracle networking – some ugly duckings

leave a comment »

I tried to answer a question in the forum a couple weeks ago about connections. It contained all the trite stuff about check this, then that, et cetera because I couldn’t remember why I’d seen an TNS-03505 error. It returns an illustrative message “Failed to resolve name” error. Along the way, I tripped into ORA-12560 and one I’d never seen before an ORA-12518. If you want the dirt on these read on …

You’ll typically encounter this error when you’re working on a laptop. It gets triggered when you run the tnsping utility. When you’ve qualified the hostname and hostname.domain name in the hosts file and in the tnsnames.ora file, it’ll throw the TNS-03505 error because you’re on another network. You may also encounter it when there’s a change or discrepancy between the machine hostname and DNS server results. Lastly, you may encounter it when you’ve lost the lease on an IP address and now have a new lease with a different IP address.

These errors effectively block successful tnsping calls. You get around it by shutting down the listener, modifying the IP address in the tnsnames.ora file if you’re not using a hostname, setting the %TNS_ADMIN% environment variable in a command session when you have multiple Oracle homes, and restarting the listener.

This is the sqlplus utility cousin of the TNS-03505. All the rules that apply to it apply to this.

This one is cute. I only hit it because my touchpad is too sensitive on the Dell laptop. It occurs if you pause the Windows listener service. You fix it by restarting the service. It is an enigma within a conundrum (Churchill on the old Soviet Union) why anybody would create a service like this with a pause option. Wouldn’t it be cool if somebody at Oracle were listening now (that is reading this blog) and they got the service fixed.

Written by maclochlainn

October 24, 2008 at 4:18 pm

Posted in Oracle

Tagged with , ,

Inline views, table fabrication, and the WITH clause

leave a comment »

Sometimes working with a product like Oracle brings a surprise, like a new feature you failed to catch when it was released. I’ve seen a lot of entries using inline views through the WITH clause in the Oracle forums. It caught my eye because it is such a radical departure from portable SQL syntax of an inline view. I finally went searching to find the rationale for this approach.

The answer doesn’t lie with the Charlotte like National Treasure, but with simplifying the join syntax, as qualified in the Oracle Database 2 Day + Data Warehousing Guide 11g, Release 1. The following looks at this approach, and compares using the WITH clause instead of the inline view to perform table fabrication.

Oracle tells us to use the WITH clause when a query has multiple references to the same query block and there are joins and aggregations. Basically, the WITH clause lets you name inline views and then reuse them inside other inline views. Like PL/SQL, they must be defined before they can be referenced. Unlike PL/SQL, they have no equivalent for forward referencing a query block. The basic syntax is:

The first code block is assigned the inline name. You can then reuse the inline code block inside any subsequent code block or the master query. The idea is that this syntax is simpler than the generic syntax.

The standard way of writing this is consistent across other SQL databases, and I don’t really see it as any more complex than the WITH syntax Oracle provides.

The WITH clause is also capable of letting you create tables from literal values, which is known as table fabrication. The following syntax uses the with clause to fabricate a table of two columns (x and y) and two rows.

The next shows the traditional way of fabricating a table using an inline view:

You can also use this type of syntax in MySQL to fabricate a table. You can’t use the WITH clause in MySQL because it’s not supported. You’ll notice in the example that the FROM dual portion is omitted in MySQL. Wouldn’t it be nice if Oracle let that happen too?

A neat function that I picked up on the Oracle Technical Network is the NUMTODSINTERVAL (number to date-stamp interval) function, which can create intervals for qualifying sales by hour or quarter hour. More or less it is a way to fabricate timing intervals. Here’s a quick example:

This has been the syntax, now I’ll have to check whether there are any performance differences. I suspect that since the execution plan is the same that there aren’t any performance differences but you never know until you test it.

Written by maclochlainn

October 19, 2008 at 6:45 am

Reference Cursors – Why, when, and how?

with 3 comments

Quick Update: I move the blog because folks told me the screen shots weren’t as useful as copiable and formatted code. You can find both and new content in the updated blog posting here, I apologize for the inconvenience but there wasn’t a way to redirect you from WordPress.

A week or two ago, I noticed a discussion on the Oracle Technical Network forum that asked some questions about reference cursors. The discussion made me reflect if there were a couple simple rules for using reference cursors. This contains my take on the rules and some examples.

What are they …
There are two types of reference cursors. They are either weakly or strongly typed PL/SQL only data types. A weakly typed cursor doesn’t anchor the output of a query to a table or view, and implements the PL/SQL SYS_REFCURSOR type. A strongly typed cursor anchors the output of a query to a table or view, and typically anchors itself to a package or local user defined type (UDT) of a cursor.

They’re defined in the declaration block or a package specification, like this:

TYPE weak_cursor IS REF CURSOR;
TYPE strong_cursor IS REF CURSOR RETURN table_name%ROWTYPE;

When you opt to use a weakly typed cursor, you can anchor it to a PL/SQL structure that is a collection of a PL/SQL record structure. This is known as an associative array, and commonly called a PL/SQL table. However, it seems politically insensitive to use that language even though Oracle published it as such in the Oracle 8 documentation because it isn’t really a table but an implementation of a list of record structures. That means the index is sparsely populated or may contain breaks in a numeric index sequence. You can also use character string indexes from Oracle 10g forward to index this type of collection.

Why should you use them …
They’re useful structures when you want to open a cursor with known output columns in one program unit and move it to another for processing. Strongly typed reference cursors are appropriate for this purpose when both the passing and calling program units are in PL/SQL. Weakly typed reference cursors can also be used passing and calling PL/SQL programs. Weakly typed reference cursors require the same structures as strong reference cursors. Specifically, you will need a record structure for row-by-row fetch and collection of a record structure for bulk fetch. Whether you define them as strong or weak, you’ll need these structures for use inside PL/SQL program units that read the cursor. As a rule of thumb, you should generally define them as strongly typed reference cursors when they’re used exclusively in PL/SQL.

The most effective use of weakly typed reference cursors is in external OCI programming languages. Actually, it appears that weakly typed cursors have little use anywhere but in an OCI program. In external programming languages, you can fetch associations and dynamically discover the structure of a weakly typed reference cursor. That’s why they’re useful in OCI programming languages. You can find an example of using a weakly typed cursor in a PHP program on this blog entry.

How you use them …
You can find an example of using a weakly typed reference cursor as an input parameter in this discussion of pipelined PL/SQL functions. You can return a weakly typed cursor from an NDS (Native Dynamic SQL) statement as follows:

Outside of an OCI program, the only way to query this is with a SQL statement like:

You can return and query a weakly typed cursor as a strongly typed cursor by doing four things. Anchor the cursor statement to a table or view in the database catalog. Create a record structure to store rows of the reference cursor in an ordinary fetch, or a record structure and associative array collection data type to store a set of rows of the reference cursor. Then, explicitly open the reference cursor into the variable. Here is the detail to support those steps:

1. Create a package specification, that acts like an Object-oriented interface because all it contains are type definitions that you’ll implement in other code modules (provided they have the EXECUTE privilege on the package):

2. You use the WEAKLY_TYPED_CURSOR function presented earlier in this blog to create a weakly typed cursor as the return type of a stored function.

3. The last step creates a function that translates a weakly typed reference cursor into an aggregate table by using a pipelined function, as shown in the illustration.

The PIPE command transfers a scalar variable or record structure into a row of a target aggregate table, which can be read through the TABLE command in a SQL statement. A neat thing that happens in the example, is that it simply transfers the whole row returned by the cursor as an instance ACTIVE_RECORD into an indexed element of the ACTIVE_COLLECTION collection. The alternative would be syntax like the following:

active_collection(counter).item_title := active_record.item_title;
active_collection(counter).item_title := active_record.item_subtitle;

While those are neat features that may be copied from example code, they’re useless. The more direct way to create this translation program eliminates the collection entirely.

Another alternative would be to do a BULK COLLECT directly into the collection. The next example demonstrates that but you should note that you can’t use a FORALL when assigning collection values to a PIPE for a pipelined function.

4. In SQL, you can query the result using the following syntax:

You can change the prior query to call the use_of_bulk_cursor function to test the bulk collect operation. It didn’t seem worth the effort of doing another screen shot.

More or less, the limitation exists because when operating exclusively inside of PL/SQL there is no way currently to support an Adapter OO pattern. You can implement an Adapter pattern if you use an OCI8 external programming language to read the cursor as a dynamic result set (really only a multi-dimensional array of strings – or technically because the index is sparse a list).

Alternatively in a PL/SQL only solution, you can simply use a strongly typed cursor and bundle all the components into a single program unit. Assuming you create a two column view from the ITEM table, you can use the following query (code base can be downloaded from McGraw-Hill for the Oracle Database 11g PL/SQL Programming book):

If you’d like another example, post a comment to let me know.

Written by maclochlainn

October 11, 2008 at 6:03 am