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 - Слава Україні, May 16 '13 at 13:15
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, May 17 '13 at 04:41

score 2 · Accepted Answer · answered May 16 '13 at 13:40

2

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   );

answered May 16 '13 at 13:40

haki

9,389
15
62
110

can you please help me understand the difference? The some_Date in the Using clause is what I am trying to insert dynamically through a variable. And I see the above exception. – jagamot May 16 '13 at 13:44
the problem is in the `into` clause. you used a query where a table should be (think of it as trying to update a view ... possible, but only in a certain circumstances). you can use s.some_data in the insert and the update clause. – haki May 16 '13 at 13:54

Oracle SQL Merge Statement Issue

1 Answers1