Odd situation, any ideas?

From: david wendelken <davewendelken_at_earthlink.net>
Date: Mon, 17 May 2004 09:06:08 -0400 (GMT-04:00)
Message-ID: <157955.1084799168118.JavaMail.root@bigbird.psp.pas.earthlink.net>

I've had a very odd situation come up while coding a pl/sql program.

I'm hoping someone will point out the obvious ...

Here's the background:

I have a view that selects one partition out of a partitioned table.

Basically, it's "select * from the_table where the_partition_column = 1".

It has an instead-of trigger that kicks in on inserts. The trigger adds the correct value to the_partition_column and issues an insert statement. (The application doesn't know about the partitioning column. Long story.)

Pretty simple, eh!

I have lots of tables that need code like this, so I wrote some simple scripts against user_tab_columns to build the views and instead of triggers.

My pl/sql program that had problems had an "insert into the_view select ... from" statement in it. One of the columns that the select statement loaded had 5 distinct values in the original data, but it loaded all the rows with just one of those values.

At first, I thought I had gotten the column lists in the two parts of the statement out of whack. Lots of columns in the table, so it would have been easy to do.

I moved the trouble column up to the front of the statement, verified it was in the same slot in both halves of the statement, and tried again. Same wrong results.

I then changed the statement to point to the underlying table (instead of the view) and got correct results. Aha! Must be the instead of trigger.

So, I wrote several statements using the "insert into the_view values" format. Got the correct results! Which means the instead of trigger wasn't the problem.

So, where is my error?

It could be in one of several places.

The view definition.
The instead of trigger.
The "insert into select from" statement.

But the view correctly shows the data in the underlying table for that column. The instead of trigger works correctly when an "insert into values" statement is used, so it must be ok. The "insert into select from" works correctly when pointed to the table instead of the view.

I had someone look over my shoulder as I repeated the tests the second time and they couldn't figure it out either.

Here's hoping someone points out the obvious thing I overlooked! :)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Mon May 17 2004 - 08:03:04 CDT