Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Index on Python function ORA-00600 [17009]

Hi,
I tried to create a function-based index using use an MLE function (declared as deterministic) but I got ORA-00600 [17009]
However, to see if it is possible in another way, I got able to workaround this by creating a virtual column with a unique constraint on it.
Here is the full test case.
I used the hello() function from the Python example provided in MLE GitHub page:
SQL> CREATE TABLE DEMO.MY_MLE_SOURCE(name varchar2(30), source CLOB, ZIP BLOB);
Table created.
SQL> host for i in *.py ; do zip $(basename $i .py).zip $i ; echo "$i,$i,$(basename $i -py).zip" >> MY_MLE_SOURCE.ctl ; done
adding: hello.py (deflated 15%)
SQL> host cat MY_MLE_SOURCE.ctl
load data infile * into table DEMO.MY_MLE_SOURCE replace fields terminated by ','
( NAME char(30), LOB_SOURCE filler char, SOURCE lobfile(LOB_SOURCE) terminated by eof, LOB_ZIP filler char, ZIP lobfile(LOB_ZIP) terminated by eof)
begindata
hello.py,hello.py,hello.py.zip
SQL> host sqlldr '"demo/[email protected]//localhost/PDB1"' control=MY_MLE_SOURCE.ctl >/dev/null || cat MY_MLE_SOURCE.log
SQL> select NAME,SOURCE,dbms_lob.getlength(ZIP) from DEMO.MY_MLE_SOURCE;
NAME SOURCE DBMS_LOB.GETLENGTH(ZIP)
------------------------------ ------------------------------------------------------------ -----------------------
hello.py import platform 254
def hello(s_in: str) -> str:
return "Hello " + s_in + "!"
exports['hello'] = hello
-- Load the code:
SQL> create or replace python source named "hello.py" using blob select ZIP from MY_MLE_SOURCE where NAME='hello.py';
Operation 254 succeeded.
-- I Create the function as DETERMINISTIC:
SQL> create or replace function hello("s_in" in varchar2) return varchar2 deterministic
2 as language python name 'hello\.py.hello(s_in str) return str';
3 /
Function created.
-- Then create the table with a virtual column
SQL> create table DEMO (
2 ID number constraint DEMO_ID primary key,
3 MESSAGE varchar2(4000) generated always as ( hello(to_char(ID)) ) --constraint DEMO_MESSAGE unique
4 );
Table created.
SQL> whenever sqlerror continue;
-- The index creation fails:
SQL> create index DEMO_MESSAGE on DEMO(MESSAGE);
create index DEMO_MESSAGE on DEMO(MESSAGE)
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []
-- Same creating a FBI without using a virtual column:
SQL> create index DEMO_HELLO on DEMO( hello('x') );
create index DEMO_HELLO on DEMO( hello('x') )
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [17009], [3], [0x068973C90], [0x0637F1658], [], [], [], [], [], [], [], []
-- Ok when adding a constraint which creates implicitly the same index:
SQL> alter table DEMO add constraint DEMO_MESSAGE unique(MESSAGE);
Table altered.
-- Proof that this index is used as an access path in the execution plan
SQL> insert into DEMO(id) select rownum from xmltable('1 to 5');
5 rows created.
SQL> set autotrace on explain
SQL> select * from DEMO where MESSAGE='Hello 5!';
ID MESSAGE
---------- ------------------------------
5 Hello 5!
Execution Plan
----------------------------------------------------------
Plan hash value: 1280979691
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEMO | 1 | 2015 | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | DEMO_MESSAGE | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MESSAGE"='Hello 5!')
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0_MLE - 64bit Beta
The Ora-600 occurs in the following stack: ksedst()+365<-dbkedDefDump()+22416<-ksedmp()+586<-dbgexPhaseII()+3068<-dbgexProcessError()+2531<-dbgePostErrorKGE()+2271<-dbkePostKGE_kgsf()+90<-kgeriv()+1706<-kgesiv()+112<-kgesic3()+142<-kglIsPinShareable()+365<-kglSessionHashIterate()+346<-kglpin()+3785<-kdiclap_lkpn()+1466<-kdicwpost()+972<-kdicrws()+23752<-kdicdrv()+2194<-opiexe()+16957<-opiosq0()+9305<-kpooprx()+467<-kpoal8()+1082<-opiodr()+2988<-ttcpip()+6614<-opitsk()+3853<-opiino()+744<-opiodr()+2988<-opidrv()+988<-sou2o()+272<-opimai_real()+244<-ssthrdmain()+511<-main()+262<-__libc_start_main()+245
If you can't reproduce, just tell me how to trace more useful information.
Regards,
Franck.
Best Answer
-
Hi Franck,
Thank you for trying again. It might be a coincidence that we get the error for common users but not for local users. There likely might be other factors that have an influence. However, we are now able to reproduce the bug and we will address it. Unfortunately, we currently don't have a workaround for the 0.3.0 release we can provide you with.
Best regards,
Bastian
Answers
-
Hi Franck,
Thank you very much for reporting this issue. Can you reliably trigger the internal error? For example, in a fresh session on a database that has just been started?
Best regards,
Bastian
-
Franck Pachot OCM 12c DBA Oracle ACE Director and Oak Table member SwitzerlandMember Posts: 912 Bronze Trophy
Hi Bastian,
Yes, reproducible in fresh session.
This is my test case for the output I've pasted:
cat > hello.py <<'CAT'
import platform
def hello(s_in: str) -> str:
return "Hello " + s_in + "!"
exports['hello'] = hello
CAT
cat > MY_MLE_SOURCE.ctl <<'CAT'
load data infile * into table DEMO.MY_MLE_SOURCE replace fields terminated by ','
( NAME char(30), LOB_SOURCE filler char, SOURCE lobfile(LOB_SOURCE) terminated by eof, LOB_ZIP filler char, ZIP lobfile(LOB_ZIP) terminated by eof)
begindata
CAT
cat > hello.sql <<'CAT'
whenever sqlerror exit failure
column message format a30
column source format a60
column zip format a60
column text format a80
set echo on linesize 160 pagesize 1000 longc 1000000 long 1000000
CREATE TABLE DEMO.MY_MLE_SOURCE(name varchar2(30), source CLOB, ZIP BLOB);
host for i in *.py ; do zip $(basename $i .py).zip $i ; echo "$i,$i,$(basename $i -py).zip" >> MY_MLE_SOURCE.ctl ; done
host cat MY_MLE_SOURCE.ctl
host sqlldr '"demo/[email protected]//localhost/PDB1"' control=MY_MLE_SOURCE.ctl >/dev/null || cat MY_MLE_SOURCE.log
select NAME,SOURCE,dbms_lob.getlength(ZIP) from DEMO.MY_MLE_SOURCE;
create or replace python source named "hello.py" using blob select ZIP from MY_MLE_SOURCE where NAME='hello.py';
create or replace function hello("s_in" in varchar2) return varchar2 deterministic
as language python name 'hello\.py.hello(s_in str) return str';
/
create table DEMO (
ID number constraint DEMO_ID primary key,
MESSAGE varchar2(4000) generated always as ( hello(to_char(ID)) )
);
whenever sqlerror continue;
create index DEMO_MESSAGE on DEMO(MESSAGE);
create index DEMO_HELLO on DEMO( hello('x') );
alter table DEMO add constraint DEMO_MESSAGE unique(MESSAGE);
insert into DEMO(id) select rownum from xmltable('1 to 5');
set autotrace on explain
select * from DEMO where MESSAGE='Hello 5!';
CAT
sqlplus demo/[email protected]//localhost/PDB1 @ hello.sql
Regards,
Franck.
-
Hi Franck,
Thank you again for your detailed and very helpful bug report. We were finally able to reproduce the issue.
The error shows up if your example gets executed under a common user. However, if the example gets executed under a local user (use the already existing local user "scott" identified by "tiger" or create "demo" as a local user), then everything is fine. This bug, which is new to us, looks like a locking issue at a first glance. Therefore, we would recommend to use local users in the 0.3.0 release, if possible.
Best regards,
Bastian
-
Franck Pachot OCM 12c DBA Oracle ACE Director and Oak Table member SwitzerlandMember Posts: 912 Bronze Trophy
Hi Bastian,
My DEMO is a local user, I didn't change the C## prefix.
I tested with SCOTT and got the same error.
Regards,
Franck.
-
Hi Franck,
Thank you for trying again. It might be a coincidence that we get the error for common users but not for local users. There likely might be other factors that have an influence. However, we are now able to reproduce the bug and we will address it. Unfortunately, we currently don't have a workaround for the 0.3.0 release we can provide you with.
Best regards,
Bastian
-
Thank you for attempting again. It is probably a twist of fate that we get the error for common customers but not for neighborhood users. There in all likelihood is probably different factors that have a power. However, we are now able to reproduce the bug and we will address it. Unfortunately, we currently don't have a workaround for the 0.Three.0 launch we are able to provide you with.