MacLochlainns Weblog

It is nice to know that the entries I’ve made have readers. It’s surprising to see almost 22,000 direct hits and another 5,000 syndicated hits in the 6 months of blogg’n. The following table contains the list and links of the top blogs that have netted 5 or more hits per day.

I plan to move this to my own site shortly and will provide notice and pay WordPress to forward. Naturally, any comments are welcome.

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 by folks who pre-date the Oraclei documentation that Oracle would prefer we forget. 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:

-- Creates a function to dynamically open a cursor and return it as a return type.
CREATE OR REPLACE FUNCTION weakly_typed_cursor
(title_segment VARCHAR2) RETURN SYS_REFCURSOR IS
  weak_cursor SYS_REFCURSOR;
  stmt VARCHAR2(4000);
BEGIN
  -- Create dynamic SQL statement.
  stmt := 'SELECT item_title, item_subtitle '
       || 'FROM   item '
       || 'WHERE  REGEXP_LIKE(item_title,:input)';
  -- Explicit cursor structures are required for system reference cursors.
  OPEN weak_cursor FOR stmt USING title_segment;
  RETURN weak_cursor;
 
END;
/

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

SQL> VARIABLE cursor REFCURSOR
SQL> CALL weakly_typed_cursor('Harry') INTO :cursor
SQL> SELECT :cursor FROM dual;

Alternatively, you can do the following implicit call:

SQL> SELECT weakly_typed_cursor('Harry') FROM dual;

If you tried to select it using the TABLE function, you’d get the following error:

SQL> SELECT * FROM TABLE(weakly_typed_cursor);
     *
ERROR at line 2:
ORA-22905: cannot access ROWS FROM a non-nested TABLE item

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):

-- Create a structure declaration package, like an interface or abstract class.
CREATE OR REPLACE PACKAGE pipeliner IS
  -- Declares a row structure that doesn't map to a catalog object.
  TYPE title_structure IS RECORD
  (item_title    VARCHAR2(60)
  ,item_subtitle VARCHAR2(60));
  -- Declares an associative array, or a collection of a PL/SQL row structure.
  TYPE title_collection IS TABLE OF title_structure;
END pipeliner;
/

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.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_input_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  counter           NUMBER := 1;
  active_record     PIPELINER.TITLE_STRUCTURE;
  active_collection PIPELINER.TITLE_COLLECTION:=
                      pipeliner.title_collection();
BEGIN
  -- A simple loop allows you to fetch the already open cursor
  LOOP
    FETCH incoming_cursor INTO active_record;
    EXIT WHEN incoming_cursor%NOTFOUND;
    -- Extend collection, assign a value.
    active_collection.EXTEND;
    active_collection(counter) := active_record;
	-- Add a row to the aggregate return table.
    PIPE ROW(active_collection(counter));
    counter := counter + 1;
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

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.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_input_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  counter           NUMBER := 1;
  active_record     PIPELINER.TITLE_STRUCTURE;
BEGIN
  -- A simple loop allows you to fetch the already open cursor
  LOOP
    FETCH incoming_cursor INTO active_record;
    EXIT WHEN incoming_cursor%NOTFOUND;
	-- Add a row to the aggregate return table.
    PIPE ROW(active_record);
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

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.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_bulk_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  active_collection PIPELINER.TITLE_COLLECTION:=
                      pipeliner.title_collection();
BEGIN
  -- A bulk collect fetch the already open cursor
  FETCH incoming_cursor BULK COLLECT INTO active_collection;
  FOR i IN 1..active_collection.COUNT LOOP  
	-- Add a row to the aggregate return table.
    PIPE ROW(active_collection(i));
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

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

-- Query the results.
SELECT *
FROM TABLE(use_of_input_cursor(weakly_typed_cursor('Harry')))
/

… or use_of_bulk_cursor using the following syntax:

-- Query the results.
SELECT *
FROM TABLE(use_of_bulk_cursor(weakly_typed_cursor('Harry')))
/

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):

DECLARE
  TYPE cursor_record IS RECORD
  ( item_title    item.item_title%TYPE
  , item_subtitle item.item_subtitle%TYPE );
  t CURSOR_RECORD;
  TYPE strong_type IS REF CURSOR RETURN item_title%ROWTYPE;
  c STRONG_TYPE;
BEGIN
  OPEN c FOR SELECT item_title, item_subtitle FROM item;
  LOOP
    FETCH c INTO t;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line(t.item_title);
  END LOOP;
  CLOSE c;
