14 Replies Latest reply: May 18, 2012 10:23 AM by Mkirtley-Oracle RSS

    11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure

    556459
      Hi,

      Recently set up Oracle Heteregenous Gateway to connect to an MS ACCESS DB running on a local Windows Server. I can run straight SQL (using PL/SQL Developer) to access the tables fine via a DB link but if I put the code inside a stored procedure when I try to compile it I get an "ORA-00942 table or view does not exist"!

      Anyone experienced this and can advise me what to do to get around the problem please?

      Thanks,

      Jeremy
        • 1. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
          Mkirtley-Oracle
          Hi Jeremy,
          Can you give us an example of the procedure that causes the problem and the code that runs from SQLPLUS ? PL/SQL behaves differently from SQLPLUS especially with names and permissions, so an example would be useful.

          Regards,
          Mike
          • 2. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
            Mkirtley-Oracle
            Jeremy,
            One thing to check is that the privileges on the gateway objects were granted directly and not via a role, that is a common cause of ORA-942 from PL/SQL procedures.

            Regards,
            Mike
            • 3. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
              556459
              Thanks,

              Here's the basic SQL:
              SELECT "JobNumber"
              FROM jobs@paperworks3

              (paperworks3 being the database link set up for me and jobs being one of the MS Access tables)

              Here's the PLSQL equivalent that throws the ORA-00942:
              create OR replace PROCEDURE get_ppw_data is

              BEGIN
              FOR cur IN
              (
              SELECT "JobNumber"
              FROM jobs@paperworks3

              )
              LOOP
              NULL;
              END loop;
              END get_ppw_data;

              Thanks,

              Jeremy
              • 4. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                Mkirtley-Oracle
                Jeremy,
                What happens if you change the select in the procedure to -


                BEGIN
                FOR cur IN
                (
                SELECT "JobNumber"
                FROM "jobs"@paperworks3

                )
                LOOP
                NULL;
                END loop;
                END get_ppw_data;

                Do you still get the same error ?
                If you do then could you set up gateway debug tracing by setting the following in the gateway init<sid>.ora -

                HS_FDS_TRACE_LEVEL=debug

                and run the procedure from a new SQLPLUS session.
                Post the lines that come after the line which should be something like -

                SQL text from hgopars, ....

                and then should show an error for the object being selected, again something like -

                Entered hgopoer at 2008/12/11-14:22:56
                hgopoer, line 159: got native error 208 and sqlstate 42S02; message follows...

                although the error numbers may be different.
                Did you check how the privileges had been granted ?

                Regards,
                Mike
                • 5. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                  556459
                  Thanks Mike,

                  sorry our ADSL went down. Not quite sure what you mean about "privileges on the gateway objects were granted directly". Where are they and how would I do that?

                  Also changed the procedure as you suggested but no difference.

                  Where would I find the gateway init<sid>.ora - would that be on the Windows Server?

                  Sorry, flying blind here as this service is a bit new to me. Thanks for your help so far!

                  Jeremy
                  • 6. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                    556459
                    OK Mike, found the file and put the option in. The procedure wont even compile so I cant run it. Please advise further steps if there are any? Jeremy
                    • 7. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                      Mkirtley-Oracle
                      Jeremy,
                      I don't think the privileges are a problem here. It would only be an issue if you were, for example, using a synonym or view for the gateway objects. So, instead of -

                      jobs@paperworks3

                      you had -

                      jobs_gateway

                      and the privileges on job_gateway were given via a role.

                      the gateway init<sid..ora file will be in the directory -

                      $ORACLE_HOME\hs\admin

                      if DG4ODBC is running on Windows, where $ORACLE_HOME is the directory in which the gateway has been installed.
                      What would be useful is a trace from running the directly select in SQLPLUS and then running the select as a PL/SQL procedure.
                      There can be problems with quotes when running from PL/SQL so we need to see what is being sent to Access in each case.

                      I'll also test on our systems to see if I can reproduce.

                      Regards,
                      Mike
                      • 8. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                        Mkirtley-Oracle
                        Jeremy,
                        I made my last update before I saw your last one.
                        Trying to compile the procedure should still have called the gateway so you should have a trace file generated.
                        Is the user running the procedure the same user that created the database link or is it using a public database link ?

                        Regards,
                        Mike
                        • 9. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                          556459
                          Mike,

                          Ok here is the output from the Trace file. To answer your other question the synonym is a public one but I also created a new one within the schema that is trying to access the gateway but this does not work either:
                          CREATE DATABASE LINK paperworks CONNECT TO "administrator" IDENTIFIED BY "<password>" USING 'paperworks';

                          Here is the output hope you can make head nor tail of it!

                          Oracle Corporation --- FRIDAY MAY 18 2012 12:34:32.075


                          Heterogeneous Agent Release
                          11.1.0.7.0




                          Oracle Corporation --- FRIDAY MAY 18 2012 12:34:32.059

                          Version 11.1.0.7.0

                          Entered hgogprd
                          HOSGIP for "HS_FDS_TRACE_LEVEL" returned "debug"
                          Entered hgosdip
                          setting HS_OPEN_CURSORS to default of 50
                          setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
                          setting HS_FDS_RECOVERY_PWD to default value
                          setting HS_FDS_TRANSACTION_LOG to default of "HS_TRANSACTION_LOG"
                          setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
                          setting HS_NLS_NCHAR to default of "UCS2"
                          setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
                          setting HS_RPC_FETCH_REBLOCKING to default of "ON"
                          setting HS_FDS_FETCH_ROWS to default of "100"
                          setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
                          setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
                          setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
                          setting HS_FDS_MAP_NCHAR to default of "TRUE"
                          setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
                          setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
                          setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
                          setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
                          setting HS_FDS_QUERY_DRIVER to default of "TRUE"
                          HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
                          Parameter HS_FDS_QUOTE_IDENTIFIER is not set
                          setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
                          Exiting hgosdip, rc=0
                          ORACLE_SID is "paperworks"
                          Product-Info:
                          Port Rls/Upd:7/0 PrdStat:0
                          Agent:Oracle Database Gateway for ODBC
                          Facility:hsa
                          Class:ODBC, ClassVsn:11.1.0.7.0_0006, Instance:paperworks
                          Exiting hgogprd, rc=0
                          Entered hgoinit
                          HOCXU_COMP_CSET=1
                          HOCXU_DRV_CSET=178
                          HOCXU_DRV_NCHAR=1000
                          HOCXU_DB_CSET=31
                          HOCXU_SEM_VER=110000
                          Entered hgolofn at 2012/05/18-12:34:32
                          Exiting hgolofn, rc=0 at 2012/05/18-12:34:32
                          HOSGIP for "HS_OPEN_CURSORS" returned "50"
                          HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
                          HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
                          HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
                          Exiting hgoinit, rc=0 at 2012/05/18-12:34:32
                          Entered hgolgon at 2012/05/18-12:34:32
                          reco:0, name:administrator, tflag:0
                          Entered hgosuec at 2012/05/18-12:34:32
                          Exiting hgosuec, rc=0 at 2012/05/18-12:34:32
                          HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
                          HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""
                          HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"
                          HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
                          HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
                          HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"
                          HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
                          HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
                          using administrator as default value for "HS_FDS_DEFAULT_OWNER"
                          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                          Entered hgocont at 2012/05/18-12:34:32
                          HS_FDS_CONNECT_INFO = "paperworks"
                          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                          Entered hgogenconstr at 2012/05/18-12:34:32
                          dsn:paperworks, name:administrator
                          optn:
                          ##>Connect Parameters (len=51)<##
                          ## DSN=paperworks;
                          #! UID=administrator;
                          #! PWD=*
                          Exiting hgogenconstr, rc=0 at 2012/05/18-12:34:32
                          DriverName:odbcjt32.dll, DriverVer:04.00.6305
                          DBMS Name:ACCESS, DBMS Version:04.00.0000
                          Exiting hgocont, rc=0 at 2012/05/18-12:34:32
                          SQLGetInfo returns Y for SQL_CATALOG_NAME
                          SQLGetInfo returns 260 for SQL_MAX_CATALOG_NAME_LEN
                          Exiting hgolgon, rc=0 at 2012/05/18-12:34:32
                          Entered hgoulcp at 2012/05/18-12:34:32
                          Entered hgowlst at 2012/05/18-12:34:32
                          Exiting hgowlst, rc=0 at 2012/05/18-12:34:32
                          SQLGetInfo returns ` for SQL_IDENTIFIER_QUOTE_CHAR
                          SQLGetInfo returns Y for SQL_COLUMN_ALIAS
                          3 instance capabilities will be uploaded
                          capno:1989, context:0x00000000, add-info: 0
                          capno:1991, context:0x0001ffff, add-info: 0
                          capno:1992, context:0x0001ffff, add-info: 1, translation:"`"
                          Exiting hgoulcp, rc=0 at 2012/05/18-12:34:32
                          Entered hgouldt at 2012/05/18-12:34:32
                          0 instance DD translations were uploaded
                          Exiting hgouldt, rc=0 at 2012/05/18-12:34:32
                          Entered hgobegn at 2012/05/18-12:34:32
                          tflag:0 , initial:1
                          hoi:0x12f074, ttid (len 29) is ...
                          00: 47454E41 5050532E 63393631 63623061 [GENAPPS.c961cb0a]
                          10: 2E32352E 31362E31 31313234 38 [.25.16.111248]
                          tbid (len 26) is ...
                          00: 47454E41 5050535B 32352E31 362E3131 [GENAPPS[25.16.11]
                          10: 31323438 5D5B312E 315D [1248][1.1]]
                          TXN Capable:2, Isolation Option:0x2
                          Exiting hgobegn, rc=0 at 2012/05/18-12:34:32
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure administrator.JOBS
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          Entered hgodtab at 2012/05/18-12:34:32
                          count:1
                          table: administrator.JOBS
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure administrator.JOBS
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          The hoada for table administrator.JOBS follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:32
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:32
                          type:0
                          SQL text from hgopars, id=1, len=36 ...
                          00: 53454C45 4354202A 2046524F 4D206061 [SELECT * FROM `a]
                          10: 646D696E 69737472 61746F72 602E604A [dministrator`.`J]
                          20: 4F425360 [OBS`]
                          Entered hgopoer at 2012/05/18-12:34:32
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\administrator.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:32
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:32 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgodtab at 2012/05/18-12:34:32
                          count:1
                          table: DUAL
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure administrator.DUAL
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          The hoada for table DUAL follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:32
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:32
                          type:0
                          SQL text from hgopars, id=1, len=20 ...
                          00: 53454C45 4354202A 2046524F 4D206044 [SELECT * FROM `D]
                          10: 55414C60 [UAL`]
                          Entered hgopoer at 2012/05/18-12:34:32
                          hgopoer, line 159: got native error -1305 and sqlstate 42S02; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'DUAL'. Make sure it exists and that its name is spelled correctly.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:32
                          hgopars, line 367: calling SQLNumResultCols got sqlstate 42S02
                          Exiting hgopars, rc=942 at 2012/05/18-12:34:32
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure administrator.JOBS
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:32
                          type:0
                          SQL text from hgopars, id=1, len=36 ...
                          00: 53454C45 4354202A 2046524F 4D206061 [SELECT * FROM `a]
                          10: 646D696E 69737472 61746F72 602E604A [dministrator`.`J]
                          20: 4F425360 [OBS`]
                          Entered hgopoer at 2012/05/18-12:34:32
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\administrator.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:32
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:32 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure PUBLIC.JOBS
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          Entered hgodtab at 2012/05/18-12:34:32
                          count:1
                          table: PUBLIC.JOBS
                          Entered hgopdsc at 2012/05/18-12:34:32
                          Describing procedure PUBLIC.JOBS
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:32
                          The hoada for table PUBLIC.JOBS follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:32
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:32
                          type:0
                          SQL text from hgopars, id=1, len=29 ...
                          00: 53454C45 4354202A 2046524F 4D206050 [SELECT * FROM `P]
                          10: 55424C49 43602E60 4A4F4253 60 [UBLIC`.`JOBS`]
                          Entered hgopoer at 2012/05/18-12:34:32
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\PUBLIC.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:32
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:32 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgocomm at 2012/05/18-12:34:32
                          keepinfo:0, tflag:1
                          00: 47454E41 5050532E 63393631 63623061 [GENAPPS.c961cb0a]
                          10: 2E32352E 31362E31 31313234 38 [.25.16.111248]
                          tbid (len 26) is ...
                          00: 47454E41 5050535B 32352E31 362E3131 [GENAPPS[25.16.11]
                          10: 31323438 5D5B312E 315D [1248][1.1]]
                          cmt(0):
                          Entered hgocpctx at 2012/05/18-12:34:32
                          Exiting hgocpctx, rc=0 at 2012/05/18-12:34:32
                          Exiting hgocomm, rc=0 at 2012/05/18-12:34:32
                          Entered hgobegn at 2012/05/18-12:34:38
                          tflag:0 , initial:0
                          hoi:0x12f074, ttid (len 29) is ...
                          00: 47454E41 5050532E 63393631 63623061 [GENAPPS.c961cb0a]
                          10: 2E32332E 31372E31 31393639 34 [.23.17.119694]
                          tbid (len 26) is ...
                          00: 47454E41 5050535B 32332E31 372E3131 [GENAPPS[23.17.11]
                          10: 39363934 5D5B312E 315D [9694][1.1]]
                          Exiting hgobegn, rc=0 at 2012/05/18-12:34:38
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure administrator.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          Entered hgodtab at 2012/05/18-12:34:38
                          count:1
                          table: administrator.jobs
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure administrator.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          The hoada for table administrator.jobs follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:38
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:38
                          type:0
                          SQL text from hgopars, id=1, len=36 ...
                          00: 53454C45 4354202A 2046524F 4D206061 [SELECT * FROM `a]
                          10: 646D696E 69737472 61746F72 602E606A [dministrator`.`j]
                          20: 6F627360 [obs`]
                          Entered hgopoer at 2012/05/18-12:34:38
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\administrator.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:38
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:38 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgodtab at 2012/05/18-12:34:38
                          count:1
                          table: DUAL
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure administrator.DUAL
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          The hoada for table DUAL follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:38
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:38
                          type:0
                          SQL text from hgopars, id=1, len=20 ...
                          00: 53454C45 4354202A 2046524F 4D206044 [SELECT * FROM `D]
                          10: 55414C60 [UAL`]
                          Entered hgopoer at 2012/05/18-12:34:38
                          hgopoer, line 159: got native error -1305 and sqlstate 42S02; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'DUAL'. Make sure it exists and that its name is spelled correctly.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:38
                          hgopars, line 367: calling SQLNumResultCols got sqlstate 42S02
                          Exiting hgopars, rc=942 at 2012/05/18-12:34:38
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure administrator.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:38
                          type:0
                          SQL text from hgopars, id=1, len=36 ...
                          00: 53454C45 4354202A 2046524F 4D206061 [SELECT * FROM `a]
                          10: 646D696E 69737472 61746F72 602E606A [dministrator`.`j]
                          20: 6F627360 [obs`]
                          Entered hgopoer at 2012/05/18-12:34:38
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\administrator.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:38
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:38 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure PUBLIC.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          Entered hgodtab at 2012/05/18-12:34:38
                          count:1
                          table: PUBLIC.jobs
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure PUBLIC.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          The hoada for table PUBLIC.jobs follows...
                          hgodtab, line 651: NO hoada to print
                          Exiting hgodtab, rc=0 at 2012/05/18-12:34:38
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:38
                          type:0
                          SQL text from hgopars, id=1, len=29 ...
                          00: 53454C45 4354202A 2046524F 4D206050 [SELECT * FROM `P]
                          10: 55424C49 43602E60 6A6F6273 60 [UBLIC`.`jobs`]
                          Entered hgopoer at 2012/05/18-12:34:38
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\PUBLIC.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:38
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:38 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgopdsc at 2012/05/18-12:34:38
                          Describing procedure PUBLIC.jobs
                          Output hoada
                          hgopdsc, line 1298: NO hoada to print
                          Exiting hgopdsc, rc=942 at 2012/05/18-12:34:38
                          Entered hgopars, cursor id 1 at 2012/05/18-12:34:38
                          type:0
                          SQL text from hgopars, id=1, len=29 ...
                          00: 53454C45 4354202A 2046524F 4D206050 [SELECT * FROM `P]
                          10: 55424C49 43602E60 6A6F6273 60 [UBLIC`.`jobs`]
                          Entered hgopoer at 2012/05/18-12:34:38
                          hgopoer, line 159: got native error -1811 and sqlstate HY000; message follows...
                          [Microsoft][ODBC Microsoft Access Driver] Could not find file 'C:\WINDOWS\system32\PUBLIC.mdb'.
                          Exiting hgopoer, rc=0 at 2012/05/18-12:34:38
                          hgopars, line 367: calling SQLNumResultCols got sqlstate HY000
                          Exiting hgopars, rc=28500 at 2012/05/18-12:34:38 with error ptr FILE:hgopars.c LINE:397 ID:Preprocess number of columns
                          Entered hgocomm at 2012/05/18-12:34:38
                          keepinfo:0, tflag:1
                          00: 47454E41 5050532E 63393631 63623061 [GENAPPS.c961cb0a]
                          10: 2E32332E 31372E31 31393639 34 [.23.17.119694]
                          tbid (len 26) is ...
                          00: 47454E41 5050535B 32332E31 372E3131 [GENAPPS[23.17.11]
                          10: 39363934 5D5B312E 315D [9694][1.1]]
                          cmt(0):
                          Entered hgocpctx at 2012/05/18-12:34:38
                          Exiting hgocpctx, rc=0 at 2012/05/18-12:34:38
                          Exiting hgocomm, rc=0 at 2012/05/18-12:34:38
                          • 10. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                            Mkirtley-Oracle
                            Hi Jeremy,
                            I am unable to reproduce the problem on my systems -

                            SQL*Plus: Release 11.1.0.7.0 - Production on Fri May 18 12:41:23 2012

                            Copyright (c) 1982, 2008, Oracle. All rights reserved.

                            Enter user-name: scott/xxxx@ora111

                            Connected to:
                            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
                            With the Partitioning, OLAP, Data Mining and Real Application Testing options

                            SQL> SELECT "Field1"
                            2 FROM table1@hsmsql ;

                            Field1
                            ---------------------------------------------------------------

                            test1
                            test2
                            test3

                            SQL> BEGIN
                            2 FOR cur IN
                            3 (
                            4 SELECT "Field1"
                            5 FROM table1@hsmsql
                            6
                            7 )
                            8 LOOP
                            9 NULL;
                            10 END loop;
                            11 END get_ppw_data;
                            12 /

                            PL/SQL procedure successfully completed.

                            SQL>

                            Do you know if you have applied any patchsets to the Oracle_Home where DG4ODBC is running and what is the version of the Access ODBC driver you are using ? Is DG4ODBC in the same OH as the RDBMS being used for the select ?

                            Regards,
                            Mike
                            • 11. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                              556459
                              Sorry Mike,

                              My Oracle DEV environment is on a Linux box running 11.0.1.7.0. The Access DB is on a Windows Server 2003 - I think that's where the guys installed the Gateway software for me.

                              I'm not sure where the DG4ODBC is running - there is an old set of directories with DG4ODBC in the title on the Linux DEV box - was that what you meant?

                              Not sure if they applied patchsets to the Oracle_Home where DG4ODBC is running.

                              The Access ODBC driver you are using - how do I find that please?

                              Is DG4ODBC in the same OH as the RDBMS being used for the select? If that's the normal configuration then yes it is.

                              Jeremy
                              • 12. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                                Mkirtley-Oracle
                                Jeremy,
                                I would think they have installed the gateway on Windows where Access is installed as they could then use the Microsoft Access driver installed on the Windows machine. I'm not sure if there is an Access ODBC driver available for Linux.
                                Could you ask them if the gateway is installed on Windows, and the full version ?

                                To identify the driver depends on which platform you are running.
                                On Windows the gateway HS_FDS_CONNECT_INFO parameter points to a System DSN which will be configured to use a particular driver. On Linux it points to a setting in an odbc.ini file which will identify the driver.
                                The tnsnames.ora entry used for the database link will identify where the gateway is running. The 'HOST' parameter in the tnsnames.ora entry will identify the machine where the gateway is running, so if it is on Windows it will be a different host from the Linux box.
                                You find the tnsnames.ora entry by finding the 'host' value returned from the select -

                                select db_link, host from user_db_links ;

                                or

                                select db_link, host from all_db_links ;

                                depending if it is a private or public db link.

                                Regards,
                                Mike
                                • 13. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                                  Mkirtley-Oracle
                                  Jeremy,
                                  Sorry, I missed the trace file you put in the thread. It shows the gateway is running on Windows. I'll review it further and get back to you.

                                  Regards,
                                  Mike
                                  • 14. Re: 11.1.0.7.0 gateway to MS ACCESS 2007 cannot read table in stored procedure
                                    Mkirtley-Oracle
                                    Jeremy,
                                    In the System DSN definition on the Windows machine in the ODBC Administrator how did they define the Access file to be selected from ? And is there any security on the file ?

                                    Regards,
                                    Mike