Oracle Database Free

1. Create user DEMO2 (prerequisite)

Create the following user to reproduce the issue:

create user demo2 identified by demo2
   default tablespace users
   temporary tablespace temp
   quota 1m on users;

grant db_developer_role to demo2;
grant execute dynamic mle to demo2;
grant execute on javascript to demo2;

2. Create MLE module as user DEMO2 (prerequisite)

Create the following MLE module:

create or replace mle module hello_world_mod language javascript as
export function greet(firstName = "World", lastName = " ") {
   return `Hello ${firstName} ${lastName}`.trim();
}
/

3. Demonstrate that the MLE module works

Create the following call specification:

create or replace function greet(
   in_first_name varchar2,
   in_last_name varchar2
) return varchar2 as mle module hello_world_mod signature 'greet(string, string)';
/ 

and then run the following

column greet format a20
select greet('John', 'Doe') as greet;

this should produce:

GREET 
--------------------
Hello John Doe

So far so good.

4. Demonstrate that default values for parameters are not supported

Create the following alternative greet function:

create or replace function greet(
   in_first_name varchar2 default 'World',
   in_last_name varchar2 default ' '
) return varchar2 as mle module hello_world_mod signature 'greet(string, string)';
/

This causes the following error:

LINE/COL ERROR
--------- -------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/10 PLS-00255: CALL Specification parameters cannot have default values

Ok, this clearly states, that this is not supported. So, let's try something else.

5. Handle parameter default values in PL/SQL wrapper

As a workaround, I tried the following:

create or replace function greet(
   in_first_name varchar2 default 'World',
   in_last_name varchar2 default ' '
) return varchar2 is
   function internal_greet(
      in_first_name varchar2,
      in_last_name varchar2
   ) return varchar2 as mle module hello_world_mod signature 'greet(string, string)';
begin
   return internal_greet(in_first_name, in_last_name);
end;
/

and then executed the following:

select greet('John', 'Doe');

what caused an ORA-600:

Error starting at line : 14 in command -
select greet('John', 'Doe')
Error at Command Line : 14 Column : 1
Error report -
SQL Error: ORA-00600: internal error code, arguments: [kgmexec21], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "DEMO2.GREET", line 5
ORA-06512: at "DEMO2.GREET", line 10
ORA-06512: at line 1
00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause: This is the generic internal error number for Oracle program
        exceptions. It indicates that a process has encountered a low-level,
        unexpected condition. The first argument is the internal message
        number. This argument and the database version number are critical in
        identifying the root cause and the potential impact to your system.

6. Workaround - PL/SQL Package

As a workaround, I created several call specifications in a PL/SQL package. Like this:

create or replace package hello_world is
   function greet
      return varchar2 as mle module hello_world_mod signature 'greet()';
   function greet(
      in_first_name in varchar2
   ) return varchar2 as mle module hello_world_mod signature 'greet(string)';
   function greet(
      in_first_name in varchar2,
      in_last_name in varchar2
   ) return varchar2 as mle module hello_world_mod signature 'greet(string, string)';
end;
/

and then run the following SQL script:

column greet_p0 format a20
column greet_p1 format a20
column greet_p2 format a20

select hello_world.greet() as greet_p0, 
hello_world.greet('John') as greet_p1, 
hello_world.greet('John', 'Doe') as greet_p2;

with this result:

GREET_P0             GREET_P1             GREET_P2 
-------------------- -------------------- --------------------
Hello World          Hello John           Hello John Doe

7. Expected Measures for coming 23c versions

• Fix the ORA-600 (if non-public call_specifications are not supported then at least I'd expect an error message that says so)
• Maybe there is an easier solution to deal with default values for parameters. I currently do not understand why the call_specification can not support that (the PLS-00255). It's not super-important, IMO. However, it would be nice to have this as an option.

Thanks.