8 Replies Latest reply on Apr 18, 2013 6:15 AM by devin pinkston

    Oracle Empty Model

    devin pinkston Newbie

      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>

        • 1. Re: Oracle Empty Model
          Ramesh Reddy Master

          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..

          • 2. Re: Oracle Empty Model
            devin pinkston Newbie

            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!

            • 4. Re: Oracle Empty Model
              devin pinkston Newbie

              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!

              • 5. Re: Oracle Empty Model
                Steven Hawkins Master

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

                 

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

                • 6. Re: Oracle Empty Model
                  devin pinkston Newbie

                  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!

                  • 7. Re: Oracle Empty Model
                    Steven Hawkins Master

                    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