Posts filed under ‘Oracle’

Toilet Stall Deadlock

A few years ago, I had a whim.  I wanted to write a book that explained database concepts in the terms of toilet, bathroom and excrement analogies.  For deadlock I had this example:

The bathroom has two stalls, each occupied by a person who needs to take a dump.  Person A is patiently waiting for the other occupant to leave before they release their bowels (and any accompanying sound effects).  Little do they know– Person B also needs to take a dump and is waiting for Person A to finish!  So both people are sitting on toilets, stifling urges and waiting for the other one to depart.  

Unfortunately that was the only example I came up with and as amusing as I may think it is, it doesn’t quite constitute a book.  But I did get to smile and think about it this morning when Derek and I ran into a deadlock* (the database type) this morning. 

We were trying to track down an Oracle issue, so we got into SQL*Plus and inserted some records into a particular table.  When we went to the application, our changes were not being displayed.  Worse even, when we tried to make changes to the same table through the application it hung indefinitely.  Oracle folks, know what we did wrong?

That’s right– SQL*Plus commands do not commit their changes until you close the tool or you explicitly type in commit; 

Our changes through the SQL*Plus tool were still in limbo so when the application went to make the its changes– it found itself patiently waiting for SQL*Plus to release the table so it could have the same privacy coveted by our bathroom goers.  SQL*Plus was lingering open, so our application waited and waited and waited… until Derek and I finally caught on.

Even if it doesn’t make for a book, perhaps one day you’ll run into some peculiar behavior with your Oracle database and SQL*Plus.  Out of frustration, you step away from your desk and take a trip to the bathroom and when you plop down on the seat– maybe you will recall this post and realize what is going on.  🙂

*Okay, so it isn’t an official deadlock because the SQL*Plus isn’t waiting for a resource the application has— but the segue was close enough me!

October 4, 2006 at 11:54 pm 1 comment

ORA-00942 on Data Moved with SQL Server Data Transformation Services

Here’s another little fun run-in with Oracle.  I used the Data Transformation Services in SQL Server Enterprise Manager (2000) using the Microsoft ODBC for Oracle as the driver for my transfer.  Everything ran successfully, however, when I logged into the database using the SQL*Plus tool and did a simple query:

SELECT * FROM MyTable;

I got the following error message:

ORA-00942: table or view does not exist

Now– when I queried the built-in All_Tables table (which by the way, that is a query I’ve found helpful on more than one occassion):

SELECT Table_Name, Tablespace_Name, Owner FROM All_Tables;

The results show me that my table is in the right tablespace and has the right ownership.  I confirmed the login I was using to SQL*Plus had view permissions to the table.  Everything seemed in order– what could be wrong?

Well, apparently there is something unique about the naming when SQL Server DTS writes tables to Oracle– quotes are around the table name (but not reflected in the All_Tables query).  Sure enough, if you run this query:

SELECT * FROM “MyTable”;

results are returned as expected.

The final solution is quite easy– just run a RENAME TABLE command from your SQL> prompt in SQL*Plus.

RENAME TABLE “MyTable” to MyTable;

And you are good to go.  Since I’m going to be migrating the same SQL database over on multiple occassions for testing purposes, I have a text file handy with all my rename calls.  I copy them in bulk to SQL*Plus and I’m off.

March 9, 2006 at 10:43 pm 1 comment

ORA-01722 with ODP and Command Parameters

This week I was working on a section of code using C#, the Oracle .NET Provider (ODP) and some good old parameters in a SQL statement.  The code was pretty straight forward (this is from memory without the assistance of intellisense– so apologies for any syntax errors):

string sql = “INSERT INTO MyTable (TextField1, NumberField2) :param1 :param2”;

OracleCommand cmd = new OracleCommand(sql, this.conn);

cmd.Parameters.Add(“param2”, OracleDbType.Int32);
cmd.Parameters.Add(“param1”, OracleDbType.nVarChar2);

cmd.Parameters[“param2”].value = 12;
cmd.Parameters[“param1”].value = “My Text”;

cmd.ExecuteNonQuery();

Everything seemed good to go, but when I executed the query, I got ORA-01722: Invalid number

When you check out the appropriate page on  http://www.ora-code.com it reads, “The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.” 

Well, my parameter value was obviously a number, so I thought I was picking the wrong field type for the parameter.  I tried all sorts of things– Int64, Int16, Long, etc.  I always got the same error.   Then I learned a helpful little tidbit about the OracleCommand object in ODP:

Named parameters are turned off by default.

So even though I’m referring to my parameters by name and setting their values by name– when the SQL command was running, it was plugging the parameters in by the order in which you add them to the command object.  So in the above example, my value for param2 was what was getting plugged in for param1 and vice versa.  As a result, my SQL was trying to insert “My Text” into a number field.  Yup, I’d say that’s an invalid number!

Luckily, ODP does support named parameters (don’t believe news group postings from 2003 that say otherwise)– you have to turn it on with a cmd.BindByName = true; call. 

 

March 3, 2006 at 2:52 am 12 comments


Flickr Photos

3D Printed Products

Tweets