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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented May 16, 2013 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
– Jon Heller, Commented May 17, 2013 at 4:41

haki · Accepted Answer · 2013-05-16 13:40:17Z

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, 2013 at 13:40

haki

9,81115 gold badges67 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jagamot Over a year ago

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.

haki Over a year ago

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.

Collectives™ on Stack Overflow

Oracle SQL Merge Statement Issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related