| MERGE problem with FORALL on 9.2 [message #8635] |
Tue, 16 September 2003 06:08 |
John Geraghty
Messages: 1
Registered: September 2003
|
Junior Member |
|
|
Having a problem with the MERGE statement on 9.2...
Running a MERGE command under SQL+ will allow me to UPDATE or INSERT as expected. However if I try to use the MERGE command within a FORALL then the Oracle instance crashes with an ORA-600 error.
I remember reading in earlier documentation that FORALL will only supoprt INSERT, UPDATE and DELETE; though can anyone tell me whether that has now changed?
The logic is that I have two arrays containing code() and name(). If the code is already present in my TITLE table (with columns TITLECODE and TITLENAME) then I want to update the TITLENAME column, otherwise I want to insert a new row.
The code I'm using is:
FOR ALL i IN 1..arrayubound
MERGE INTO TITLE datatarget
USING
(SELECT code(i) TITLECODE FROM DUAL) datasource
ON(datatarget.TITLECODE = datasource.TITLECODE)
WHEN MATCHED THEN
UPDATE SET datatarget.TITLENAME = name(i)
WHEN NOT MATCHED THEN
INSERT (TITLECODE, TITLENAME)
VALUES (datasource.TITLECODE, name(i));
commit;
Does anyone have any ideas? If I can get this to work it would provide me with a neat solution to my requirements.
Thanks in anticipation
John Geraghty
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
