7 Replies Latest reply: Oct 12, 2010 3:43 AM by user533186 RSS

    ORA-00942: table or view does not exist  [Generic Connectivity Using ODBC]

    615123
      hi

      I make a connectivity between sql server and oracle 10g both are on same machine.

      my os is windows 2003 server.

      my listener points correctly.

      my tnsping database name.

      is also working fine.

      my database creation is ok.

      but when a work on it it gives me error like
      SQL> select count(*) from storemain@shop;
      select count(*) from storemain@shop
                           *
      ERROR at line 1:
      ORA-00942: table or view does not exist
      [Generic Connectivity Using ODBC]
      ORA-02063: preceding 2 lines from SHOP
      
      
      ping the database
      
      C:\Documents and Settings\DBA>tnsping shop
      
      TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 24-MAR-2008 00:21:04
      
      Copyright (c) 1997, 2003, Oracle.  All rights reserved.
      
      Used parameter files:
      C:\oracle\product\10.1.0\Neo\network\admin\sqlnet.ora
      
      
      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL= tcp)(HOST = 208.64.229.163)(PORT = 1522)) (CONNECT_DATA = (SID = shop)) (HS = OK))
      OK (30 msec)
      
      C:\Documents and Settings\DBA>
      
      
      status of the listener
      
      C:\Documents and Settings\DBA>lsnrctl start LISTENERSHOP
      
      LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 24-MAR-2008 00:20:56
      
      Copyright (c) 1991, 2004, Oracle.  All rights reserved.
      
      Starting tnslsnr: please wait...
      
      TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
      System parameter file is C:\oracle\product\10.1.0\Neo\network\admin\listener.ora
      Log messages written to C:\oracle\product\10.1.0\Neo\network\log\listenershop.log
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=208.64.229.163)(PORT=1522)))
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
      
      Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=208.64.229.163)(PORT=1522))
      STATUS of the LISTENER
      ------------------------
      Alias                     LISTENERSHOP
      Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
      Start Date                24-MAR-2008 00:20:57
      Uptime                    0 days 0 hr. 0 min. 0 sec
      Trace Level               off
      Security                  ON: Local OS Authentication
      SNMP                      OFF
      Listener Parameter File   C:\oracle\product\10.1.0\Neo\network\admin\listener.ora
      Listener Log File         C:\oracle\product\10.1.0\Neo\network\log\listenershop.log
      Listening Endpoints Summary...
        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=208.64.229.163)(PORT=1522)))
        (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))
      Services Summary...
      Service "shop" has 1 instance(s).
        Instance "shop", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully
      
      
      initshop.ora
      
      #
      HS_FDS_CONNECT_INFO = shop
      HS_FDS_TRACE_LEVEL = OFF
      i think i need to set some environment variable in initship.ora file but i don't know what.

      can any body help

      thanks

      Message was edited by:
      Umesh Sharma
        • 1. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using ODBC]
          ebangma-Oracle
          Hi,

          By default Microsoft SQL Server is case sensative. Meaning that object names can consist of mixed case. By default Oracle is uppercase oriented. The statement entered by you:

          "SQL> select count(*) from storemain@shop;"

          is being sent to SQL Server as: select count(*) from STOREMAIN;

          So if the "storemain" table/view is lowercase in SQL Server you need to surround it by double-quotes when using it in SQL*Plus to assure that Oracle is sending it 'as-is'.

          So try the following:
          SQL> select count(*) from "storemain"@shop;

          Regards.
          Ed
          • 2. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
            615123
            No it doen't work for me i got same error after doing it.

            like
            SQL> select count(*) from  "Storemain"@shop;
            select count(*) from  "Storemain"@shop
                                  *
            ERROR at line 1:
            ORA-00942: table or view does not exist
            [Generic Connectivity Using ODBC]
            ORA-02063: preceding 2 lines from SHOP
            thanks
            • 3. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
              ebangma-Oracle
              I'm sorry to hear that.

              What is the complete definition of your database link?

              And what is the result of the following query?
              SQL> select owner, table_name from all_tables@shop order by 1,2;

              Regards,
              Ed
              • 4. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
                615123
                select owner, table_name from all_tables@shops order by 1,2;
                
                OWNER                          TABLE_NAME
                ------------------------------ ------------------------------
                dbo                            Company_Master
                dbo                            Department_Master
                dbo                            Employee_Master
                dbo                            InventoryTrace
                dbo                            Invoice
                dbo                            Rec_interval
                dbo                            Recurring
                dbo                            Schedule_Master
                dbo                            Sheet1$
                dbo                            Store_Email_Settings
                dbo                            TESTIMONIALS
                
                OWNER                          TABLE_NAME
                ------------------------------ ------------------------------
                
                
                
                yes it is giving me list of some of the table but not all which i have in sqlserver
                
                
                even i can select in those given tables.
                
                i make a new database link like
                
                create database link shops
                connect to sa identified by "pwd"
                 using 'shop'
                ;
                
                even i can do it.
                
                
                SQL> select count(*) from shop_invoice_hf@shops;
                
                  COUNT(*)
                ----------
                        15
                
                
                but only at few tables not at all the tables.
                
                one more thing
                
                if a do all this for any another database it is working 
                
                fine means all the table of that database are shown.
                
                but only this database has problum.
                
                i think there is an rights issue in this sqlserver 
                
                database but 
                
                i don't know what.
                
                so plz help me to resolve this issue.
                thanks for help

                null
                • 5. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
                  ebangma-Oracle
                  I just realized your are using 10.1.0.2.0,

                  This issue looks like a known issue:
                  SQLSERVER Data type Uniqueidentifier is not supported by HSODBC 10.1.0.2

                  Could you please try the following workaround:
                  1. Create a view on the SQL SERVER table without the column that is defined as UNIQUEIDENTIFIER.
                  2. Use this View for any SELECTS from Oracle side using hsodbc.

                  Regards,
                  Ed
                  • 6. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
                    615123
                    Thnak u very much

                    it solve my problem.

                    thanks aganin
                    • 7. Re: ORA-00942: table or view does not exist  [Generic Connectivity Using OD
                      user533186
                      i found out that helps to open a new sqlplus/toad session and reexecute the query.
                      especially when you make changes for the hs