Creating Oracle SQL Java Function does not work

Question

When trying to create oracle SQL java function to generate random UUID as described in this StackOverflow answer, I get following error:

sql> CREATE or REPLACE FUNCTION random_uuid
   RETURN VARCHAR2
   AS LANGUAGE JAVA NAME 'java.util.UUID.randomUUID() return java.lang.String'
[2019-01-29 09:28:29] [99999][17110] Warning: execution completed with warning
[2019-01-29 09:28:29] completed in 23 ms
[2019-01-29 09:28:29] 3:78:PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
[2019-01-29 09:28:29] ;
[2019-01-29 09:28:29] The symbol ";" was substituted for "end-of-file" to continue.

Even this is just a warning, calling it does not work afterwards:

sql> select random_uuid() from dual
[2019-01-29 09:36:28] [65000][6575] ORA-06575: Package or function RANDOM_UUID is in an invalid state

I use IntelliJ IDEA's Database Console to execute the script. What is wrong?

EDIT: Mine original script does contain semicolon at the end:

create or replace function random_uuid return varchar2 as
  language java
  name 'java.util.UUID.randomUUID() return String';

Adding ';' after the String does not help:

create or replace function random_uuid return varchar2 as
  language java
  name 'java.util.UUID.randomUUID() return String;';

There is no java code apart from the "java.util.UUID.randomUUID()" in the script. I'm just trying to execute the script within Database Console of IntelliJ IDEA. This seems to be caused by the parsing: Database Console treats the semicolon at the end as statement delimiter and therefore semicolon is missing for the statement. Same problem with dbvis but it is possible to mark the block with "--/" and "-" there. — Vilmantas Baranauskas
– Vilmantas Baranauskas, Commented Jan 29, 2019 at 11:02
Check if class is valid SELECT * FROM dba_objects WHERE object_type IN ('JAVA CLASS') and object_name = 'java/util/UUID'; — Arkadiusz Łukasiewicz
– Arkadiusz Łukasiewicz, Commented Jan 29, 2019 at 11:57
IntelliJ IDEA bug reported: youtrack.jetbrains.com/issue/DBE-7723 — Vilmantas Baranauskas
– Vilmantas Baranauskas, Commented Jan 30, 2019 at 8:53

MT0 · Accepted Answer · 2019-01-29 12:01:45Z

1

Since the problem appears to be the IDE not recognising where the statement terminates then you can try to wrap it in a PL/SQL anonymous block and use EXECUTE IMMEDIATE to force it to parse the statement correctly:

BEGIN
  EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION RandomUUID RETURN VARCHAR2 AS LANGUAGE JAVA NAME ''java.util.UUID.randomUUID() return java.lang.String'';';
END;
/

edited Jan 29, 2019 at 12:01

answered Jan 29, 2019 at 11:29

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vilmantas Baranauskas Over a year ago

'java.util.UUID.randomUUID() return String' works just fine when creating the function using dbvis and SQL block delimiters: confluence.dbvis.com/display/UG100/Executing+Complex+Statements

Collectives™ on Stack Overflow

Creating Oracle SQL Java Function does not work

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related