1

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

1 Answer 1

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';
Sign up to request clarification or add additional context in comments.

3 Comments

@MiguelNoTeimporta did you succeeded? also updated to define the function with java.lang.String
Yes! It worked! But why it is not necessary to put on the return part? Thanks a lot!
@MiguelNoTeimporta you don't have to use tge return but it's returning a value

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.