Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Merge statement

Posted on 2013-01-10
7
Medium Priority
?
1,377 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 334 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 334 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 332 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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