Home » SQL & PL/SQL » SQL & PL/SQL » Error while executing the MERGE statement (merged)
Error while executing the MERGE statement (merged) [message #415969] Thu, 30 July 2009 00:27 Go to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Hi All,

When I try to execute the below MERGE statement

 MERGE INTO summary.osfi_feed_core_data_h h
   USING (SELECT   a.ntrl_k_odc_instrument,
                   LAST_DAY
                      (MIN (account_write_off_posting_date)
                      ) account_write_off_posting_date,
                   MAX
                      (account_write_off_posted_amt
                      ) account_write_off_posted_amt
              FROM landing_h.mortgage_dwo_cr154 a
             WHERE a.account_write_off_posting_type = 'DR'
               AND a.account_write_off_posting_date =
                      (SELECT MIN (b.account_write_off_posting_date)
                         FROM landing_h.mortgage_dwo_cr154 b
                        WHERE b.ntrl_k_odc_instrument = a.ntrl_k_odc_instrument
                          AND b.account_write_off_posting_type = 'DR')
          GROUP BY a.ntrl_k_odc_instrument, a.account_write_off_posting_date) mdwor
   ON (    mdwor.ntrl_k_odc_instrument = h.ntrl_k_odc_instrument
       AND mdwor.account_write_off_posting_date = h.month_end_date)
   WHEN MATCHED THEN
      UPDATE
         SET h.recovery_amt =
                  NVL (h.recovery_amt, 0)
                + NVL (mdwor.account_write_off_posted_amt, 0)
   WHEN NOT MATCHED THEN
      INSERT (ntrl_k_odc_instrument, month_end_date, row_source, recovery_amt)
      VALUES (mdwor.ntrl_k_odc_instrument,
              mdwor.account_write_off_posting_date, 'MTGDWO',
              NVL (mdwor.account_write_off_posted_amt, 0));



in the below environment.

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for 32-bit Windows: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production


regards,
Nataraj.

[remove surplus CR/LF]

[Updated on: Wed, 27 January 2010 03:19] by Moderator

Report message to a moderator

Re: Error while executing the MERGE statement [message #415977 is a reply to message #415969] Thu, 30 July 2009 00:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please post problem 3rd time to be sure I understand it.
Re: Error while executing the MERGE statement [message #415979 is a reply to message #415969] Thu, 30 July 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which error?
How can we help with what you posted?

Use SQL*Plus and copy and paste your session.
Remove empty lines.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Error while executing the MERGE statement [message #415983 is a reply to message #415979] Thu, 30 July 2009 00:54 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member


I 'm getting the below error.

Quote:
MERGE INTO summary.osfi_feed_core_data_h h

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01791: not a SELECTed expression





Pls Help

regards,
Nataraj.

Re: Error while executing the MERGE statement [message #415986 is a reply to message #415983] Thu, 30 July 2009 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Use SQL*Plus and copy and paste your session.

WHOLE session.
And use code tags.

Regards
Michel
Re: Error while executing the MERGE statement [message #415989 is a reply to message #415986] Thu, 30 July 2009 01:31 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Actually I'm giving support to the onsite so I'cant list the session details here.
Re: Error while executing the MERGE statement [message #415990 is a reply to message #415989] Thu, 30 July 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I wanted to know is: do you get this error from SQL prompt or from a PL/SQL block or procedure?

Regards
Michel
Re: Error while executing the MERGE statement [message #415991 is a reply to message #415990] Thu, 30 July 2009 01:34 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

Miche,
This is an independent DML statement.
Re: Error while executing the MERGE statement [message #415998 is a reply to message #415991] Thu, 30 July 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the error likely comes from a trigger.

Regards
Michel
Re: Error while executing the MERGE statement [message #416044 is a reply to message #415998] Thu, 30 July 2009 04:50 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

I had used no triggers in the Query.
Re: Error while executing the MERGE statement [message #416048 is a reply to message #416044] Thu, 30 July 2009 04:59 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You may not have used trigger but the table that you are updating may already have trigger written on it and that trigger may be causing the issue

Check in user_triggers if there are any triggers defined on this table.
Re: Error while executing the MERGE statement [message #416109 is a reply to message #416048] Thu, 30 July 2009 08:59 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

I dont see any triggers on this table.

this same query is running fine in Oracle v10.2.0.4

[Updated on: Thu, 30 July 2009 09:00]

Report message to a moderator

Re: Error while executing the MERGE statement [message #416111 is a reply to message #416109] Thu, 30 July 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search again, it is the only reason I see for an error at recursive level. Maybe it is a FGA or VPD trigger.
Try other and simpler MERGE statement on the table to get the root of the problem.

Regards
Michel
Re: Error while executing the MERGE statement [message #416113 is a reply to message #416111] Thu, 30 July 2009 09:05 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

ORA-00604: error occurred at recursive SQL level 1

ORA-01791: not a SELECTed expression

from the above two errors, which one is causing the actual error and why since the same query is running fine in Oracle v10.2.0.4, wat could be the reason.

Re: Error while executing the MERGE statement (merged) [message #416114 is a reply to message #415969] Thu, 30 July 2009 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
No Operating System name or version
No Oracle version number (SELECT * from v$version).
Re: Error while executing the MERGE statement (merged) [message #416118 is a reply to message #416114] Thu, 30 July 2009 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 30 July 2009 15:16
No Operating System name or version
No Oracle version number (SELECT * from v$version).



Read the first post.
Re: Error while executing the MERGE statement (merged) [message #416119 is a reply to message #416114] Thu, 30 July 2009 09:22 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

11:07:56 SQL> select * from v$version;



BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE 10.2.0.3.0 Production

TNS for 32-bit Windows: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production



Elapsed: 00:00:00.01

Re: Error while executing the MERGE statement [message #416126 is a reply to message #416113] Thu, 30 July 2009 10:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rtnataraj wrote on Thu, 30 July 2009 16:05
ORA-00604: error occurred at recursive SQL level 1

ORA-01791: not a SELECTed expression

from the above two errors, which one is causing the actual error and why since the same query is running fine in Oracle v10.2.0.4, wat could be the reason.

Michel Cadot wrote on Thu, 30 July 2009 09:17
So the error likely comes from a trigger.

Regards
Michel

Michel Cadot wrote on Thu, 30 July 2009 16:01
Search again, it is the only reason I see for an error at recursive level. Maybe it is a FGA or VPD trigger.
Try other and simpler MERGE statement on the table to get the root of the problem.

Regards
Michel


Re: Error while executing the MERGE statement (merged) [message #416189 is a reply to message #415969] Fri, 31 July 2009 01:40 Go to previous messageGo to next message
rtnataraj
Messages: 102
Registered: December 2008
Senior Member

let me know if you have any references to FGA or VPD triggers
Re: Error while executing the MERGE statement (merged) [message #440802 is a reply to message #415969] Wed, 27 January 2010 01:16 Go to previous messageGo to next message
Zinger_Der_GOD
Messages: 2
Registered: January 2010
Junior Member

Well, if anyone will meet the "problem", here's the answer Smile

Problem is the version... To fix it - do upgrade to 10.2.0.4, and that should do the trick...

Proper patch name for this: p6810189_10204_<YOUR_SYSTEM>-<YOUR_SYSTEM_MODE>.zip

I was unable to get it anywhere else, but from my local oracle partner Sad
If you will download it from the WEB - you have to realize, that it might contain something that has nothing to do with the patch itself. 2 of 3 i got from the web differed from right one.
Re: Error while executing the MERGE statement (merged) [message #440820 is a reply to message #415969] Wed, 27 January 2010 03:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only time I've ever seen ORA-01791 is when you have a SELECT DISTINCT and and ORDER BY clause that doesn't contain one of the expressions from the SELECT list.
I'd post the actual error text, but it's not available in the online documentation (as far as I can see).

I'd agree with @Michel that the most likely suspect for this is a trigger on the summary.osfi_feed_core_data_h table.

Points:
1) Triggers on that table can be owned by other schemas, and you might not have the privileges to see them.
2) Is that a table or a view?
Re: Error while executing the MERGE statement (merged) [message #440842 is a reply to message #415969] Wed, 27 January 2010 05:57 Go to previous message
Zinger_Der_GOD
Messages: 2
Registered: January 2010
Junior Member

Well i got ORA-01791 on my new base, that was ported on new hardware, and NOT patched to 10.2.0.4. I tested same select statement on 10.2.0.1 and 10.2.0.4 (old HardWare). On 10.2.0.1 I had ORA-01791, and on 10.2.0.4 - i had no errors ^_^. So i concluded that it was patch ^_^.
Looks like that in 10.0.2.4 - it is not an error anymore...

[Updated on: Wed, 27 January 2010 05:58]

Report message to a moderator

Previous Topic: why DDL statements are autocommit
Next Topic: SQL QUERY TO ROLLOVER PRIOR YEAR VALUES TO CURRENT YEAR
Goto Forum:
  


Current Time: Fri Dec 09 02:08:29 CST 2016

Total time taken to generate the page: 0.14735 seconds