Odd situation, any ideas?

From: david wendelken <davewendelken@xxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Date: Mon, 17 May 2004 09:06:08 -0400 (GMT-04:00)
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.

1) The view definition.
2) The instead of trigger.
3) 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Odd situation, any ideas?

Other related posts:
