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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9d0179c9d951eaaf4ba7660afa560a30?s=64&d=mm&r=g) |
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');
|
|
|
Re: Oracle Invalid hint [message #684818 is a reply to message #684817] |
Sat, 28 August 2021 01:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
John Watson
Messages: 8944 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>
|
|
|
Goto Forum:
Current Time: Sat Sep 07 21:30:49 CDT 2024
|