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

On Mar 30, 2:55 pm, code <kevinj..._at_gmail.com> wrote:
> 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-%d2%fe%b2%d8%b1%bb%d2%fd%d3%c3%ce%c4%d7%d6 -
>
> > - ÏÔʾÒýÓõÄÎÄ×Ö -
>
> 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;

1. Don't close the cursor being returned. Besides, CLOSE is unreachable because RETURN exits the block.
2. You don't need PL/SQL for this particular example.
3. You can't RETURN anything from an anonymous block, only from a stored procedure (function.) But you can use bind variables for this purpose. Consider this:

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

public class curtest {

public static void main(String[] argv)
{
  // resolve the JDBC driver class
  try
  {
    Class.forName(oracle.jdbc.driver.OracleDriver.class.getName());   }
  catch(ClassNotFoundException ex)
  {
    ex.printStackTrace();
  }
  try
  {
    OracleConnection conn =
(OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@localhost: 1521:BZ92","SCOTT","TIGER");
    OracleCallableStatement oracs =
    (OracleCallableStatement)conn.prepareCall("BEGIN OPEN ? FOR SELECT DEPTNO, DNAME FROM DEPT; END;");
    // register the output parameter
    oracs.registerOutParameter(1,OracleTypes.CURSOR);     try
    {

      // we don't expect true here
      if(oracs.execute())
        System.out.println("execute() returned true");
      else
        System.out.println("execute() returned false");
      // however, we'll retrieve our result set via the bind
variable...
      ResultSet rs = oracs.getCursor(1);
      // and scroll through it
      while(rs.next())
      {
        System.out.println(rs.getString(1)+" "+rs.getString(2));
      }

  }
  catch(SQLException sqlex)
  {
    sqlex.printStackTrace();
  }

}

}

$ java -classpath .:$ORACLE_HOME/jdbc/lib/ojdbc14.jar curtest

execute() returned false
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS There you have it, a cursor returned from an anonymous block.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Sun Mar 30 2008 - 09:50:20 CDT