Oracle Stored Proc Question

Here's sample from Oracle JDBC demo. It contains both java part and the oracle procedure creation:

/*
 * This sample shows how to call a PL/SQL function that opens
 * a cursor and get the cursor back as a Java ResultSet.
 */

import java.sql.*;
import java.io.*;

// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.*;

class RefCursorExample
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    String url = "jdbc:oracle:oci8:@";
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }

    // Connect to the database
    Connection conn =
      DriverManager.getConnection (url, "scott", "tiger");

    // Create the stored procedure
    init (conn);

    // Prepare a PL/SQL call
    CallableStatement call =
      conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");

    // Find out all the SALES person
    call.registerOutParameter (1, OracleTypes.CURSOR);
    call.setString (2, "SALESMAN");
    call.execute ();
    ResultSet rset = (ResultSet)call.getObject (1);

    // Dump the cursor
    while (rset.next ())
      System.out.println (rset.getString ("ENAME"));

    // Close all the resources
    rset.close();
    call.close();
    conn.close();

  }

  // Utility function to create the stored procedure
  static void init (Connection conn)
       throws SQLException
  {
    Statement stmt = conn.createStatement ();

    stmt.execute ("create or replace package java_refcursor as " +
            "  type myrctype is ref cursor return EMP%ROWTYPE; " +
            "  function job_listing (j varchar2) return myrctype; " +
            "end java_refcursor;");

    stmt.execute ("create or replace package body java_refcursor as " +
            "  function job_listing (j varchar2) return myrctype is " +
            "    rc myrctype; " +
            "  begin " +
            "    open rc for select * from emp where job = j; " +
            "    return rc; " +
            "  end; " +
            "end java_refcursor;");
    stmt.close();
  }
}

If you had to create the above stored proc in oracle, what the right syntax is going to be. The reason that I am asking is that my syntax is compiled with warning errors.

- Nesifa

By the way, in SQL server you could return more then one resultset:
example

create procedure test1
as
  select * from emp;
  select * from dept;
return

then two resultset are returned back to Appplication that calls procedure test1.
Is that possible in oracle?
thanks
 - Nesifa

Hmm, I have used returning multiple recordsets through ADO with explicit SQL statements, but not with Java+Oracle stored procedure, but I think it could be done by oracle stored procedure with several OUT parameters, i.e. same logic - define cursors of the desired type and make Package with

Type myrctype1 IS REF cursor ;
Type myrctype2 IS REF cursor ;

Procedure MultyCursor(c1 OUT myrctype1, c2 OUT myrctype1...

open these cursors in the package body and retrieve them by
call.registerOutParameter(1, OracleTypes.CURSOR);
call.registerOutParameter(2, OracleTypes.CURSOR);
... set other required params and execute
rs1 = call.getObject(1);
rs2 = call.getObject(2);
where call is CallableStatement and you have imported
oracle.jdbc.driver.* to have access to OracleTypes.CURSOR

Honestly I have used the code above only with a single
OUT parameter, but can see no reason why it will not work with any number of them...

P.S. as for Java people knowing Oracle better then VB people, I think it's natural - if you use SQL server you stay on MS platform and use ADO, ASP-s, VB or C? etc. - same vendor less problems, but if you want to have crossplatform product with Oracle, and then you can  start with e.g. JDBC-JSP/Servlet-Java-Oracle