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

On 3ÔÂ29ÈÕ, ÏÂÎç9ʱ06·Ö, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Mar 29, 8:47 am, code <kevinj..._at_gmail.com> 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; Received on Sat Mar 29 2008 - 10:42:33 CDT