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

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 Received on Sun Mar 30 2008 - 04:03:03 CDT