Scala external procedures in Oracle

I assume it would be more correct to say “using scala functions in Oracle” but Oracle documentation uses terms like External Procedures or External Subprograms.

There are no specific details about scala with regard to external procedures in Oracle but given that scala code is translated into Java byte code there should be no difficulties in using scala as a language for stored code as well.

Of course, there is no scala compiler in Oracle so we cannot run statement like create or replace and compile java source but we can compile scala code in advance and upload classes using loadjava utility.

All java methods which are mapped to PL/SQL routines must be static and since there are no static methods in scala the typical approach to mimic them is by declaring functions in an object rather than in a class.

Let’s have a look at some sample code

package pkg;

object obj1 {
  def f(p: Int) = 1000 * p
}

object obj2 {
  def f(p: Int) = 1000 * p + List(1,2,5,3,4).map(x => x * x).sum
}

Below commands are used to compile the code and load all classes produced by a compiler into a database.

c:\temp>scalac dummy.scala

c:\temp>loadjava -thin -user usr/pwd@host:port:sid pkg\obj*.class

All classes have been successfully loaded into DB.

SQL> select name, source from user_java_classes;

NAME       SOURCE
---------- ---------------
pkg/obj1$  dummy.scala
pkg/obj1   dummy.scala
pkg/obj2$  dummy.scala
pkg/obj2   dummy.scala

Final step is to create corresponding PL/SQL functions.

create or replace function f1(p in number) return number as
language java name 'pkg.obj1.f(int) return int';
/
create or replace function f2(p in number) return number as
language java name 'pkg.obj2.f(int) return int';
/

Let’s have a look at compiled scala code in Oracle in action!

SQL> select f1(5) f1 from dual;

        F1
----------
      5000

SQL> select f2(5) f2 from dual;
select f2(5) f2 from dual
                     *
ERROR at line 1:
ORA-29541: class USR.pkg/obj2 could not be resolved

First function works fine but the second one says class M122.pkg/obj2 could not be resolved.
This is because the second function uses functionality of scala library, in particular List collection so to get it work we need to load scala library into DB as well.

c:\temp>loadjava -thin -user usr/pwd@host:port:sid "%SCALA_HOME%\lib\scala-library.jar"

After this change function f2 also works fine.

SQL> select f2(5) f2 from dual;

        F2
----------
      5055

But now we have quite a few objects representing scala library in DB.

SQL> select kind, substr(source, instr(source, '.')) src_ext, count(*) cnt
  2    from user_java_classes
  3   where name not like 'pkg/obj%'
  4   group by kind, substr(source, instr(source, '.'))
  5   order by 1, 2;

KIND                                               SRC_EXT           CNT
-------------------------------------------------- ---------- ----------
??? OAC = 0                                        .java               3
CLASS                                              .java              30
CLASS                                              .scala           1815
INTERFACE                                          .java              87
INTERFACE                                          .scala            569

Why 3 of those objects have kind “??? OAC = 0” that’s a separate question…