Solved

Oracle Merge statement

Posted on 2013-01-10
7
1,269 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
7 Comments
 
LVL 142

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 76

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now