END;
/

If you like this, you should check out another wrapping technique here. If you’d like another system reference example, post a comment to let me know.

I think one annoying error that I’ve suffered with on Microsoft Vista with Oracle 11g is the nmefwmi.exe error. I got a message from Microsoft saying it’s fixed. They refer to Oracle Bug #6596234, but it’s really fixed in the Oracle 11g patch (11.1.0.7.0). Therefore, don’t waste your time looking for a one-off patch on metalink.

You can ignore this message on Vista that says refer to Bug #6596234 … by the way it’s actually under patch #6890831. Only the Linux and Solaris ports are available as of today.

Somebody got to my blog searching for how to find an external file. I thought about why they might be looking for that. Since the lack of a file raises an error stack, I concluded that they want to verify whether the file exists before querying it. That way, they avoid the annoying ORA-29913, ORA-29400, and KUP-04040 error stack.

I’ve added a page to describe the complete set of steps … The instructions let you avoid the error stack by verifying the existence of the file first.

San Francisco was nice as always but I thought a quick view of the natural beauty of Idaho and Wyoming would be nice to share. I took this during our lunch break at a faculty meeting. The valley in the distance is the northern edge of Star Valley in Wyoming.

I also wanted to let you know that I replaced the text returns from hierarchical queries with screen shots. I think they better illustrate how you navigate trees in recursive tables. You’ll find them in the Hierarchical Queries Basics page if you’re interested.

I forgot to post the note from Alan Shook, a friend from my days in Oracle Support and teaching at Regis University. Alan works for Quest software, and he works with the Stat for E-Business Suite product. He’s passionate about the product, as you can see in his note. Alan has contributed two appendices on leveraging Quest’s tools to a book that Scott Mikolaitis and I are writing about the Oracle eBusiness Suite. Beyond the upgrade, tools are critical to cutting costs when you convert data.

Alan sums it up by …

“Stat is an Application Change Management product from Quest Software that supports the entire change lifecycle of the Oracle E-Business Suite. It is a single-integrated solution for issue tracking, process control and workflow, version control, migrations, recovery, patch management, auditing, and reporting. Stat provides native support for Oracle flat files, Developer objects, AOL object types, and a variety of application setups. Stat leverages its change management architecture to automate the manual steps required in patching and migrations while simultaneously helping you resolve process control, auditing, reporting and security concerns. The product has a security model to prevent unauthorized changes from being migrated through restricting access to do such task and limiting the rights to do so to those individuals you identify responsible for such actions. The change management process can be enforced with Stat’s workflow engine, incorporating approval requirements while providing full audit trail. Stat conducts all activities in an Oracle-supported manner, hence why it is validated by Oracle for R12 and for Oracle On-demand environments.”

I’ve added the Windows static IP networking steps to the How to configure Mac OS X as an Oracle Client. I’ll try to get back and update the entry for a couple Linux distros (distributions) next week.

If you get the DNS configuration wrong but everything else right, you’ll be able to connect to Oracle. Unfortunately, you won’t be able to make a call out of the VM with the browser.

Key Note and News:

There were two big announcements today. The one that I liked the most was what Andy Mendelsohn said, that there will be an Oracle Database 11g Express Edition. You can read the full report in the NY Times. You’ll also find that no date for 11gR2 is available but the beta code must be very close since they’re seeking beta testers.

The second announcement was the big “X” secret alluded to for Larry Ellison’s key note. Larry basically announced the HP Oracle Exadata Storage Server and HP Oracle Database Machine. There seems to be some cynicism about it, but it sounds interesting.

The general idea is to tightly couple hardware and software platforms. Integration of the two components simplifies delivery by reducing complexity, and increases performance for high throughput uses of the Oracle Database. It really makes sense.

For example, if you look at Steve Jobs’ success at Apple (this time around) that’s exactly what he did. He simplified the number of offerings, focused on the quality of hardware and software, and limited the risk of hardware and software incompatibility. Apple seems to clearly dominate the highend market, or at least it made an awesome showing at Oracle OpenWorld 2008 (by my anecdotal eyeballing of machines).

The last point is very close to what Larry’s proposing. By focusing efforts on high load OLTP and data warehouse installations with a one-two punch of integrated hardware and software, Oracle can become more scalable without creating different database engines. More or less, Oracle achieves higher throughput by leveraging hardware with the same product. This means he leverages a great product across new markets (probably IBM’s existing markets), without increasing the number of offerings. He one-ups Steve Jobs because he makes the hardware HP’s problem. Deming (that quality engineering guy) might say Larry risks success by ceding hardware components to a supplier. I don’t think the risk is any greater than the risk Apple has in ceding component manufacturing. The question is: Can HP manage the process as well as Apple now that Carly Fiorina is gone?

