Creating function in an Oracle database using java directly

Question

I understand that it is possible to create java function in an oracle database using the command CREATE JAVA - look here. I have been reading a lot about how to do this but the more I read the more there is to read, it seems.

What I want to do is simple. Since I am already very familiar with Java, I don't want to go through learning PL/SQL especially that the project I am working on is rather small. I also don't want to mess around too much with with this feature, all that I want to do is something like the following:

1) Declare a function while connected to the database like:

CREATE JAVA AS 
    public class Example{
        public static bool returnTrue() {
            return true;
        }
    }

2) Then call the function while connected like:

SELECT Example.returnTrue() FROM DUAL;

Is this possible?
How?

Answer 1

Not directly possible, you need another step:

(Note that you cannot return a boolean type in a SQL callable function. You must return a valid Oracle SQL type, in this example, a String).

Create your function:

create or replace and compile java source named returntrue as
public class example
{ public static String returnTrue() { return "TRUE"; } };

You have to create a PL/SQL "wrapper" to interface between the java function and PL/SQL:

SQL>     CREATE OR REPLACE FUNCTION returnTrue
  2      RETURN VARCHAR2
  3      AS LANGUAGE JAVA
  4      NAME 'example.returnTrue() return java.lang.String';
  5  /


Function created

SQL> select returntrue from dual;

RETURNTRUE
--------------------------------------------------------------------------------
TRUE

More info in the Oracle documentation.

Answer 2

How invested are you in Oracle? If the answer is anywhere between "a fair amount" and "my life depends on it," learn PL/SQL. It's a sort of odd language in some ways, but it does what it does quite well, and that is provide a procedural layer on top of SQL. If what your code is doing is more in depth or sophisticated than "a procedural layer on top of SQL", you shouldn't use stored procedures at all. Run your own JVM and employ JDBC when you need it.