Oracle SQL Merge Statement Issue

Question

I have a sql that looks more or less something like the below one -

declare 
  some_Date date;
begin
select sysdate into some_Date from dual ; 
merge into 
  (SELECT cola, colb, colc FROM TableA  WHERE status='A'  AND some_id  = 101) P 
  USING ( select some_Date as cola from dual  ) S ON (P.cola=S.cola)
WHEN MATCHED THEN
  UPDATE SET P.status = 'D'
WHEN NOT MATCHED THEN
  INSERT
    (  cola, colb, colc    )
    VALUES
    ( xxx, xxx, xxx   );
end ;

When I run the above query by replacing the some_date with sysdate directly, it executes with no error. But I replace the sysdate inside the using clause and try to use it dynamically I get the following error. I get an exception as following -

Error report:

ORA-00600: internal error code, arguments: [qcsfbdnp:1], [B1], [], [2], [], [], [], [], [], [], [], []
ORA-06512: at line 5
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
           exceptions.  This indicates that a process has encountered an
           exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

Is this a know bug in oracle? What would be the alternate?

Update: Fixed a typo!

Should the USING clause be returning a column named cola? In the statement above it's returning a column named cala. Probably just a typo but perhaps worth mentioning... — Bob Jarvis - Reinstate Monica
Every ORA-600 is a bug. If you look it up on My Oracle Support you will quickly find the answer to this problem. (Sorry for not posting those details here, but it's not entirely clear what the rules are for sharing that information.) — Jon Heller

haki haki · Accepted Answer · 2013-05-16T13:40:17

try this

merge into TableA  P 
USING ( select some_Date as cala from dual  ) S 
ON (P.cola=S.cola)
WHEN MATCHED THEN
  UPDATE SET P.status = 'D'
WHERE status='A'  AND some_id  = 101
WHEN NOT MATCHED THEN
  INSERT
    (  cola, colb, colc    )
    VALUES
    ( xxx, xxx, xxx   );

Oracle SQL Merge Statement Issue

1 Answers