Oracle Empty Model

[pinkstondevin]

Hello!

I am trying to integrate an Oracle database in Teiid, I can create a connection through the admin console successfully to my data source.  I can see my table through sqlplus in Oracle as well, however I keep receiving errors when defining my Oracle data source.  Do I need to create a view in order to use Oracle?  I have tried switching properties around in my VDB with no luck.

Thanks in advance!

Error:

11:10:06,117 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4)  TEIID31070 Empty model;There are no tables, procedures or functions defined in this model oracleBaseball

11:10:06,119 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4)  TEIID40073 The metadata for the VDB Portfolio.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

11:10:06,120 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4)  TEIID40003 VDB Portfolio.1 is set to FAILED

VDB:

<model name="oracleBaseball">

<property name="importer.tableTypes" value="TABLE,VIEW"/>

<property name="importer.schemaPattern" value="my-schema"/>

<property name="importer.useFullSchemaName" value="true"/>

<source name="oracle-connector" translator-name="oracle" connection-jndi-name="java:/Oracle-DS"/>

</model>

[rareddy]

Is there a schema called "my-schema" in your source? When you define the "importer.*" properties, the translator filters the metadata to match to what is specified. If none matched that is the issue you are seeing.

Ramesh..

[pinkstondevin]

Ramesh,

No there is not a "my-schema".  I have a sample oracle database created for testing called "baseball", with just one table "master". 

If I remove the importer.schemaPattern, Teiid gives a long list of invalid tables that "has no columns defined".  Here is just a snippet of the list, it is very long:

12:02:27,385 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_RENDER_CHART2 has no columns defined

12:02:27,385 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_SEQ has no columns defined

12:02:27,386 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.WWV_SPELING has no columns defined

12:02:27,386 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB$STRING_LIST_T has no columns defined

12:02:27,387 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB_PVTRIG_PKG has no columns defined

12:02:27,387 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDB_RVTRIG_PKG has no columns defined

12:02:27,388 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDBURITYPE has no columns defined

12:02:27,388 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDS_ACE has no columns defined

12:02:27,389 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XDS_ACL has no columns defined

12:02:27,389 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XIMETADATA_PKG has no columns defined

12:02:27,389 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLAGG has no columns defined

12:02:27,390 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLBINARYINPUTSTREAM has no columns defined

12:02:27,390 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLBINARYOUTPUTSTREAM has no columns defined

12:02:27,391 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLCHARACTERINPUTSTREAM has no columns defined

12:02:27,391 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLCHARACTEROUTPUTSTREAM has no columns defined

12:02:27,392 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLDOM has no columns defined

12:02:27,392 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLFORMAT has no columns defined

12:02:27,393 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLPARSER has no columns defined

12:02:27,393 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLSEQUENCE has no columns defined

12:02:27,394 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLSEQUENCETYPE has no columns defined

12:02:27,394 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XMLTYPE has no columns defined

12:02:27,395 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XQSEQUENCE has no columns defined

12:02:27,395 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XQWINDOWSEQUENCE has no columns defined

12:02:27,396 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS$CACHE_ACTIONS has no columns defined

12:02:27,396 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS$CACHE_DELETE has no columns defined

12:02:27,397 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XS_SESSION_ROLES has no columns defined

12:02:27,397 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.XSLPROCESSOR has no columns defined

12:02:27,398 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.PUBLIC.Z has no columns defined

12:02:27,398 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_NAME has no columns defined

12:02:27,399 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_TYPE has no columns defined

12:02:27,402 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID40073 The metadata for the VDB Portfolio.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

12:02:27,403 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID40003 VDB Portfolio.1 is set to FAILED

My VDB when i do this:

<?xml version="1.0" ?><vdb name="Portfolio" version="1">

<description>The Portfolio Dynamic VDB</description>

<!--

   <model name="weather">

        <source name="textWeather-connector" translator-name="file" connection-jndi-name="java:/weather-file"/>

    </model>

<model visible = "true" type = "VIRTUAL" name = "weatherstats">

        <metadata type = "DDL"><![CDATA[ 

            CREATE VIEW edinburgh (

            theIndex integer,

            theYear integer,

            theDay integer,

            theTime integer,

            ap integer,

            rainfall double,

            windspeed double,

            temp double

            ) OPTIONS (UPDATABLE TRUE) AS

            select edinburgh.* from (call weather.getTextFiles('*.txt')) f, TEXTTABLE(f.file COLUMNS theIndex integer, theYear integer, theDay integer, theTime integer, ap integer, rainfall double, windspeed double, temp double HEADER) edinburgh;

            ]]>

        </metadata>

    </model>

    <model name="fieldingPostgres">

        <source name="fielding-connector" translator-name="postgresql" connection-jndi-name="java:/postgres-ds"/>

    </model>

-->

    <property name="UseConnectorMetadata" value="true" />

<model name="oracleBaseball">

        <property name="importer.useFullSchemaName" value="true"/>

<source name="oracle-connector" translator-name="oracle" connection-jndi-name="java:/Oracle-DS"/>

</model>

</vdb>

I have my two other sources commented out so i can focus on Oracle.  When I used value of "false" for useFullSchemaName I received duplicated tables error, so i changed it to true for now.

Thanks Ramesh!

[shawkins]

If these tables are synonyms, you should set the oracle connection property includeSynonyms=true - http://docs.oracle.com/cd/E18283_01/appdev.112/e13995/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_INCLUDE_SYNONYMS

[pinkstondevin]

Steven,

Thanks for your quick response.  I do not have synonyms.  I did read a couple posts on the community here about that just in case.

My Oracle DB is on a windows server, I can query it and see the contents of the table using the user given to Teiid.  i can also create a connection to the data source through Teiid's web console and it connects successfully.  However I am just having trouble with the Virtual Database.

Thanks!

[shawkins]

I think you are importing too many object types.  Try narrowing down, for example:

<property name="importer.tableTypes" value="TABLE,VIEW"/>

[pinkstondevin]

Steven,

Thanks for the tip.  If i use the property mentioned like below, I receive a different error I received before when trying the tabletypes property.

What do you think?

<?xml version="1.0" ?><vdb name="Portfolio" version="1">

<description>The Portfolio Dynamic VDB</description>

    <property name="UseConnectorMetadata" value="true" />

<model name="oracleBaseball">

        <property name="importer.useFullSchemaName" value="true"/>

        <property name="importer.tableTypes" value="TABLE,VIEW"/>

<source name="oracle-connector" translator-name="oracle" connection-jndi-name="java:/Oracle-DS"/>

</model>

</vdb>

Error:

12:48:38,615 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50030 VDB Portfolio.1 model "oracleBaseball" metadata loaded. End Time: 4/17/13 12:48 PM

12:48:38,626 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_NAME has no columns defined

12:48:38,627 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 1)  TEIID31071 Invalid table; Table oracleBaseball.SYS.HS_PARTITION_COL_TYPE has no columns defined

12:48:38,630 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID40073 The metadata for the VDB Portfolio.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

12:48:38,631 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID40003 VDB Portfolio.1 is set to FAILED

Thanks again!

[shawkins]

Notice how you have exluded things like oracleBaseball.PUBLIC.XMLAGG, which is an aggregate function definition and not a table.  However you are still pulling things from the SYS schema, so more than likely you should limit yourself to a specific schema using <property name="importer.schemaPattern" value="my-schema"/>

If you do need to go across several schemas, then you would probably want to use the excludeTables regex to filter anything that is not needed and is causing a problem.

Steve 

[pinkstondevin]

Thanks Steven!