Re: help!! how to call pl/sql anonymous block from java?

On Mar 29, 11:42 am, code <kevinj...@xxxxxxxxx> wrote:
On 3月29日, 下午9时06分, "Vladimir M. Zakharychev"





<vladimir.zakharyc...@xxxxxxxxx> wrote:
On Mar 29, 8:47 am, code <kevinj...@xxxxxxxxx> wrote:

I know that jdbc can call a pl/sql package or procedure,but if i
want to call a pl/sql anonymous block, how can i do it? no procedure
name or package name will be offered.
Can u give me a sample code? thanks very much

Just use a CallableStatement or OracleCallableStatement:

import java.sql.*;
import oracle.jdbc.*;
...

try
{
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:@dbhost:
1521:ORCL","SCOTT","TIGER");
OracleCallableStatement oracs =
((OracleConnection)conn).prepareCall("BEGIN do_something; END;");
try
{
if (oracs.execute())
{
ResultSet rs = oracs.getResultSet();
// process the result set here
}
}
catch(SQLException ex)
{
System.out.println("Exception in execute phase: ");
ex.printStackTrace();
}
finally
{
// we want to close the statement regardless if it failed or not
oracs.close();
}}

catch(SQLException sqlex)
{
System.out.println("General SQL exception encountered:");
sqlex.printStackTrace();

}

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

thanks very much.
but it can not work. here:oracs.execute())
this is my pl/sql

declare

deptno dept.deptno%TYPE;

dname dept.dname%TYPE;

CURSOR d_cursor is select deptno,dname from dept;

begin

open d_cursor;

loop

fetch d_cursor into deptno,dname;

exit when d_cursor%NOTFOUND;

--DBMS_OUTPUT.PUT_LINE('d='||deptno||',n='||dname);
end loop;

close d_cursor;

end;- Hide quoted text -

- Show quoted text -

Did you put BEGIN and END when testing. That is very important if I
remember correctly.
.

Relevant Pages