Create Function in h2 from Oracle

Question

i have the following function in oracle that i have to translate into h2. Could anyone help me. I have no idea to do this:

create or replace function unpack_info (p_trackchar table.ordchar%type) 
 return varchar2 is
l_res varchar2(8);
begin
select decode(bitand(to_number(ascii(p_trackchar)),1),1,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),2),2,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),4),4,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),8),8,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),16),16,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),32),32,'1','0') ||
  decode(bitand(to_number(ascii(p_trackchar)),64),64,'1','0') into l_res
from dual;
return l_res;
end;

I have tried to do first some basics but the creation of this function will not work:

CREATE ALIAS HTS.TEST AS $$
String nextPrime(String value){
 return null;
}
$$;

I get this error:

Error: Syntax Fehler in SQL Befehl "CREATE ALIAS HTS.TEST AS []$$ String nextPrime(String value){ return null" Syntax error in SQL statement "CREATE ALIAS HTS.TEST AS []$$ String nextPrime(String value){ return null" [42000-162]

Is this the right way to do the translation or what can i do?

I have created an alias (the test one) and could execute it. Now O have to create the alias for unpack:info. Could someone help me cause of the syntax etc. how does the function looks as an alias in h2?

Answer 1

Use ' as the escaped sequence instead of $$. In between if you want to use the escape sequence use $$. so the code would look like:

CREATE ALIAS HTS.TEST AS '
public static String nextPrime(String value){
 return null;
}
';

Answer 2

I can't reproduce the problem. How exactly did you run the statement? Possibly you run the statement with a tool that only sent part of the query? Or possibly there is a 'special character' (some kind of unicode space) just before the '$$'? I tried this:

create schema hts;
CREATE ALIAS HTS.TEST AS $$
String nextPrime(String value){
    return null;
}
$$;

Could you try this again in the H2 Console?