How to create a view that fetches all records from a jdbc connection?

[pietrograssi68]

I have a VIEW (VIEW_COUNT) that returns 1 record with the number of rows from two external tables fetched via jdbc connection.
Below the code:

public class JavaExternalDb
{
  public static int getTableCountRecord (String tabName) throws SQLException
  {
	Connection conn = DriverManager.getConnection("jdbc:oracle:thin:USER/PWD@HOST:PORT:SID");
        String sql = "SELECT count(*) FROM " + tabName;
	Statement stmt = conn.createStatement();
	ResultSet rset = stmt.executeQuery(sql);
        int rows = 0;
	while (rset.next())
	{
	     rows = rset.getInt(1);
	}
	rset.close();
	stmt.close();
        return rows;
  }
}

CREATE OR REPLACE FUNCTION FUNC_COUNT (tab_name VARCHAR2)
   RETURN NUMBER AS LANGUAGE JAVA
   NAME 'JavaExternalDb.getTableCountRecord(java.lang.String) return int';
/

CREATE OR REPLACE FORCE VIEW VIEW_COUNT ("COUNT_TAB1", "COUNT_TAB2") AS 
  select FUNC_COUNT('tab1name'), FUNC_COUNT('tab2name') from dual;
/

How can I, likewise, create a VIEW that returns all the contents of an external table via jdbc?

Thanks for the info

[Kuassim]

Hi @pietrograssi68,
DId the code you have in getTableCountRecord() work?
If so
Have you tried replacing String sql = "SELECT count(*) FROM " + tabName;
with String sql = "SELECT * FROM " + tabName; ?

[pietrograssi68]

Hi @Kuassim,
yes of course with this change the first step would be to return a recordset and not an integer. The problem is the following steps: how to make the view VIEW_COUNT see the recordset returned by the function JavaExternalDb.getTableCountRecord?