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

This is a fabulous project, I have had problems with using java for real time applications because of the garbage collection mechanism.  I have used configuration techniques to minimize garbage collection, I'm wondering if you could address that here?

unknown-3392251

tanx is soo goood

طراحی سایت

Kashif Sohail

Is there any starter kit available for beginners?

Jose Cruz

Is there any starter kit available for beginners?

Dear Kashif

No, there isn't, because it is one of my hobby projects that I share with Java ME community.

If you need I can help you about your needs.

Best Regards

1 - 4

Post Details

Added on Dec 16 2018
6 comments
757 views