Humorous or prophetic, does this shift in platform have a hidden benefit as great as changing from the PowerPC to Intel processor. I don’t see it but this may be a beginning … What I can see immediately, is that configuring a RAC environment may become much easier if everything is prebundled.

Virtualization:

As you know from my blog posts, I really like virtualization software. So, … it’s probably not a surprise that I spent some time talking virtual machines with my favorite vendor – VMWare.

While hanging there and collecting more information on options, I met Dave Welch. Dave is a consumate maven of virtualization with Oracle and Oracle eBusiness Suite deployments. He had some awesome performance statistics that I’m still digesting. Basically, he debunked a lot of rubish that’s out there about overhead. He works for House of Brick Technologies out of Omaha, Nebraska. Dave actually demonstrated VMWare Stage Manager and VMWare Lab Manager, and told us about the bundling of these two into a suite. They appear a great soltuion to managing and deploying virtual machines. If you’re wondering were the VMWare folks were, don’t worry they were busy somewhere else.

Probably the coolest feature that I saw was how you could clone an Oracle eBusiness Suite for testing and move it to a virtual subnet without cloning. You can do this because the virtual subnet preserves the hostname, IP, et cetera. However, it’s not a cheap date but they’ve a reduced price for next few months. Isn’t that marketing the rule for products at Oracle OpenWorld?

While I like and use VMWare, Sun Microsystems VirtualBox appears very interesting. It also supports the Mac, which makes it a high priority in my testing queue. I’ll let you know what I find. The RedHat entrant in virtualization is Solid ICE. Another item for my list, oh gosh … it keeps on growing.

Business Judgment:

I have to give a business acumen prize to Wells Fargo Bank. Having spent the first 10 years of my career at First Interstate Bank working on the evolution of banking systems, I learned the key rule about technology. Technology has to pay for itself by increasing revenue or decreasing costs.

Wells Fargo Bank focused on an API that lets commercial accounts leverage banking services without human intervention. Their focus is to put the technology out there and leave their customers with the implementation costs, customization, and business process engineering. A sharp decision from my perspective but they could have gone one step further. They should have considered placing it in the open source community. The software ultimately has a very short competitive edge, and the return on initial investment would have been higher if they’d capitalized on the creativity of the open source community.

The prize for continuing product evolution through the open source community probably goes to SugarCRM. They place components into their product versions and then into their version of sourceforge (www.sugarforge.org). There, the community can examine, innovate, and improve the software.

Open Source:

I had a chance to catch up with Chris Jones on PHP and open source scripting. Chris and Alice Holloway provide that wonderful book on PHP known as the Underground PHP and Oracle Manual. It’s great to catch up on Oracle’s commitment to Open source. Remember my favorite news of the day – Oracle Database 11g Express Edition is coming. That’s evidence that Oracle sees the open source community as important and PHP and scripting languages as valuable.

In summary, I’d like to thank TUSC for their courtesy in broadcasting Larry’s key note from their demonstration area. They’re truly a class act! Tomorrow, it’s time to go home. Naturally, I gave the party ticket away to somebody who would enjoy it. The last official day of Oracle OpenWorld 2008 must be left to another to report on …

Day 2 is a wrap. As we left AT&T Park, the Giants’ were behind by one run. It was a hard call who I wanted to win because we lived in Colorado for 7 years cheering for the Rockies. It was a wonderful ball park, and the first time I’ve made it to a Giants game since they played at Candlestick Park.

The publisher’s seminar was very informative about direction and product. The summary may surpise some, but ultimately they seem to make good sense. Of course the big one is withheld until Larry delivers his keynote. I’ll report it as soon as I hear it.

The following summarizes what I heard:

Cloud Computing
Cloud computing is definitely on the menu for Oracle. Oracle has an FAQ for Cloud computing here. An exisitng API supports it. You can use Enterprise Manager to backup to Amazon EC2, but first you must run the Cloud Backup Installer. It uses the EM tape backup, which extends a previous API that existed for tape drives.

APEX
APEX is popular (oh, yes that’s true). Rumor has it that there are 250,000 developers using the product. APEX is also in the same space as Oracle Forms, and APEX will provide an Oracle Forms’ conversion tool to an improved Web 2.0 solutions.

