Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Index on Python function ORA-00600 [17009]

Franck PachotDec 16 2018 — edited Dec 19 2018

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/demo@//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.

This post has been answered by Bastian Hossbach-Oracle on Dec 19 2018
Jump to Answer

Comments

1. Use code tags when you post code.
2. Post the entire method.
EJP
C++ has much stricter type-checking than C.

This is not a JNI question. Try a C+ forum.
1 - 2

Post Details

Added on Dec 16 2018
6 comments
759 views