I need to select all the data in one table located in a Sybase RDBMS and insert into a table in an Oracle RDBMS.  I can not use the Transparent Gateway because we have not purchased it and management does not have the money.  So, that leave me with using a Java/jdbc solution.

I thought I would Load the Java classes/packages that I needed into the Oracle RDBMS using JavaLoad.  Create a Java Stored Procedure to select all the data in a table located in a Sybase RDBMS and return the record set to the calling procedure so that I can perform a bulk load into a table located into Oracle.

I have figured out how to connect to the Sybase RDBMS and select the data.  Now I need to figure out how I can return the record set to the Calling PL/SQL procedure.

Any ideas how to do this?

Here is what I have come up with so far.  With this code I can connect to the Sybase RDBMS and select data from the the Sybase Table.  Now how would I return the RecordSet/Result Set to the calling Oracle PL/SQL Procedure?

import com.sybase.jdbcx.SybResultSet;
import com.sybase.jdbcx.SybConnection;
import com.sybase.jdbcx.Debug;
//import com.sybase.jdbcx.SybD
//import com.sybase.jdbcx.SybMessageHandler;
import com.sybase.jdbc2.jdbc.*;
import com.sybase.jdbc2.jdbc.SybDriver;
import java.sql.*;

public class JConnect {

      /**
       * @param args
       */
      public static void main(String[] args) {
            // TODO Auto-generated method stub
            SybConnection conn = null;
            SybStatement stmt = null;
            SybDriver drv = null;
            SybResultSet rs = null;
            Debug bug = null;
            try {
            drv = (SybDriver)Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
            DriverManager.registerDriver((Driver)drv);
            DriverManager.setLoginTimeout(5);
            System.out.println("Driver Loaded");
            
            bug = drv.getDebug();
            //bug.debug(true,"ALL",System.out);
            
            conn = (SybConnection) DriverManager.getConnection("jdbc:sybase:Tds:192.168.65.32:5500/regstr_ext","userid","password");
            
            
            stmt = (SybStatement) conn.createStatement();
            //stmt.setQueryTimeout(10);
            
            rs = (SybResultSet)stmt.executeQuery("select index_name from database.owner.table where id = '123456'");
      
            if(rs.next()){
                String value = rs.getString(1);
                  //int value = rs.getInt(1);
                  System.out.println("Fetch value: " + value);
            }
            
            //bug.println(this,"Query:" + query);
            rs.close();
            stmt.close();
            conn.close();
            System.out.println("Finished up.");
            
            } catch(Exception e) {
                  System.out.println("Exception...");
                  System.out.println(e.getMessage());
                  e.printStackTrace();
            }
            System.exit(0);
      }

}