How to create SQL function with JDBC?

Question

I want to create SQL function with JDBC

CREATE OR REPLACE FUNCTION TEST."TO_STRING"(
nt_in in ntt_varchar2, 
delimiter_in IN VARCHAR2 DEFAULT ',')
return varchar2 is
v_idx PLS_INTEGER;
v_str varchar2(32767);
v_dlm VARCHAR2(10);
v_length NUMBER;
v_max_size NUMBER;
begin  
v_dlm := delimiter_in;
v_idx := nt_in.FIRST;

v_max_size :=2000;
WHILE v_idx IS NOT NULL LOOP
  IF (nvl(length(v_str),0) + length(v_dlm) + length(nt_in(v_idx))) > v_max_size THEN 
    if  v_str is null then
      v_str := substr(nt_in(v_idx), 0, v_max_size);
    else
      v_str := v_str || v_dlm || substr(nt_in(v_idx), 0, v_max_size - length(v_str) - length(v_dlm));
    end if;
    EXIT;
  END IF;
  IF (v_str is not null and length(v_str) > 0) THEN
    v_str := v_str || v_dlm || nt_in(v_idx);
  ELSE
    v_str := nt_in(v_idx);
  END IF;
  v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
end to_string

, but get ORA-00900: invalid SQL statement

Is it possible to create SQL function with JDBC? I use Oracle Database 11g Release 11.1.0.0.0

can you add your function as shown thru a different interface? Is this problem specific to jdbc or to your function? — ControlAltDel
– ControlAltDel, Commented Mar 29, 2012 at 14:59
Thru Toad I run this code without problem. And this function run — Ігар Цімошка
– Ігар Цімошка, Commented Mar 29, 2012 at 15:08

Lukas Eder · Accepted Answer · 2012-03-29 15:12:30Z

3

What's wrong with this?

String sql = "CREATE OR REPLACE FUNCTION TEST.\"TO_STRING\"(......";
connection.createStatement().execute(sql);

Of course, your SQL must still be correct. You're actually missing a semi-colon on the last line:

end to_string;

answered Mar 29, 2012 at 15:12

Lukas Eder

223k138 gold badges728 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create SQL function with JDBC?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related