Oracle Observations

March 6, 2007

Problems encountered migrating to Oracle 8i

Filed under: 8i — bigdaveroberts @ 11:49 am

Another document that I produced long ago was the issues encounterd upgrading a small document management system to Oracle8i.

With hindsight, the fact that we also changed from the RBO to the CBO explains at least the first issue, but may also be implicated in the other two issues as well.

1 SQL Order of execution.

It has always been my experience that if there are no other factors to consider, Oracle will apply conditions in reverse order, executing the last where clause first. This no longer appears to be the case.

For example:

The table testdate has a column logdate that consists of either a single space or a date as a character string in YYYY MM DD HH24:MI format.

The following select statement works fine under Oracle 7 as all of the rows where the value is a single space are ignored by the last line.

Select count (*) from testdate
Where to_date(logdate, 'yyyy-mm-dd HH24:MI’) <> sysdate
And length(logdate) = 16 ;

However under oracle 8 the following error occurs:

ORA-01840: input value not long enough for date format

Indicating that the first where clause is executed on the rows first!

Note also, that an attempt to get round the problem was made by creating a view that excluded the rows containing a space, however this still failed as Oracle appeared to optimise the query and apply the to_date function on the fields that were not selected by the view!

2 Change in behaviour for invalid private synonyms.

In oracle 7, if an invalid private synonym (where the target object does not exist) and a valid public synonym with the same name exists, then the system will use the public synonym without raising an error. In oracle 8 an error is raised.

3  Ambiguous column names

You are no longer allows to have duplicate column names in a subselect. In oracle 7 a column name could be identical to a second column name as long as they were given separate aliases in the enclosing select statement.

I.e. the following worked under Oracle 7 but not under oracle 8:

Select description d1, description d2
From (select t1.description, t2.description
From t1, t2);
Select description d1, description d2                *
ERROR at line 1:
ORA-00918: column ambiguously defined

Under oracle 8 it would need to be rewritten thus:

Select d1 d2
From (select t1.description d1, t2.description d2
From t1, t2);
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: