OraFAQ Forum: SQL & PL/SQL » Oracle Invalid hint

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Invalid hint (19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle Invalid hint [message #684817]

Fri, 27 August 2021 17:14

Unclefool
Messages: 85
Registered: August 2021

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

Report message to a moderator

Re: Oracle Invalid hint [message #684818 is a reply to message #684817]

Sat, 28 August 2021 01:27

John Watson
Messages: 8977
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>

Report message to a moderator

Previous Topic:	Generate Unique Grouping ID
Next Topic:	Fire triggers on a nested_table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 21 20:35:09 CDT 2025