Solved

Oracle Merge statement

Posted on 2013-01-10
7
1,310 Views
Last Modified: 2013-02-04
HI,

I have an oracle merge statement.

All components are correct individually, yet it fails on the insert with the following error:

SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-00905: missing keyword
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

It has worked in the past.

My select works.
The update works
the Delete works but not the insert.

It is being run from a package.

The syntax simplified massively is following standard merge structure.

Other items and packages designed the same way work:

merge into mytable
        using
        (
        my select
       
        )on (    joins
           )
        when matched then
          --script
         
          delete
          where      Del       = 'Delete'
        when not matched then
          insert (--columns )
          values (--cols);
0
Comment
Question by:wilflife
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
ID: 38762478
you DO have an error in the syntax ...
please more the non-simplified version (you may want to obfuscate the table/column names and data, but otherwise keep the sql as you use it.

my guess is that you are using bad aliases somewhere.
also, if it has worked "in the past", there must have something changed...
any upgrades?
how do you run the sql?
0
 
LVL 15

Assisted Solution

by:gplana
gplana earned 167 total points
ID: 38762549
As you way UPDATE and INSERT statements wroks independly, I suspect here is the issue, because INSERT and UPDATE clauses of MERGE statement has not exactly the same syntax as these MERGE clauses.

Please look here:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Particularly on merge_update_clause and merge_inser_clause syntax parts.

Hope it helps. Regards.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38762651
Please post the statement.  It will be impossible to assist with a syntax error without seeing the code causing it.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 32

Expert Comment

by:awking00
ID: 38767470
In addition to the complete merge statement, perhaps some sample data that is generating the error plus anticipated output would be most helpful.
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 166 total points
ID: 38795146
Just throwing idea. Check if you have this situation:-

CREATE TABLE temp_dev_1(a number, b number, "TABLE" NUMBER);
SELECT "A", "B", "TABLE" FROM temp_dev_1; -- Will Work

SELECT A, B, TABLE FROM temp_dev_1; -- Will Not Work
0
 

Accepted Solution

by:
wilflife earned 0 total points
ID: 38834869
We had a problem with our sequence rebuilt the table and it worked.
0
 

Author Closing Comment

by:wilflife
ID: 38850411
It was the solution.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MYSQL responding very slow 3 48
SQL Query help 3 24
Special characters in a TCPDF 4 24
SP inserts data with order number, then push the rest one order up 11 16
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question