Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Invalid hint (19c)
Oracle Invalid hint [message #684817] Fri, 27 August 2021 17:14 Go to next message
Unclefool
Messages: 16
Registered: August 2021
Junior Member
I have 2 scenarios where I'm using the hint IGNORE_ROW_ON_DUPKEY_INDEX. The first seems to be working fine.

The second scenario is failing with the error below and I'm unsure how to fix it.

ORA-38913: Index specified in the index hint is invalid

Thanks in advance to all who answer. I'm testing on live SQL so our environments can be the same if you so desire.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

 create table t (
    t_pk integer not null primary key
  );

 insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (t_pk)) */ into t values (1);

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (t_pk)) */ into t values (1);


CREATE table t1(
      seq_num INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       a NUMBER,
       b  DATE,
       c NUMBER,
       d NUMBER,
       e DATE,
       f DATE,
       g DATE,
       h VARCHAR2(1),
constraint t1_pk primary key (a, b,c,d,e, f, g,h)
       );

insert /*+ignore_row_on_dupkey_index (t1 ( t1_pk)) */ INTO t1(
       a
       ,b
       ,c
       ,d
       ,e
       ,f
       ,g
       ,h
 )
VALUES
 (1,
TO_DATE('2021-08-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1,
TO_DATE('2021-08-28 13:27:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2021-08-28 13:30:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2021-08-28 13:27:20', 'YYYY-MM-DD HH24:MI:SS'), 'G');

Re: Oracle Invalid hint [message #684818 is a reply to message #684817] Sat, 28 August 2021 01:27 Go to previous message
John Watson
Messages: 8618
Registered: January 2010
Location: Global Village
Senior Member
orclz> ed
Wrote file afiedt.buf

  1  insert /*+ ignore_row_on_dupkey_index (t1, t1_pk) */ INTO t1(
  2         a
  3         ,b
  4         ,c
  5         ,d
  6         ,e
  7         ,f
  8         ,g
  9         ,h
 10   )
 11  VALUES
 12   (1,
 13  TO_DATE('2021-08-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1,
 14  TO_DATE('2021-08-28 13:27:00', 'YYYY-MM-DD HH24:MI:SS'),
 15  TO_DATE('2021-08-28 13:30:00', 'YYYY-MM-DD HH24:MI:SS'),
 16* TO_DATE('2021-08-28 13:27:20', 'YYYY-MM-DD HH24:MI:SS'), 'G')
orclz> /

1 row created.

orclz> /

0 rows created.

orclz>
Previous Topic: Generate Unique Grouping ID
Next Topic: Fire triggers on a nested_table
Goto Forum:
  


Current Time: Sat Oct 16 00:39:33 CDT 2021