java-sql-function.html
05-09-2001 Bikle
-----------------

Creation of a Java Based SQL Function

This is a simple description about how to create a java based function which
may be treated just as any ordinary sql based function.

First, I create a .java file named Doubleit.java:

// Doubleit.java

public class Doubleit {
    public static int dblit (int n) {
        return n*2;
    }
}

Then, I load it into the database from the UNIX command line:

% loadjava -u scott/tiger Doubleit.java

Next, I create a simple CREATE FUNCTION script:

--
-- crDoubleit.sql
--

CREATE OR REPLACE FUNCTION dblit (double_this NUMBER) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'Doubleit.dblit(int) return int';
/

Then, I run the script:

% sql scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Wed May 9 09:48:34 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production

09:48:34 SQL> @crDoubleit
09:48:39 SQL> --
09:48:39 SQL> -- crDoubleit.sql
09:48:39 SQL> --
09:48:39 SQL>
09:48:39 SQL> CREATE OR REPLACE FUNCTION dblit (double_this NUMBER) RETURN NUMBER
09:48:39   2  AS LANGUAGE JAVA
09:48:39   3  NAME 'Doubleit.dblit(int) return int';
09:48:39   4  /

Function created.

09:48:39 SQL>

Next, I test the function:



09:48:39 SQL> select dblit(8) from dual;
  
  DBLIT(8)
----------
        16

09:48:53 SQL>


That's all there is to this demo.