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

On 3ÔÂ30ÈÕ, ÏÂÎç5ʱ03·Ö, "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> wrote:
> On Mar 29, 7:42 pm, code <kevinj..._at_gmail.com> wrote:
>
>
>
>
>
> > 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;
>
> What's the exact Java code you tried? Which error is being thrown with
> this block (what's the exception stack trace?) "Can not work" is to
> vague to diagnose and offer a solution... If no exception is thrown
> then the block completes successfully, it simply doesn't return
> anything and doesn't modify anything as far as I can see, it just
> fetches a cursor in a do-nothing loop, so I wouldn't expect any output
> from it, and execute() should return false because there's no result
> set being returned.
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- Òþ²Ø±»ÒýÓÃÎÄ×Ö -
>
> - ÏÔʾÒýÓõÄÎÄ×Ö -

i am sorry, i fogot to return value. this is pl/sql. but execute() returned false., resultset is null. i want to get result from anonymous block. thanks very much.

CURSOR d_cursor is select deptno,dname from dept; begin
open d_cursor;
return d_cursor;
close d_cursor;
end; Received on Sun Mar 30 2008 - 05:55:54 CDT