SQL*Developer
Oracle statistics indicate there have been 1.5 million downloads of SQL Developer. While Quest seemed to feel that Toad licenses haven’t declined. Quest also mentioned that they might consider porting Toad to the Mac OS, but that’s a second subjunctive that’s technically a hypothetical remark.

The announcement today on SQL Developer was that it will soon include a data modeling tool. The up or downside on that announcement is that the ERD’s look like the return of Oracle*Method (why not? ;-)). Running down the other perspective on SQL*Developer let me connect with Steven Feuerstein again this year. By the way, he’s got a new PL/SQL book that’ll be released some time late next year.

Oracle 11g Upgrade Oppotunity
The discussion today covered that the cost of upgrading from Oracle 9iR2 to 11gR1 is less than an upgrade from Oracle 9iR2 to 10gR2. The reason for the change are those long overdue improvements in how upgrades work in Oracle 11g. Though there was mention that the first major patch of Oracle 11g is due shortly after OpenWorld 2008 (yes, that means October or …. maybe November). Oracle also mentioned that 5% of customers have upgraded to 11gR1 without the major patch. Oracle expects another 10% of customers to upgrade to 11gR1 after the patch release. A significant feature of the upgrade process is the automatic migration of all execution plans. I have to also champion the utility of the new Automated Database Diagnostic Manager (ADDM) in the Oracle Database 11g because it is sweet!

Java Developers working with Oracle
The numbers thrown out were 100,000 developers in 2005 and approximately 1,000,000 today. It seems Java inside the Oracle database and integrated through the JDBC with Oracle has always finally found its place in the mainstream Oracle community.

That’s about all the key points from the publisher seminar.

Visiting vendors is always important. RedHat is a big stop each year. This year was a pleasant surprise because they’ve got a new centrally managed identity, policy, and audit information product (IPA) for Linux and Unix environments. It uses open technologies and standards, including LDAP and Kerberos. They also have an open source virtualization software – Solid ICE.

Jim Brennan, Senior Product Manager, gave us a great demonstration of the Red Hat Enterprise IPA tool. It was impressive in how simply it works and I could easily see how I could leverage it to manage the student accounts on the servers I administer.

That’s it for Day 2 … the other tidbits will have to fade away. I apologize for it being late but it took a while to make it back to the hotel.

I just finished my book signing. Alas, no body showed up. Rumor has it books sales are down yet again this year. I guess that’s because folks search the web.

I think one of the mystery photographers caught me sitting at the table while I was waiting for the throngs of folks to buy my latest PL/SQL book (obviously in some dream state). Since I’ve heard Oracle Database 11g adoption rates are low, at least from a few vendors here at OpenWorld, perhaps I can blame that.

Things are much the same as they were last year, the year before, et cetera. However, the real business of the conference occurs in the Game Room of Moscone West. As you can see the budget dollars of organizations are well spent here.

Hopefully, there aren’t too many managers who visit my blog. These pictures probably shouldn’t be seen by management. Especially, if they’re the ones approving the expense reports, eh? I hope that nobody gets busted from these photos. 🙂

After the soda and snack here in the OTN lounge, I’m Off to a 5:30 p.m. session. Then, I’m heading over to the OTN Night at the Hilton in San Francisco. It starts at 7:30 p.m. this evening. We’ll only stay about 30 minutes or so to visit and then off to check in at the hotel.

Also, thought some of those taking classes at night or in college would be interested to know about the iPhone Developer University Program. It’s new and slick. We’ve actually got a course up and running this term at BYU – Idaho. A couple students are already working for a company developing iPhone applications remotely. I think that they’re calling it insourcing to Idaho.

A Google search that showed up in my statistics wanted to know how to check for external table file sources. I’ve got a sample that should be posted later this evening. If there are things you’re interested in that I’ve not explored, let me know. I always like new ideas but time typically goes to the projects at hand.

The reason for the Mac OS X configuration material is because I’ve been building a server to run multiple instances of the Oracle Applications 11i eBusiness Suite for the book I’m currently working on. Along with that, I’ve been documenting configurations for OPAL (Oracle, PHP/Perl/Python, Apache, Linux) stacks on various platforms. I’ll post my own version of Ubuntu later this week or on the weekend. Unfortunately, I’ve got some real work developing activity diagrams, and they’ll compel my attention this week between sessions and at the hotel.