Agfdhyk

The code I have in PLSQL is as:

    CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ConversorSQL" AS

  package test;

  import java.util.Calendar;

  import java.sql.Connection;

  import java.sql.DriverManager;

  import java.sql.ResultSet;

  import java.sql.SQLException;

  import java.sql.Statement;



  import java.io.FileReader;

  import java.io.IOException;



  public class ConversorSQL {  

    public static String runConversor(String tipoOperacion, String nombreTabla, String queryOpcional) {

      return "Hola";

    }

  }

And then:

CREATE OR REPLACE FUNCTION f_conversorSQL(p_tipo VARCHAR2,

                                      p_tabla VARCHAR2,

                                      p_query VARCHAR2) RETURN VARCHAR2 IS LANGUAGE JAVA NAME 'test.ConversorSQL.runConversor(String, String, String) return String';

And I call it with:

  SELECT f_conversorSQL( 'Hello there!',

                       'General Kenobi',

                       'You are a bold one')

 FROM DUAL;

And I get the error in the title:

Error que empieza en la línea 1 del comando:

SELECT f_conversorSQL( 'Hello there!',

                       'General Kenobi',

                       'You are a bold one')

FROM DUAL;

Informe de error -

Error SQL: ORA-29531: ningún método runConversor en la clase ConversorSQL

29531. 00000 - "no method %s in class %s"

*Cause: An attempt was made to execute a non_existent method in a Java class.

*Action: Adjust the call or créate the specified method.

I don't understand why it can not reach the method. Both, the JAVA SOURCE and the FUNCTION, compile correctly. But, and this is where the fun begins, this code runs right and dont give me the error

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JavaDate" AS

  package pruebas;

  import java.util.Calendar;

  import java.sql.Connection;

  import java.sql.DriverManager;

  import java.sql.ResultSet;

  import java.sql.SQLException;

  import java.sql.Statement;



  import java.io.FileReader;

  import java.io.IOException;



  public class JavaDate {  

    public static String getString(int anho, int mes, int dia, int hora, int minuto, int segundo) {

      return "hola";

    }

  }

Same as before:

    CREATE OR REPLACE FUNCTION f_javadate(p_anho NUMBER,

                                      p_mes NUMBER,

                                      p_dia NUMBER,

                                      p_hora NUMBER,

                                      p_minuto NUMBER,

                                      p_segundo NUMBER) RETURN VARCHAR2 IS LANGUAGE JAVA

 NAME 'pruebas.JavaDate.getString(int, int, int, int, int, int) return String';

Same as before too:

SELECT f_javadate(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')),

                  TO_NUMBER(TO_CHAR(SYSDATE, 'MM')),

                  TO_NUMBER(TO_CHAR(SYSDATE, 'DD')),

                  TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')),

                  TO_NUMBER(TO_CHAR(SYSDATE, 'MI')),

                  TO_NUMBER(TO_CHAR(SYSDATE, 'SS')))

 FROM DUAL;

They look pretty much the same, I only changed the package name, the function name and the number of parameters.

I'm working with Oracle 11g, the Java version is 1.5.0_10 and the environment is SQL Developer 4.0.1

To call java source function use CALL:

 VARIABLE myString VARCHAR2(20);

 CALL f_conversorSQL( 'Hello there!',

                   'General Kenobi',

                   'You are a bold one') 

 INTO :myString;

Call the stored procedure, as follows:

SQL> VARIABLE myString VARCHAR2(20);



SQL> CALL helloworld() INTO :myString;

statement performs a top-level call in Oracle Database. SQL and PL/SQL see no difference between a stored procedure that is written in Java, PL/SQL, or any other language.

Define the function with java.lang.String:

CREATE OR REPLACE FUNCTION f_conversorSQL(p_tipo VARCHAR2,

                                  p_tabla VARCHAR2,

                                  p_query VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'test.ConversorSQL.runConversor(java.lang.String, java.lang.String, java.lang.String) return java.lang.String';