Oracle Database Discussions

Hello together,

at a single database instance with Oracle 11.2.0.4 on a Oracle Linux 7.3 server I've a problem with the ORA_HASH function, which is used by our replication tool shareplex.

I've a table like this (copied from SQL Developer):

CREATE TABLE "AP001"."CRDPIVCHECK"
(
  "DOCID" NUMBER, 
  "CUST_KWH" NUMBER, 
  "DSO_KWH" NUMBER, 
  "TUM" NUMBER, 
  "CUST_CALC_AMOUNT" NUMBER, 
  "DSO_CALC_AMOUNT" NUMBER, 
  "TSINSERT" DATE, 
  CONSTRAINT "PK_CRDPIVCHECK" PRIMARY KEY ("DOCID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "INDEXES001"  ENABLE
)
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA001";

In that table we're trying to generate an ORA_HASH as this:

SELECT /*+ PARALLEL (A,2) */ ROWID,ORA_HASH("DOCID"), ORA_HASH("CUST_KWH"||'~'||"DSO_KWH"||'~'||"TUM"||'~'||"CUST_CALC_AMOUNT"||'~'||"DSO_CALC_AMOUNT"||'~'||"TSINSERT") FROM "AP001"."CRDPIVCHECK" A
ORDER BY 2, 3;
SELECT /*+ PARALLEL (A,2) */ ROWID,ORA_HASH("DOCID"), ORA_HASH("CUST_KWH"||'~'||"DSO_KWH"||'~'||"TUM"||'~'||"CUST_CALC_AMOUNT"||'~'||"DSO_CALC_AMOUNT"||'~'||"TSINSERT") FROM "AP001"."CRDPIVCHECK" A
ORDER BY 2, 3;

OK so I've tried to break down this select to each column for it's own. Everything works fine. But when I do an combination of the column "TUM" with any other column of the table I will get the ORA-01722 again.

SELECT /*+ PARALLEL (A,2) */ ROWID, ORA_HASH("CUST_KWH"||'~'||"TUM") FROM "AP001"."CRDPIVCHECK" A
ORDER BY 2;
Fehler beim Start in Zeile: 45 in Befehl -
SELECT /*+ PARALLEL (A,2) */ ROWID, ORA_HASH("CUST_KWH"||'~'||"TUM") FROM "AP001"."CRDPIVCHECK" A
ORDER BY 2
Fehlerbericht -
ORA-01722: Ungültige Zahl

Alone the "TUM" column works fine with ORA_HASH and gives me values...

SELECT /*+ PARALLEL (A,2) */ ROWID, ORA_HASH("TUM") FROM "AP001"."CRDPIVCHECK" A
ORDER BY 2;
ROWID              ORA_HASH("TUM")
------------------ ---------------
AAAXofAAOAALGxZABB         6398473
AAAXofAARAALMS3ABR         6398473
AAAXofAAJAALNB0AAB         6398473
AAAXofAAPAALJYSAB8         6398473
AAAXofAAMAALJXNAAJ         6398473
AAAXofAAIAALQ1KAAG         6409299
.....

So I've treid to find out if anything is wrong with the TUM column. First I've tried do build a SUM from all values as I thought when there would be any value which is not a number the SUM function will not work.

SELECT SUM(TUM) FROM AP001.CRDPIVCHECK;
SUM(TUM)
----------
8905682772

OK, works fine. Then lets try this.

SELECT DOCID, TUM FROM AP001.CRDPIVCHECK A
WHERE A.TUM <> A.TUM * 1;
DOCID    TUM
-------- --------
427067 (NULL)
1017283 (NULL)
1360449 (NULL)
3415403 (NULL)
3941215 (NULL)
4945111 (NULL)
605610 (NULL)
806945 (NULL)
1738101 (NULL)
2115891 (NULL)
3063516 (NULL)
4275637 (NULL)
2492185 (NULL)

Oh look, there are NULL values. Maybe that could be the problem? Unfortunately this is our productive system and I'm not able to change these values just for test.

So I've tried to find out if ORA_HASH can't work with NULL values when there is a combined string. Maybe you all can help here?

Thanks and regards,

David