avix avix - 3 years ago 141
SQL Question

Java String parameter in Oracle sql developer

I have to use some java function in Oracle SQL Developer. But Im having some troubles with java String parameter. I know my code does nothing with this String. It will be.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "StringTest" AS
public class StringTest
{
public static int test(String a)
{
return 1;
}
}
/


Which returns:


Java Source StringTest created


Then:

CREATE OR REPLACE FUNCTION F_STRING(input1 in char) return number
as LANGUAGE JAVA NAME 'StringTest.test(String) return int';



Function F_STRING compiled


Now when I try to execute my function:

SELECT F_STRING("some_text") FROM MyTable;



ORA-00904: "some_text": invalid identifier


  1. 00000 - "%s: invalid identifier"




When I try using single quote instead of " I get this:


ORA-29531: no method test in class StringTest
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 create the specified method.


Same thing happens when I use varchar2 instead of char.
Im sure Im missing something very simple, but can't find solution for like few hours and it already drives me crazy.

Answer Source

When you publishing java method in oracle you have to use full class name. (Canonical Name).

int - is ok , String - is not ok.

Change this

CREATE OR REPLACE FUNCTION F_STRING(input1 in char) return number
    as LANGUAGE JAVA NAME 'StringTest.test(String) return int';

to this.

CREATE OR REPLACE FUNCTION F_STRING(java.lang.String in char) return number
as LANGUAGE JAVA NAME 'StringTest.test(java.lang.String) return int';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download