Scala inside Oracle database: Hello World

Short intro

Let's write Oracle stored procedure (or function) in Scala.

Classical ways to resolve problems with Oracle database are:

  • SQL
  • PL/SQL
  • Java <= We are here
  • C

Scala is not natively supported by Oracle.
But it is possible to make it work pretending it is just some Java library.

Download and prepare

Ingredients used:

  • Oracle database 12.1
  • Scala 2.10.2
  • sbt 0.13 (optional)

Upload Scala library

Upload $SCALA_HOME/lib/scala-library.jar in Oracle database:

loadjava -user user/pwd@oradb \
-resolve -resolver "((* user )(* public)(* -))" \
-verbose \
$SCALA_HOME/lib/scala-library.jar

Check there are no errors at the end of load and verify status inside database: VALID status:
select object_type,status, count(*) from user_objects
group by object_type, status
order by 1,2;

Write Scala example

Using text editor:

object HelloWorld {
  def sayHello( p :String ) = s"Hi $p. I am Scala function, hidden inside Oracle database." 
}        	
Save as HelloWorld.scala.

Compile and load

You can use scalac to compile. Another alternative is to use sbt:

sbt package
That will create jar file. Note the name and path.
And load the same way you did before:

loadjava -user user/pwd@oradb \
-resolve -resolver "((* user )(* public)(* -))" \
-verbose \
myFile.jar

Check again that everything is OK by looking at user_objects.status

Publish

Now it is the time to create PL/SQL wrapper:
create or replace function helloFn( p varchar2 )
return varchar2
as
  language java
    name 'HelloWorld.sayHello( java.lang.String ) returns String';
/
show errors

Call stored function

Created and published function is available as function written in Java or PL/SQL. Let's test it:

No errors.
SQL> 
SQL> select helloFn( 'World wide web') from dual; 

HELLOFN('WORLDWIDEWEB')
------------------------------------------------------------------------
Hi World wide web. I am Scala function, hidden inside Oracle database.

That's it.
comments powered by Disqus