From oracle-l-bounce@freelists.org Mon May 17 08:03:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i4HD2ma30870 for ; Mon, 17 May 2004 08:02:59 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i4HD2c630767 for ; Mon, 17 May 2004 08:02:48 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3344A72C803; Mon, 17 May 2004 07:51:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 02960-40; Mon, 17 May 2004 07:51:44 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id ED14E72C7F7; Mon, 17 May 2004 07:51:42 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 17 May 2004 07:50:27 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E016172C52C for ; Mon, 17 May 2004 07:50:26 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 32261-100 for ; Mon, 17 May 2004 07:50:26 -0500 (EST) Received: from albatross.mail.pas.earthlink.net (albatross.mail.pas.earthlink.net [207.217.120.120]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8B76472C37E for ; Mon, 17 May 2004 07:50:26 -0500 (EST) Received: from bigbird.psp.pas.earthlink.net ([207.217.78.244]) by albatross.mail.pas.earthlink.net with esmtp (Exim 3.33 #1) id 1BPhom-0002dV-00 for oracle-l@freelists.org; Mon, 17 May 2004 06:06:08 -0700 Message-ID: <157955.1084799168118.JavaMail.root@bigbird.psp.pas.earthlink.net> Date: Mon, 17 May 2004 09:06:08 -0400 (GMT-04:00) From: david wendelken To: oracle-l@freelists.org Subject: Odd situation, any ideas? Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Mailer: Earthlink Zoo Mail 1.0 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 697 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: davewendelken@earthlink.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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 -----------------------------------------------------------------