The meaning of ORA-29907 error and the fix

2008/03/19

I have first time encountered the error:

ORA-29907: found duplicate labels in primary invocations

after testing the change request we have implemented recently. The error message was not very helpful – I was not exactly sure which labels and which invocations 😉 – and as Oracle guru who actually does understand fine details of Oracle Text was visiting sunny India, I was on my own.

The change addressed an issue in repository search: users were asking for more flexible processing of full text search phrases: instead of e.g. searching for “unit test” search for document that contains both unit AND test. The text was parsed in Java and the SQL generator produced instead of something like


SELECT * FROM DOCUMENT d1 WHERE ROWNUM <= 500
AND  ( CONTAINS(d1.document,'unit test',1) > 5)


SELECT * FROM DOCUMENT d1 WHERE ROWNUM <= 500
AND  ( CONTAINS(d1.document,'test',1) > 5)
AND ( CONTAINS(d1.document,'unit',1) > 5)

I am still unsure what primary invocation is, but the above mentioned ‘label’ was the number one. After changing the generator to issue sequence numbers in the CONTAINS, everything worked OK:


SELECT * FROM DOCUMENT d1 WHERE ROWNUM <= 500
AND  ( CONTAINS(d1.document,'test',1) > 5)
AND ( CONTAINS(d1.document,'unit',2) > 5)


Oracle SQLDeveloper and mystery of the missing host

2008/02/23

If you work with Oracle database, a very good tool you should not ignore is the Oracle SQL Developer. It gives you much more power when it comes to manipulating data or managing database. I use most of the time MyEclipseIDE plugins, but when I need e.g. export data, the SQLDeveloper is way to go.

Today I tried it for first time on the Mac. I started to configure and database connection and was completely confused by the error message “Port must contain numerical value”. Here was the dialog box:

picture-6.png

I typed the hostname:1521 but the error message kept coming back. There was no obvious way how to put 3 values – host, port and SID into two field. After checking the documentation, I remembered that the Windows version did have 3 text fields instead of two.

What was the problem ? The layout manager in Java did unfortunately completely hide one of the 3 fields. After resizing the dialog, all fields would be visible:

picture-4.png

After this, everything worked OK.

Obviously, Mac version of the SQLDeveloper could use some minor improvement. For example, the menu looks everything but like a finished Mac application:

picture-5.png


The craziest requirement I’ve ever heard about

2007/10/15

Can you imagine a database table with 25000 (twenty five thousands) columns ? No, not rows, COLUMNS ! Now if you can do that, can you imagine displaying these columns in a datagrid (spreadsheet) on a screen ?

This is exactly what this guy is describing to be his requirements. I hope he did misunderstood or misread the requirements, because displaying 25000 columns on a screen is not only impossible – it is absolutely useless. How would the user interaction look like ? As one of the guys in the discussion pointed out, the table would be few hundred meters wide (assuming 1 cm cell and 19 inch monitor) ….

Monsters like this happens when the person responsible for WHAT needs to be done goes step further and defines his/her idea about HOW it should be done. I have seen it many times when otherwise very good functional analyst gets overexcited in the process and goes beyond domain model – makes the actual data model part of the spec – despite lack of data modeling or database skills. This is ususally a guaranteed way how to end with bad data model and lot of unnecessary complexity later on.

In this case, showing peak information about customer the key is slicing the time interval (to show just some subset of information), proper using of graphs and aggregation of the values with drill-down functionality to show the details.


Oracle 10g XE – nice surprise

2007/09/24

As part of current project I need to test some ORM frameworks and for that, you need a database. In this particular case, database equals Oracle. I need a local instance to be able to work outside of the customer’s location.

On my notebook, I have already installation of MySQL (for all the Rails stuff) and SQLServer (for ASP.NET). Plus few lightweight databases (meaning that they do not really need a server) such as sqlite3, Derby and HSQLDB). I was very hesitant to add another instance and for some time tried to do the work with MySQL – but differences in SQL and most importantly the way how the CLOBs and BLOBs are handled convinced me this is a bad idea.

So grudgingly I downloaded the Oracle 10g Express Edition – which is free and available for any use – including production deployment. Remembering my last installation was not very encouraging: the machine I installed the 9i suite (Windows 2000 back in 2002 or 2003) became close to unusable for development after install because of the memory consumption and CPU load of the database server – and after the installation I had to go and develop on different machine. I also remembered that it installed variety of services, two or three different instances of Apache (for no obvious reason) and lots of other stuff I had no need for.

I have to say that Oracle made a huge improvement since that time. The install went very smooth, did not mess up the notebook and everything actually works very nicely. It did not even ask the scary questions that one does not fully understand and later pay dearly for answering them wrong… The admin interface is web based and does not require you to read hundreds pages of documentation to make anything useful. It shows memory used and allows easily manage users, databases and pretty much everything what a developer (not a DBA) may want. If you need more or are an Oracle DBA, you have the dreaded command line based SQL tool available as well – so even Bob can feel at home with XE :-). The whole Oracle suite is now single service (more or less) and if you do not need it, you just stop single service (shortcut in Programs is provided) and claim the memory + CPU to where you need them.

Now I know that if you need a lightweight commercial grade database (backed with The Big Name), there is an alternative to SQL Server Express – very comparable with respect to footprint, resources, easy of use and limitations (from developer’s point of view unimportant such as 4 GB size limit on database). And as added benefit, you can install on Windows or Linux – which may come handy if you need to build a virtual database appliance without any licensing issues on the operating system side …