Example of OracleDatabaseMetaData using OAS datasources to display table and view data

Posted by Steve Racanovic | Posted in , | Posted on 3:10 PM

0

A simple example of using oracle database metadata (from JDBC) with Oracle Application Server 1013x datasources to display the data.

1. My data-sources.xml looks like:

<managed-data-source connection-pool-name="scottPool2" jndi-name="jdbc/ScottDS2"
name="jdbc/ScottDS2"/>

<connection-pool name="scottPool2">
   <connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott" password="tiger" url="jdbc:oracle:thin:@//sracanov-au2.au.oracle.com:1522/orcl"/>

</connection-pool>

2. My java code in jdev looks like:

package examples;

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.Properties;

import javax.naming.Context;
import javax.naming.InitialContext;

import javax.sql.DataSource;

import oracle.jdbc.OracleDatabaseMetaData;

public class TestOracleDatabaseMetaDataDataSource {
public static void main(String[] args) throws Exception {
Properties props = new Properties();
props.put(Context.PROVIDER_URL,
"opmn:ormi://sracanov-au2.au.oracle.com:6005:OC4J_Apps/default");
props.put(Context.INITIAL_CONTEXT_FACTORY,
"oracle.j2ee.rmi.RMIInitialContextFactory");
props.put(Context.SECURITY_PRINCIPAL, "oc4jadmin");
props.put(Context.SECURITY_CREDENTIALS, "welcome1");
Context ctx = new InitialContext(props);
DataSource ds = (DataSource)ctx.lookup("jdbc/ScottDS2");
Connection conn = ds.getConnection();
OracleDatabaseMetaData dbmd =
(OracleDatabaseMetaData)conn.getMetaData();
ResultSet rs =
dbmd.getTables(null, "%", "%", new String[] { "TABLE", "VIEW" });
while (rs.next()) {
System.out.println(" " + rs.getString(3) + " : " +
rs.getString(4));
}
}

}
3. Ensure the following libraries are in the project:

* JDBC driver
* Apache Ant
* JSP Runtime

4. Run the class and the results are displayed:
      DR$NUMBER_SEQUENCE : TABLE
DR$OBJECT_ATTRIBUTE : TABLE
DR$POLICY_TAB : TABLE
RLM$PARSEDCOND : TABLE
OGIS_GEOMETRY_COLUMNS : TABLE
OGIS_SPATIAL_REFERENCE_SYSTEMS : TABLE
....
....
....
WM$REPLICATION_INFO : VIEW
WM$TABLE_NEXTVERS_VIEW : VIEW
WM$TABLE_PARVERS_VIEW : VIEW
WM$TABLE_VERSIONS_IN_LIVE_VIEW : VIEW
WM$TABLE_WS_PARVERS_VIEW : VIEW
WM$VERSIONS_IN_LIVE_VIEW : VIEW
WM$VER_BEF_INST_NEXTVERS_VIEW : VIEW
WM$VER_BEF_INST_PARVERS_VIEW : VIEW
WM_EVENTS_INFO : VIEW
PATH_VIEW : VIEW
RESOURCE_VIEW : VIEW
Results of the output have been truncated.

Comments (0)