CODEWITHSUNDEEP
oracle-databasejava-stored-procedures
Stuart Grassie
Stuart Grassie

Reputation: 3073

PLS-00103 error when comping Oracle stored function

Given the following Java which is loaded into the database using loadjava:

package grassie.example;

public class Example {

    public static String test(){
        return "Hello World!";
    }
}

And given the following Oracle Stored Function:

CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'grassie.example.Example.test() RETURN java.lang.String';

Why does the function not compile with the following error?:

Error(3,1): PLS-00311: the declaration of "grassie.example.Example.test() RETURN java.lang.String" is incomplete or malformed

Oracle client is 9.2.0.8.0, database is 9.2.0.8.0. Using SQL Developer 2.1.0.63

edit: Ammended my question based on answers below.

To further clarify, I created this simple test class and function because I am having problems with more complicated Java and stored functions, which accept and return various parameter types.

Upvotes: 1

Views: 4040

Answers (3)

Δημήτρης Σαράντης
Δημήτρης Σαράντης

Reputation: 1

Function definition should be like the below codes, 

CREATE OR REPLACE FUNCTION NEMO_SCM.TEST_FUNCTION RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'grassie.example.Example.test() **return** java.lang.String';

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425361

Get rid of the empty parentheses in the function declaration:

CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'grassie.example.Example.test() RETURN java.lang.String';

Update:

Java keywords should be lowercase. Try replacing RETURN with return in the NAME clause.

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

if the function has no parameters you don't need parentheses ():

SQL> CREATE OR REPLACE FUNCTION TEST_FUNCTION() RETURN NUMBER AS
  2  BEGIN
  3     RETURN 0;
  4  END;
  5  /

Warning: Function created with compilation errors

SQL> CREATE OR REPLACE FUNCTION TEST_FUNCTION RETURN NUMBER AS
  2  BEGIN
  3     RETURN 0;
  4  END;
  5  /

Function created

Upvotes: 1

Related Questions