Pfthb

I have a small piece of code which has a sub query using the regexp_replace function to get a time stamp and this is inside a GREATEST function to get the highest date from this and a field in the outer query.

The data I am using in the sub query looks like this:

IDDate IDKeys
17-DEC-16 Q=INT^A_NO=123^
03-OCT-18 B_ID=123^
18-OCT-18 C_ID=123^
28-OCT-18 C_ID=123^

If I run this segment of code:

SELECT MAX(IDDate) GDATE
FROM HHL h
WHERE regexp_replace(IDKeys, '[^0-9]', '') = '123'
AND LEFT(IDKeys, 1) = 'C'

It returns the correct information

GDATE
28-OCT-18

However when I incorporate this into the outer query as a sub query like this:

SELECT DISTINCT
c.P_ID,
GREATEST((SELECT MAX(IDDate) GDATE
 FROM HHL h
 WHERE regexp_replace(IDKeys, '[^0-9]', '') = c.P_ID
 AND LEFT(IDKeys, 1) = 'C'), c.CIDDate) GCDate
FROM CICC c

I get the following error:

ORA-00600: internal error code, arguments: [kkqcsfixfro:1 -- frooutj], , , , , , , , , , , 
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
 exceptions. It indicates that a process has encountered a low-level,
 unexpected condition. The first argument is the internal message
 number. This argument and the database version number are critical in
 identifying the root cause and the potential impact to your system.

EDIT

This works if I use SUBSTR and INSTR functions rather than regexp_replace:

SELECT DISTINCT
c.P_ID,
GREATEST((SELECT MAX(IDDate) GDATE
 FROM HHL h
 WHERE SUBSTR(NVL(SUBSTR(IDKeys, 0, INSTR(IDKeys, '^')-1), IDKeys), INSTR(NVL(SUBSTR(IDKeys, 0, INSTR(IDKeys, '^')-1), IDKeys), '=') + 1) = c.P_ID
 AND LEFT(IDKeys, 1) = 'C'), c.CIDDate) GCDate
FROM CICC c

I suggest that you should create a service request to Oracle support. Seems like you face the Bug 19894622 - ORA-600 [kkqcsfixfro:1 -- frooutj] error occur in 12c (Doc ID 19894622.8). Affected software versions are Versions >= 12.1.0.1 but BELOW 12.2. You should at least verify with your dba what software version you have and if patching will solve the problem.

There is a walk around described in the note.

alter session set "_optimizer_unnest_scalar_sq"=false;

As you can see it refers to the hidden parameter which is set on the session level. If you want to set this parameter on instance level the best practice with such parameters is to confirm with Oracle Support that setting this parameter is recommended by them.