Michael McLaughlin's Blog (16)

Looking forward to meeting old and new friends at OOW11. If you want to stop by, I'm in the bookstore at Moscone North on Monday and Wednesday at 9 a.m. for a book signing.

I was doing some testing tonight, and discovered something curious on Oracle 11.1.0.7.0. Basically, if you add a CLOB column through an alter statement, drop the table, recreate the table, and try to alter the table again to add the CLOB column it's still there. It is like the table was never dropped, but DBMS_METADATA.GET_DDL and the DBA_LOB and DBA_SEGMENT views don't find it before you try to re-add the column. When you re-create it, you get an ORA-01430. After the error is thrown,… Continue

This is just a thought about making our information more accessible. I've adopted a print.css on my main blog to expand all content on an entry and strip unnecessary header, footer, and sidebar information.



Aside from the striping the useless page stuff, a print.css let me unfold all my entries. This was important since I adopted JQuery folding on the pages based on feedback from readers. The folding forces me to reflect a bit more before I post something and hopefully avoids… Continue

When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. I updated my old entry, more or less rewrote it entirely, now there's a 16 page post. Hope you enjoy it.



Also, about a month or so ago I began tweeting. You can find me at MacLochlainn… Continue

I've moved to my own domain for reasons set out in the last blog post on the old site and first directly on the new site. If you've got an iPhone give it a hit and then reflect on using the iPhone plugin on your own blog. I've migrated all the content to the new site and eventually things should forward but I wanted to give you a heads up if you've set an RSS feed to the old site. The new site is… Continue

I had some interesting exchanges with folks at Oracle. They advised me that the blog post for configuring a Mac to work with the Oracle 10gR2 client can't be referenced because VMWare isn't supported. They suggested that I run Oracle VM. I was stunned since there were two points in the blog post. One is running Oracle Client 10gR2 on a Mac against any Oracle instance. Two is how to create a virtual Windows server to have a test environment anywhere you go.



On their suggestion, I've… Continue

A number of my students said the documentation wasn't there to create a Mac OS X client installation. Well, I put it out there now. It walks you through the Oracle specific steps for SQL*Plus and SQL*Developer.
You can find the 14 magic steps here.

Somebody asked for a URI reference to the PL/SQL code for my Oracle Database 11g PL/SQL Programming book. You can find it here on the McGraw-Hill site. If you find any glitches, just… Continue

While experimenting with some of the internals in the XDB schema, I needed to discover whether some of the XDB collection types were varrays or nested tables. It was tedious, so I wrote a script to discover the type of collection, the base type of the collection, and the limit of varrays. It creates a SCHEMA_COLLECTION_TYPES view.



If you're interested in discovering collection type details too,… Continue

I went too fast and installed Oracle Database 11g into a new Windows XP x64 virtual machine. I forgot to change the automatically generated hostname. It was a trade-off whether the time to rebuild the VM was less than fixing the Oracle Enterprise Manager. When you change the Windows hostname, you must drop and recreate the Enterprise Manager Repository, update the listener files, hostname, and hosts file.



I found it wasn't as clean as I thought when cruis'n down the street. That's… Continue

I posted an entry that shows you how to get the formatted words excluded by a STOPLIST in Oracle Text. I also took a shortcut and tripped an error linked to permissions. When I google’d it, I found there wasn’t a complete answer out there, so I put one here (at least it is a start).

Over a decade ago, I got frustrated without any documentation on the Oracle E-Business suite schema. I wrote a utility to snag a formatted and alphabetized list of tables with their columns and datatypes. I've updated it and put it on my other blog.



I also updated the External File Basics to… Continue

I put quick article together to demonstrate how you can convert XML source documents to CSV files at the command line. It also shows you how to configure and use Apache Xalan (an XSLT Processor) to transform the data. Then, a SQL*Loader external table uploads the CSV file to the database.

External files are great tools for reading data into and writing data out of an Oracle database. You have two options for reading data into the database. One uses SQL*Loader and the other uses Oracle Data Pump. You have only one option to write data from the database into an external table file, and that’s Oracle Data Pump.



I thought this was pretty straightforward when recommending it as a solution to my Data Warehousing students. Given the questions that I got back, it appears that… Continue

Last month I also presented the Oracle Database LOBs In-depth at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides and sample programs here. The presentation includes scripts for uploading LOBs from the file system and across the network via PHP. A quick process for reclaiming lost space from LOBs is… Continue

Last month I presented the Oracle Database 11g PL/SQL New Features at the Utah Oracle User’s Group (UTOUG) Training Days. You can find the slides here. My favorite new feature is that you no longer have to select a sequence into a variable to use it in a SQL statement in a PL/SQL block.