As qualified 8 months ago, I moved the blog here. If you’ve comments on the posts here, please feel free to move them there.
You can find the new blog at: http://blog.mclaughlinsoftware.com
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 TechTinker.com 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!!!
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
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
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.
You’ll need to right click on the VMWareTools icon and manually extract the file. If you don’t and double click the icon instead, it’ll raise an exception that one already exists. After you manually extract it to your desktop, run the command as follows (replacing your user name of course):
# sudo /home/username/Desktop/vmware-tools-distrib/vmware-install.pl
I’d already upgraded my Windows x86 instances to 2.x on my Mac Book Pro but hadn’t got around to Ubuntu until today. The upgrade went fine, and VMWare Tools compilation succeeded and kernel reports were normal. Unfortunately, CUPS (Common Unix Printing Service) fails to start correctly and there doesn’t appear to be any way to fix it without changing the VMWare Fusion libraries. It appears to be a bug introduced by VMWare Tools. I’ve updated my Ubuntu VMWare printer set up steps to note it. If you’ve got a fix for the problem, let me know.
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 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:
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 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:
OUT mode also has an exception, which relates to
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.