Re: Need simple stored procedure for JDBC test

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 02 Jul 1999 14:48:56 GMT
Message-ID: <3785d14c.150818675@newshost.us.oracle.com>

A copy of this was sent to JavaOne <chris63_at_my-deja.com> (if that email address didn't require changing) On Fri, 02 Jul 1999 13:39:12 GMT, you wrote:

>
>
>Folks:
>I'm using Oracle Enterprise Manager 8.0.5 on NT 4.0
>Assume I have some table named "Fred"
>
>I want to create a stored procedure or function that I can call from
>JDBC that returns all of the rows of Fred. I don't know what the
>DDL would look like, although I know its something like:
>
>create function <returnType>? readFred()
>BEGIN
> return select * from Fred;
>END
>
>Can someone tell me what the DDL would be to create either a stored
>procedure or function to do this?
>
>Email replies appreciated.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

[snip]

In short, it'll look like this:

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;     return l_cursor;
end;
/

With 7.2 on up of the database you have cursor variables. Cursor variables are cursors opened by a pl/sql routine and fetched from by another application or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as well as open them). The cursor variables are opened with the privelegs of the owner of the procedure and behave just like they were completely contained within the pl/sql routine. It uses the inputs to decide what database it will run a query on.

Here is an example:

create or replace package types
as

    type cursorType is ref cursor;
end;
/  

create or replace function sp_ListEmp return types.cursortype as

    l_cursor types.cursorType;
begin

    open l_cursor for select ename, empno from emp order by ename;  

    return l_cursor;
end;
/    

REM SQL*Plus commands to use a cursor variable  

variable c refcursor
exec :c := sp_ListEmp
print c

And the java to use this could be:

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

class curvar
{
  public static void main (String args [])

                     throws SQLException, ClassNotFoundException
  {
      String driver_class = "oracle.jdbc.driver.OracleDriver";
      String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";

      String query = "begin :1 := sp_listEmp; end;";
      Connection conn;

      Class.forName(driver_class);
      conn = DriverManager.getConnection(connect_string, "scott", "tiger");

      CallableStatement cstmt = conn.prepareCall(query);
      cstmt.registerOutParameter(1,OracleTypes.CURSOR);
      cstmt.execute();
      ResultSet rset = (ResultSet)cstmt.getObject(1);

      while (rset.next ())
        System.out.println( rset.getString (1) );
      cstmt.close();

  }
}

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 02 1999 - 09:48:56 CDT