Solved

Oracle Merge statement

Posted on 2013-01-10
7
1,324 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

732 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