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…