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 _VIEWCOUNT see the recordset returned by the function JavaExternalDb.getTableCountRecord?