Oracle Java stored procedures returning data -


i'm trying write java stored procedure return result. i've found doc on oracle website, none of examples provided return data http://docs.oracle.com/cd/b19306_01/java.102/b14187/cheight.htm#chdjjdgh

i have created package follow:

create or replace package test_proc  function hello_world return varchar2; procedure insert_test(chaine varchar2, nombre number); end test_proc; 

the package body follow

create or replace package body test_proc function hello_world return varchar2 language java name 'testproc.helloworld() return java.lang.string'; procedure insert_test(chaine varchar2, nombre number) language java name 'testproc.inserttest(java.lang.string, int)'; end test_proc; 

and java code

public class testproc {  public static void inserttest(string chaine, int nombre)  {     system.out.println("insert test...");     string sql = "insert test values(?,?)";     try     {         connection conn = drivermanager.getconnection("jdbc:default:connection:");         preparedstatement pstmt = conn.preparestatement(sql);         pstmt.setstring(1, chaine);         pstmt.setint(2, nombre);         pstmt.executeupdate();     }     catch (sqlexception e)     {         system.err.println(e.getmessage());          } }  public static string helloworld()  {     return "hello world!!";  } } 

i use sqldeveloper call procedures using following instructions

call test_proc.insert_test('test',1); #work call test_proc.hello_world(); #doesn't work 

when executing second instruction have following error ora-06576: not valid function or procedure name

do know how solve this? or know find working example of java stored procedure returning data in oracle database?

finally got result using following command:

select test_proc.hello_world() dual; 

result:

test_proc.hello_world()                                                               ------------------------------------------------------------------------------------- hello world!! 1 rows selected 

do know how return complex result database multiple row?

for pl/sql function returns something, cannot call in pl/sql if procedure. call function. try

var test varchar2(30) call test_proc.hello_world() :test; print test  test ------------------------------------------ hello world!! 

Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -