[SOLVED] Creating Oracle SQL Java Function does not work

Creating Oracle SQL Java Function does not work

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;';

Solution

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;
/