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

On Mar 29, 11:42 am, 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;- Hide quoted text -
>
> - Show quoted text -

Did you put BEGIN and END when testing. That is very important if I remember correctly. Received on Sat Mar 29 2008 - 11:03:59 CDT