Teaching SQL is interesting because folks try syntax that experience would tell you shouldn’t work. It was interesting when I discovered what should be broken from my perspective but was expected behavior in MySQL. It became clearer to me why it’s there as I did some experimenting with it enabled and disabled. While I’d still [...]

A Gauss posted a question on my

My students are into the upload lab and they’ve encountered the wonderful ORA-30926 error from a MERGE statement. An example of the MERGE for an upload is in this earlier blog post.
This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase [...]

This is to correct an earlier omission and provide instructions for configuring static and dynamic IP addresses for Virtual Machines runing on VMWare Fusion on Mac OS X. The benefit of static IP address is important for those installing multiple development and test instances of Oracle Databases in virtual environments.
Back in September 2008, I blogged [...]

In my second database class we focus on PL/SQL but we’ve begun highlighting the alternatives in MySQL and SQL Server. A student asked how they could debug runtime variable values in a MySQL Stored Function or Procedure (or subroutines according to some documentation).
In Oracle, we debug with the DBMS_OUTPUT package. Packages, like DBMS_OUTPUT hold related [...]

Somebody posted a quick question about the outcome of defining a table with a bool data type in PHPMyAdmin. They were subsequently surprised when they checked the MySQL database and found that it was actually a tinyint(1). The natural question they had was: “What do you enter – true/false or 1/0?”
I promised to post an [...]

I’ve been working on a Basic Aggregation tutorial for my students. I think this might be close to what may benefit them. However, I thought it would be great to put it out there and solicit ideas. If you have some on improving this post, please let me know.
My first take at the post …
This [...]

Of course there is no such thing as the "last" record in a relational database. Unless you have an ORDER BY in your query.
For the current project we needed to determine the last record in a set, because this record needs special treatment. One of my colleagues came up with a CASE expression combined with a LEAD function to determine this record.

First let's create a sample table - a simple one with just one column.

SQL> create table test
  2  as
  3  select rownum id
  4    from dual

After I wrote the post for students on the multiple row MERGE statement for an upload through an external table in Oracle, I thought to check how it might be done with MySQL. More or less because I try to keep track of how things are done in several databases.
MySQL’s equivalent to a MERGE statement [...]

The project that I'm currently involved in uses XML to send data to a reporting engine which parses the XML and creates a pretty PDF report.
In order to do this some "generic" packages were created to create the XML based on the content of a RefCursor. Works like a charm. Just call the function with a RefCursor and get an XML back.
The problem they initially had was that the XML returned uses the ROWSET and ROW tags, while they wanted different tags. No problem, some REPLACE function would do the job, and that worked fine. Until last week that was...

While working through examples for my students on uploading data, I thought it would be interesting to demonstrate how to create a re-runnable upload. Especially when chatting with a friend who was unaware that you could use joins inside the source element of a MERGE statement. Naturally, the MERGE statement seemed like the best [...]

During configuration phase of Grid Infrastructure for cluster, CVU failed while performing post-checks.
Following message is displaced in the Installation Log file:
Checking Single Client Access Name (SCAN)...

WARNING:
PRVF-5056 : Scan Listener "LISTENER" not running

Checking name resolution setup for "scan-test.abc.com"...

Verification of SCAN VIP and Listener setup failed
The LISTENER from Grid Infrastructure home is running fine:
$ ps -eaf | grep tns
/u01/app/grid_11.2/GridInfra/bin/tnslsnr LISTENER -inherit
Same error is observerd by manually runnig CVU as:
$ cluvfy comp scan
Verif

Somebody asked me about how you bind a parameter in mysqli when a multiple character wildcard needs to be next to the variable value. They’d found that it worked when creating a SQL statement by concatenation (string gluing), like this:

$sql = "SELECT item_title FROM item WHERE item_title LIKE '%".$title."%'";

However, they tried to bind the variable [...]

A couple months ago, I tried to install Oracle Database 10g, Release 2 for Mac OS X on Intel x86-64 on a MacBook running generic Snow Leopard. It didn’t work because you must have the Mac OS X Server. Somebody asked me to post more of what I found when I tried to hack the [...]

Lczinkoc’s Blog 

I got a request Saturday for me to post code for the Oracle Database 11g PL/SQL Programming Workbook. You can download the book code here. It should also be on the McGraw-Hill web site tomorrow.
The irony for me is the timing of the request. I didn’t get it until late Saturday night when I had [...]

I’d upgraded from VMWare Fusion 2 to 3 and taken care of most instances. A NASTY surprise awaited me when I tried to upgrade my Linux installations this morning.
You MUST to uninstall VMWare tools from your repository of Linux operating systems before upgrading your VMWare Fusion or ESX server. If you don’t, you can’t access [...]

My co-author got his author copies about a week before me. The technical editor got his complementary book too the same day. I was wondering where mine got routed but now I’ve got mine, a few days after it was available on Amazon.
I think the new book looks good. John and I were probably a [...]

I am creating 11gR2 RAC setup for one of my client.

If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one [...]

This is probably defensive because I’ve had to answer the question about two dozen times since the iPad product announcement. The question is naturally, what do yo think about the iPad?
My perspective is biased by the fact that I’ve been using both DOS/Windows PCs and Macs since the 1980s. They each have merits but in [...]

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.
Part of the idea behind these examples is to show the basic structure [...]

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.

The CASE statement, like the CASE expression, comes in two flavors: Simple and Searched. To illustrate, this is the Simple CASE

SQL> declare

New term for this month, borrowed from Algebra of Binary Relations: composition operation. Composition of two binary relations can be interpreted in Codd’s Relational Algebra (aka Algebra of Relations with Named Attributes) by taking their join, leading to a ternary relation, followed by a projection that removes the middle component. The less concise synonym for [...]

I was working on a code example for my database class, got in a hurry, and changed a table name without dropping the original table. Oops!
Naturally, I got this error message.

LOB (administrator_photo) STORE AS admin_photo
[...]

Before I start my post, I would like to wish all our readers a “Happy New Year”.
While reviewing a 10gR2 RAC configuration I faced following errors on invoking cluvfy utility
$ ./cluvfy stage -post crsinst -n prod01,prod02 -verbose

Performing post-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "prod01-fe"
Destination Node Reachable?
------------------------------------ ------------------------
prod01 yes
prod02 yes
Result: Node reachability

Alas, poor ereg() I abused you well. PHP 5.3 has deprecated ereg() and now we must move forward with preg_match(). Along with that change, somebody asked me to show how to upload images to the file system as opposed to the database. Personally, I think they should be stored in the database.
With my bias toward [...]

While reconfiguring my iMac, it was interesting to note the bumpy road to implementing Code Weaver’s CrossOver Plus 8.0.1 for the whole Microsoft Office Suite. Previously, it had only been used to support Microsoft Visio 2007.
I discovered that a Shutdown and Start operation was required after installing CrossOver Plus because a Restart hadn’t worked. Attempting [...]

Hi All,
This time I am sharing an experience from my personal life, I got Married on 2nd Dec 2009
I was on holidays since last month and just joined back.

Let SQL do the talking:

select decode
      (sign (floor (maxwidth/2) - rownum)
      ,1, lpad (' ', floor (maxwidth/2) - (rownum -1))
         ||rpad ('*', 2 * (rownum -1), ' *')
      ,lpad ('* * *', floor (maxwidth/2) + 3)) "Merry Christmas"
from all_objects
,    (select 40 as maxwidth from dual)
where rownum < floor (maxwidth/2) + 5

... and in case your wondering what the result would be, I added a screenshot.