Create or replace and resolve Java source, I am not able to create a function using java source

Question

Oracle environment.

The following Java source has been correctly compiled:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED hr.roman
   AS package Package.NumeriRomani;

class Numero{
    String r="";
    int a=0;

    Numero(int n){
        a=n;
            if(n==0){
                r="NULL";
                return;}
            while(n>=1000){
                    r+="M";
                    n-=1000;}
            if(n>=900){
                    r+="CM";
                    n-=900;}
            while(n>=500){
                    r+="D";
                    n-=500;}
            if(n>=400){
                    r+="CD";
                    n-=400;}
            while(n>=100){
                    r+="C";
                    n-=100;}
            if(n>=90){
                    r+="XC";
                    n-=90;}
            while(n>=50){
                    r+="L";
                    n-=50;}
            if(n>=40){
                    r+="XL";
                    n-=40;}
            while(n>=10){
                    r+="X";
                    n-=10;}
            if(n>=9){
                    r+="IX";
                    n-=9;}
            if(n>=5){
                    r+="V";
                    n-=5;}
            if(n==4){
                    r+="IV";
                    n-=4;}
            while(n>=1){
                    r+="I";
                    n--;}
    }//Numero(int)
    Numero(String k){
        int vCor,vNext;
        r=k;
        for(int i=0;;i++){
            vCor=valore(k.charAt(i));
            if(i==k.length()-1){
                a+=vCor;
                break;}
            vNext=valore(k.charAt(i+1));
            if(vCor>=vNext)
                a+=vCor;
            else
                a-=vCor;
        }
    }

    static public int valore(char c){
        switch(c){
          case 'I': return 1;
            case 'V': return 5;
            case 'X': return 10;
            case 'L': return 50;
            case 'C': return 100;
            case 'D': return 500;
            case 'M': return 1000;
            default: return 0;
        }
    }

    public int getArabo(){
        return a;
    }
    public String getRomano(){
        return r;
    }
    }
/

I would like to create a function for getArabo and a function for getRoman, but I always receive errors.

I have tried with:

CREATE OR REPLACE FUNCTION hr.converti (alfa IN varchar2)
   RETURN VARCHAR2
AS
   LANGUAGE JAVA
   NAME 'ROMAN.getArabo(java.lang.String) return java.lang.String';
/

but I receive the error:

select hr.converti('XII') from dual;

ORA-29540: ROMAN class does not exist

How could I fix this?

Answer 1

Here are the things you need to correct to be able to use this java class:

1. Use static methods so that they can be called from Oracle without object instantiation
2. Your source is named ROMAN, but your class is named Numero. You will reference the class when calling it from Oracle. Giving both the class and the source the same name will prevent confusion.

3. java is strongly typed, you can't convert the method int getArabo() to:

   getArabo(java.lang.String) return java.lang.String
   

Here's a working example:

SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Roman" AS
  2  class Roman {
  3     public static int getArabo(java.lang.String x){
  4          return 0;
  5      }
  6  }
  7  /

Java created

SQL> CREATE OR REPLACE FUNCTION converti (alfa IN varchar2)
  2     RETURN NUMBER
  3  AS LANGUAGE JAVA NAME 'Roman.getArabo(java.lang.String) return int';
  4  /

Function created

SQL> select converti ('aaa') from dual;

CONVERTI('AAA')
---------------
              0

---

Additionally, you can use Oracle functions to convert a number to its character value in roman with TO_CHAR (but the TO_NUMBER function can't be used for the reverse operation):

SQL> SELECT to_char(2013, 'RN') FROM dual;

TO_CHAR(2013,'RN')
------------------
         MMXIII