Solved

Oracle Merge statement

Posted on 2013-01-10
7
1,281 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.

911 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

20 Experts available now in Live!

Get 1:1 Help Now