Introduction

This article discusses exchanging data between ORACLE, Access, Excel and Word. It gives optimisation tips and timing of various queries, and describes how to check check the statements being passed. It also gives a list of ORACLE error messages encountered.

At the University there are about 4000 PC, over 700 with Access installed (1250 with Excel). New users start with Access and transfer to Oracle if they have large databases, have complex applications, want to use C, or want to share their data. They are accustomed to the Microsoft tools and build their own applications, only calling for help at the design stage or if they get problems. They often fight shy of SQL despite the fact that Access has a very prominent SQL button.

I have installed Oracle on many PCs and several departmental Suns, and though these users are happy with SQL, they are not always too happy with SQL*FORMS 3, especially now they are used to a windows interface and tend to try to use a mouse by mistake. Many still only have 4Mb machines (or smaller), and upto 6 months ago there were not many 16Mb machines capable of running the ORACLE windows tools, so for reasons of a familiar interface plus machine constraints, ODBC provides a good solution. There is also a limit of how many packages I can support virtually single-handed and neither do I have the disk space to hold them.

Hardware and software requirements

It is possible for Microsoft Access and Excel to access ORACLE databases, as long as they have SQL*Net and the correct connectivity drivers loaded. I used:

• Oracle version 6 on a Sun Solaris
• Telnet connection PCNFS-PRO. PCNFS version 5 will work but there tends to be memory problems due to RTM occupying standard memory (Access needs at least 400K to attach ORACLE else gives 6105 error).
• SQL*NET TCP/IP for Windows provides the network software connecting client workstation to the server. Needed if using Oracle ODBC drivers provided by Oracle, Microsoft or Intersolv. SQL Retriever, OpenLink and SequeLink provide their own software. This means that if you are accessing Ingres or Sybase databases as well as Oracle it is more flexible to use third party middleware.
• Microsoft ODBC driver (Access 2.0 comes with an ODBC driver for Microsoft SQL Server and one-tier drivers for dBase, Word, Excel etc so you have to buy other ODBC drivers separately from them. The driver that came with Access 1.1 will also work). Allows Access to access Oracle 6 data and Oracle 7 (with version 6 functionality, 16 bit only). To get full Oracle 7 functionality, download the Oracle ODBC driver from the Internet.
• Word for Windows version 6 (version 2 does not offer database connectivity), Excel version 5 (version 4 offers Q+E, an earlier version of query tool) and Access version 1.1 or 2 (version 2 is preferred because it offers more facilities such as OLE Automation).

Performance is slow on a 4Mb machine, so it is recommended that an 8Mb machine is used (performance increased 8 fold). I used a 4Dx33 with 16Mb and compressed disk.

Architecture

Two-tier system (Oracle. ODBC driver sends SQL directly to ORACLE):

Application (Access 15Mb disc space, min 4Mb memory)
|
ODBC Driver Manager (ODBC.DLL)
|
ORACLE Driver (SQORA.DLL)
|
ORACLE Call Interface (OCI) Layer (ORA6WIN.DLL)
|
SQL*NET TCP/IP For Windows Driver (SQLTCP.DLL, SQLTCP1.DLL)
|
Network Software (PC-NFS Winsock MPCNFS4.DLL)
|
SQL*Net Listener on remote machine
|
ORACLE Server

The Application provides an Application Program Interface (API) between client and Driver Manager

The ODBC Driver manager directs ODBC calls to the correct driver (ORACLE) using the Service Provider Interface (SPI)

The ORACLE Driver processes ODBC function calls, connects to the data source, translates SQL, retrieves the result set and returns it to the application

The OCI layer is the router (eg t for tcp/ip)

SQL*NET enables transfer of requests, data, status, errors and connects to the server using the tcp socket

The Network Software transports the data stream over the network

Note that Access can not be used as a front-end to an ORACLE database on your PC through ODBC because the DOS version is not a multi-task server

Setting up data sources from the ODBC Control Panel.

To add a data source to the ODBC Data Sources dialog box, its ODBC driver must already be installed on your machine.

• Click on the control panel icon in the Main window.
• Click on the ODBC icon to see the list of data sources available. This runs \windows\system\odbcadm.exe
• Click on Add.
• Click on ORACLE.
• Set Data Source name to 'x' (the name of the machine).
• Set Description to 'x demo'.
• Set SQL*NET Connect string to for example 't:x:sid'. You will notice that a new data source has been added 'x (Oracle)' to enable you to access ORACLE data on machine X.

The control string specifies the network details when logging in. The syntax for the connect_string for TCP/IP is:

net_prefix:hostname:system_id

The general syntax for running an application program from the command line is:

application username/pwd@connect_string

Using Microsoft Access with Oracle data.

There are 2 options to access Oracle data: create an external link or use import/export

Attaching data means that you are using an external link to the data at its source. Creating external links to Oracle tables keeps the Access view up-to-date, avoids duplication of data, and does not require extra storage space within the Access database. Attached tables are slower to access than locally stored tables, and their design can not be changed.

• From the File menu choose Attach.
• Select an ODBC data source by clicking on <SQL Database> (ORACLE or SQL Server)
• Select a SQL Data Source that you have already defined.
• Logon to ORACLE by giving an ORACLE username and password. Note that this is not your host machine username and password.
• Accessible tables are then listed. Highlight the tables (one at a time- which is tedious) that you want to attach. They are prefixed by the username that they belong to since you may have access to other peoples tables. If you want to save the ORACLE username and password locally so that you will not be asked for it when you next access the attached tables, click on the 'Save ID and password locally' box.

Depending on the data source, a bold right arrow and a symbol prefixes the name of the attached table. dBase tables show a bold 'dB', ORACLE tables a globe If you look at the design of the table and show the properties you can see the source of the table.

Editing and updating attached Oracle tables within Microsoft Access is very easy. The only condition is that the Oracle table must have a unique index. If this does not exist on the Oracle table, for example if you are looking at an Oracle view, then you can create a unique index within Access that Oracle is not aware of, by using a data-definition query.

• You can query an Oracle table (if you are the owner) without attaching it by:

  select * from emp
  in "" "ODBC;dsn=x;database=t:sun:sid;uid=Scott;pwd=tiger"

  This method is slower if you want to access the table again, since although it takes 25 seconds to attach a table, it does not take long to open it. The above query takes about 25 seconds to show a page of results regardless of the size of table
• Attachment Manager (from File/Add-in) allows you to view or refresh attachments if the attached table's structure has been changed.

Sending Access data to ORACLE

You can import and export data in a number of different database formats and from spreadsheets and text files. Downloading the tables enable more speedy access to the data but will become out-of-date in Access if subsequently modified in Oracle.

• From the File menu choose Export. Choose format <SQL database> (ORACLE)

Note that if you use spaces in an Access field name and you are exporting to Word or ORACLE, that spaces will automatically be replaced by underscores.

Accessing Oracle data from Excel version 5

• From the Data menu choose Get External Data to start Microsoft Query.
• The data source window shows available data sources (ORACLE sources show the username that you used).
• Highlight a data source and press Use.
• If the source is ORACLE, the ORACLE username and password is requested.
• If a wanted data source is not displayed, press Other.
• Choose an ODBC data source (can create a new one).
• The accessible tables will be shown. Create a query definition by adding the appropriate tables.
• Press Close to stop adding tables.
• Use the Criteria menu to give criteria (uses listboxes), choose Criteria from the View menu to display the criteria section using QBE, or press SQL icon (uses ORACLE SQL).
• From the File menu, choose Return data to Excel.
• In the destination box type or select the reference for the upper left cell of the range where you wish the results to appear (you can save the query by selecting the Keep Query Definition box).

Sending Excel data to ORACLE

To embed an Excel object in another application, use Copy and Paste Special. This can not be used for ORACLE.

If you select Save As from the File menu, you will have to save in comma delimitered format to mailmerge into Word or import into ORACLE. Access can import Excel spreadsheets.

Accessing ORACLE data from Excel version 4

Use the QE.XLA add in, then from the Data menu choose Activate Q+E. You must then use SQL to construct your query.

Using Word For Windows with Oracle data

There is no ODBC link for Word For Windows 2. You can only insert Excel worksheets into a Word document. Use Copy and Paste Special between Microsoft applications. Access data has to be incorporated using mailmerge.

Word for Windows version 6 does provide an ODBC link

• From the Insert menu choose Database/Get Data.
• From the Open Data Source window select MS Query.
• The available data sources are listed. Either select Use to use one, or select Other and add a new source as described above, and Use that.
• Logon to ORACLE by giving your ORACLE username and password.
• You are now in MS Query. Choose the tables that you want by highlighting them and pressing Add Table.
• Close when you have chosen all your tables.
• Create your query, in the same way as you would using Access query.
• Exit by choosing Return Data from the File menu.
• From the Database window, click on Insert Data. You now have the choice of returning all records, or a selected number.

You will get a General Protection Fault if you try to insert ORACLE data with the Microsoft ODBC driver. This is a problem due to the Winsock driver and may be got round using PSNFS4 or 3rd party ODBC software.

Points to note:

Data Definition

• ORACLE tables must be set up with primary keys (by using 'create unique index' from SQLPLUS) if you want to be able to add data from Access.
• Make sure the Access table has been specified without Oracle reserved words (ORACLE6 has 111, Access 39), has valid table and field names (embedded spaces relaced by '_', no check on name length (ORACLE limit 30, Access 64), number of fields (ORACLE 240, Access 255) and data types (no counters), and has been defined using upper case. If you export a table with a lower case name, you only will be able to use it with double quotes arround it in Oracle and you will not find it in the dictionary. Rename the table or columns eg:

  rename "test" to TEST
  create table x(a) as select a "A" from oldx

Exporting an Access table to ORACLE

If the conversion fails, the value is treated as null- which should not happen very often since Jet chooses text when no other Jet type has a large enough value range.

• You can not set up automatic counters unless you are using ORACLE ODBC to an Oracle7 database with default values set to a sequence built into the data design. You cannot use the Access counter to generate sequence numbers, and cannot access a sequence from Oracle
• ORACLE field and table names can not contain spaces and are limited to 30 characters (Access 64). You may not have more than 240 fields in an ORACLE table (Access 255). There are also more reserved words in ORACLE that you may not call a table or field, such as 'comment', so if you are thinking of creating a table in Access and exporting it to ORACLE later, you should bear this in mind.
• Access Fields containing OLE objects will not successfully be imported into the ORACLE longraw datatype since it only holds 65Kb so the picture will come out very fuzzy.
• Since you can not change the table definition, and ORACLE version 6 does not enforce data validation (except for null values and non-unique primary keys), you will have to build validation and defaults into your Access forms.
• Relationships are built automatically between attached tables in Access version 2 if you use the same field names and data types, but you can also set up relationships.
• When you attach or download an Oracle table, a list of Oracle tablenames appears in the format: ownername.tablename.

The list only includes those tables that the Oracle ID has access to. Oracle system (public) tables such as User_tables and Session_roles are also excluded unless the Show System Options box is ticked within the View/ Options menu. Trying to view the Oracle system tables in Access can cause an ODBC fail with the message: ORA-01741 - illegal zero-length identifier.

• The Oracle user must have resource and connect access else you get an error message "fetch out of sequence"
• Make sure table has the correct privileges if it belongs to another user
• ORACLE stored procedures may not be attached because they do not resemble tables and views enough, but Access Basic can be used to create your own functions and subroutines

Data Entry

• If you are using a 4Mb machine, you find Access very slow. Do not type ahead else you will find that data will end up in the wrong cell.
• Dates can be entered in any form in Access. They will be converted automatically to ORACLE format (dd-mon-yy) when stored. Note that when you are importing an ASCII file that has been produced by spooling in SQLPLUS, you must change the Access data type to char first, and then change it back to date type otherwise the records will be rejected
• ORACLE does not save data until the user issues a request to commit it (in SQLPLUS you can ask for changes to be saved automatically by means of the autocommit statement. Access saves changes as you leave the record so does not support rollback.

Queries

• You can perform 'pass-through' queries from Access (Query/SQL Specific/ Pass-through). This allows you to use ORACLE SQL instead of Access SQL to query the ORACLE tables. This avoids Access SQL being converted into ORACLE SQL so is more efficient. It also allows you to do SQL queries such as nested queries, and to create tables. You must be careful to remove all the Access SQL square brackets etc
• Pass-through queries are read only since they return a snapshot
• Subreports cannot use pass-through queries as record sources
• Searches on ORACLE data are case-sensitive from Access even if case sensitivity is off.
• The Access driver evaluates the where clause as it fetches each row. If a row contains a value that causes an arithmetic error such as dividing by zero, the driver returns all the rows but returns errors for the fields with the arithmetic errors. ORACLE would just return an error.
• To do outer joins, it is not recommended to use the query wizard on attached tables because you get an error message - use 'not exists' instead- you will probably find it easier to type it directly in the SQL interface rather than using the QBE interface.
• The query builder does not save the connect string unless you save the query
• Access will run slower than on a local database due to the network traffic time. Try to avoid accessing large tables. There is a limit of about 100,000 rows.
• Jet does not make any attempt to use the server's record identifier (rowid) in lieu of a unique index so there is no efficient way for Jet to receive the new record identifier after inserting a record
• use count(*) rather than count(column_name) to use special optimisations

Updating

• If you are updating a table and you get an error, Access will tell you how many rows will be updated, and then tell you that there are errors. If you choose to continue, the invalid values will be set to null. ORACLE only returns an error message and will not allow you to continue.

Access Basic

• You can also use Access Basic with ODBC functions SQLAllocEnv, SQLAllocConnect, SQLExecute etc

Security

Access and Excel deal with password security to external databases in opposite ways.

Microsoft Access provides a check box for the 'Save login ID and password locally' which is unchecked by default, but which the user can change, giving the potential consequence of NO security. Encrypting the database is another option to protect the passwords.

When you query ORACLE from Excel, you are taken into MS-QUERY, where you are asked for your ORACLE id and password. You then select the table(s) to query, and when you have the data you require, you then select 'Return data to Excel'.

The important thing is the next box which appears. By default, an item 'Keep query definition' is checked. If you leave this checked then the query definition, along with the information needed to re-connect to ORACLE, ie the userid and password, is saved with the spreadsheet.

This means that if you call up the sheet at some later date, and select 'Get external data', you will not be asked for your ORACLE id and password, and the query you used to select the data on the spreadsheet will be used to re-query the database. and enable you to update the data on the spreadsheet with the revised data in the database. The problem of course, is that it offers NO security.

The way to plug this loophole is to remember to uncheck the box marked 'Keep query definition' when you return the data to the spreadsheet. This will of course mean that when you wish to update the data, you will have to provide all the information (login id/password and query) again.

Locking and sharing data

• Access can support transactional processing eg. rollback, through the use of Access Basic
• You can control shared data from Access by View\Options\Multiuser/ODBC
• You can share Access .mdb files by placing them on a network drive. No one can view data in a table that another user has open in Design view. Similarly no one can change table design, or reattach while another user is viewing data in queries, reports or forms. Be careful who is making design changes, because you may not have enough access next time- you can get round this by copying the .mdb file, or even by deleting the .ldb file.
• Jet does no explicit server-based locking of any kind; the servers default concurrency mechanisms are used at all times. The 'record locks' form property can only be set to 'no locks'. 'all records' is illegal, 'edited record' behaves like 'no locks'. Updates are done using optimistic concurrency. Long-running transactions over large amounts of data can lock out other users.

Optimising performance

Access is optimised to return answers as quickly as possible; as soon as the first screenful of results is available it will be painted. Result sets are stored in memory. While you are inactive, the result set is populated in the background to allow faster operations when you become active again. You can control how quickly this idle-time population occurs using MSysConf described below. Automatic idle-time population does not apply to snapshots and dynaset objects in Access Basic- ORACLE may hold a lock on that row.

• create the table MSysConf in Oracle (or any other SQL database) with fields:

Config (2 byte integer not allowed null),
chValue (varchar 255),
nValue (4 byte integer)
Comments (varchar 255).

The optimiser uses statistics based on number of records in a table, location, indexes etc. A query is compiled each time it is modified. If a significant number of rows have been added to your table since last compiled, it should be recompiled to update the statistics.

Snapshots and Dynasets

Microsoft distinguish between snapshots (query selects the selected columns of each qualifying row) and dynasets (query selects only the primary key columns of each qualifying row and must use a separate query to fetch the data. Thus data can be refreshed quickly with a dynaset, whereas a snapshot, which caches the entire data set cannot be refreshed except by complete reexecution of the query. A snapshot is a nonupdatable, unchanging view of the data and is obtained by changing the form's 'Allow Updating' property to "No tables" (or by not creating an ORACLE index)

The cache size for dynasets (not snapshots or pass-through queries) can be altered by CacheStart, CacheSize, FillCache properties).

Pass-through queries are populated on demand as rows are requested for faster ope and less traffic.

• If your result set is small and you do not need to update data or see changes made by other users, use a snapshot
• If your result set is large, a dynaset is faster and more efficient since moving to the end of a snapshot requires the whole result set to be downloaded to the client. A dynaset downloads only the primary keys and then fetches the last screenful corresponding to those keys. Dynaset open time and scrolling speed are affected most negatively by number of columns.

Asynchronous Queries

JET executes ODBC queries asynchronously (The ODBC driver returns control back to the application while the function is still executing). Access makes full use of this facility because it brings back a couple of screenfulls and then enables the execution to be cancelled, or switch to another Windows task. JET asks the server if the query is finished every M millisceconds (500 by default, is changed by altering AsyncRetryInterval in msaccess.ini)

JET will automatically cancel a long-running query after a default time of 60 seconds (QueryTimeout in msaccess.ini, or ODBC Timeout propery of an individual query)

Queries

• Open the database in read-only mode if you do not want to update it (Security/Permissions)- faster than shared mode
• Do not use binocular find, particularly on a non-indexed column, or worse still all columns, since Jet does a full table scan and takes for ever (certainly not jet-propelled!). Use a filter or query using restrictions that can be processed by the server. It does not work asynchronously either so it is not possible to multi-task. Fortunately it can be stopped by pressing Ctrl/Break.
• Make sure that queries are sent to the server for processing, by using functions supported by ORACLE, avoiding operations that cannot be expressed in a single SQL statement, crosstabs, parameter queries, reports with multiple levels of grouping, heterogeneous joins between local and remote tables (joins between small local tables and large remote tables, where the join column is indexed, may result in a remote index join. where one query for each row in the local table is sent to the server and only the joining rows are returned).
• use Oracle indexes. They are used on equality and range searches. Inequality and function searches are less efficient since they do not use indexes.
• restrict the amount of data requested from the server. Select only the rows and columns you need.
• use pass-through queries especially for bulk updates such 'insert', 'update', 'delete' and 'maketable'.
• For any given query, Jet must decide what portion is done by ORACLE so often pass-through queries are faster, except that the user is asked to identify the data source each time the query is run. Client-side processing increases network traffic.
• set ODBC Refresh Interval to a long interval (default 1500 seconds)

Forms

• avoid look up lists based on large attached tables. They are not populated during idle-time. They will yield sluggish performance as they drain server and network resources as they fetch data to fill the list. Limit the list or break up the data into smaller combo boxes. Import them rather than attach them. >

• each list box, combo box, subform and total field requires a separate query. Against a remote table, long delays may occur when you open a form since every query must be sent to the server and a response returned before the form can open.

• index the first field displayed in the combo box or list box
• if a form is based on an attached table and you will not be updating it, set the forms property AllowUpdating to No Tables
• Use Access query rather than SQL as the source of values in a list or combo box
• Use black and white rather than colour bitmaps
• speed laser printing by setting property FastLaserPrinting. Printing in landscape can be slower than portrait
• memo columns are not fetched unless they are visible on the screen- so place memo fields off the bottom of the screen, or add a button to make them visible when pressed
• base subforms on queries rather than tables, including only those fields that are necessary
• use fields with normal borders rather than sunk or raised

Local Processing

The SQL that Jet sends to an ODBC driver is generated according to the SQL grammar defined by ODBC. This is then translated into the appropriate server SQL

Example VICTIM------->>FRACTURE

• equi-join
  Access SQL: select distinctrow from victim
  inner join fracture on victim.vno=fracture.vno
• outer-join
  Access SQL: select distinctrow victim.* from victim
  left join fracture on victim.vno=fracture.vno
  where ((fracture.vno is null))

ORACLE: select victim.* from victim,fracture
where victim.vno=fracture.vno(+)

• parameter query
  parameters assault_district text;
  select distinctrow victim.* from victim
• crosstab
  transform count(vno) as (the value)
  select [(assault_district]), count(vno) as (row summary)
  from victim
  group by assault_district pivot sex

ORACLE: select assault_district,count(vno) "row summary",
sum(decode(sex,'m',1,0)) m,sum(decode(sex,'f',1,0)) f
from victim
group by assault_district

• Attached tables are prefixed by ownername and enclosed in square brackets eg [ops$ccmjs_victim]
• joins spanning multiple data sources (joins across the same data source or a small local table with a large attached table are done by ORACLE)
• functions (and user-defined) not supported by standard SQL (eg iif)
• reports with multiple levels of grouping and totals
• non-standard 'like' wildcards such as '#' for date, '[' for list
• only the component conditions on a where clause that can not be performed remotely
• group by queries with an order by clause

Timing

The same 10 column table was opened in datasheet mode from the Table icon when it was stored locally and attached remotely. The time (in seconds) was taken from pressing the icon to showing the first 12 records. Note that once the table has been accessed that it is quicker to open it again in the session. Note also the difference on a 4Mb machine if network software is in memory. Thus, it is advisable to only include RTM and RNMNIS if attaching files over the network, particularly when using a smaller machine.

The following operations aim to compare timing carried out on large tables both remotely and locally. The times shown (in seconds) are to show the first page of results when run from the design so also includes compile time and when Windows has been restarted. I also show performance monitor (FASTQ.MDA) lapsed time but it was not always consistent. A pass-through query takes 10 seconds to connect and login (this was not included in the following results).

The joins were performed with a 6021 row table.

Effect of MSysConf

I used equijoin (result 6049 rows) and not exists (result 14173 rows), both dynasets

Debugging

1. To see SQL statements sent to an ODBC data source

• Edit MSACC20.INI

  [ODBC]
  TraceSQLMode=1
  DisableAsync=1
  

SQLAllocEnv(phenv5F3F0000);
SQLAllocConnect(henv5F3F0000, phdbc52770000);
SQLSetConnectOption(hdbc52770000, 103, 00000014);
SQLDriverConnect(hdbc52770000, hwnd3CB8, "DSN=cse;DBQ=T:sun:sid;UID=SCOTT;PWD=*****", 41, szConnStrOut, 0, pcbConnStrOut, 0);
SQLGetInfo(hdbc52770000, 9, rgbInfoValue, 2, pcbInfoValue);
SQLGetInfo(hdbc52770000, 6, rgbInfoValue, 100, pcbInfoValue);
:
SQLGetStmtOption(hstmt57A70000, 4, pvParam);
SQLSetStmtOption(hstmt57A70000, 4, 00000001);
SQLError(henv5F3F0000, hdbc52770000, hstmt57A70000, szSqlState, pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
SQLError(henv5F3F0000, hdbc52770000, hstmt57A70000, szSqlState, pfNativeError, szErrorMsg, 8133, pcbErrorMsg);
SQLExecDirect(hstmt57A70000, "SELECT Config, nValue FROM MSysConf", -3);
SQLError(henv5F3F0000, hdbc52770000, hstmt57A70000, szSqlState, pfNativeError, szErrorMsg, 8192, pcbErrorMsg);
:
SQLExecDirect(hstmt57A70000,"SELECT DEPTNO,DNAME,LOC FROM SCOTT.DEPT",-3);
SQLFetch(hstmt57A70000);
SQLGetData(hstmt57A70000, 1, 5, rgbValue, 256, pcbValue);
SQLGetData(hstmt57A70000, 2, 1, rgbValue, 256, pcbValue);
SQLGetData(hstmt57A70000, 3, 1, rgbValue, 256, pcbValue);
SQLFetch(hstmt57A70000);
SQLGetData(hstmt57A70000, 1, 5, rgbValue, 256, pcbValue);
SQLGetData(hstmt57A70000, 2, 1, rgbValue, 256, pcbValue);
SQLGetData(hstmt57A70000, 3, 1, rgbValue, 256, pcbValue);
:
SQLFreeStmt(hstmt57A70000, 0);
SQLFreeStmt(hstmt57A70000, 1);
SQLDisconnect(hdbc52770000);
SQLFreeConnect(hdbc52770000);
SQLFreeEnv(henv5F3F0000);

2. To trace calls to ODBC functions:

• Click on the ODBC icon in the Control panel
• select options and choose 'trace ODBC calls'

output will go into SQL.LOG by default

Error numbers are all user-defined (given in ODBC Programmers Handbook)

SQLExecDirect: SELECT Config, nValue FROM MSysConf
SQLExecDirect: SELECT DEPTNO ,DNAME ,LOC FROM SCOTT.DEPT

3. To see what Oracle does

• Edit oracle.ini

OSNTDBUG=0xffff

OSNTDBUG=0x200

The last gives briefer output (still pretty long!) and shows the SQL that Oracle tries to do with any error messages

Output sent to ORAWIN\TCPNNN.NNN

osntra recv 26 bytes:
00 16 01 00 01 02 01 00	|........|
49 42 4d 50 43 2f 4d 53	|IBMPC/MS|
44 4f 53 2d 36 2e 30 2e	|DOS-6.0.|
30 00 00 00 00 00 00 00	|0.......|
osntra recv 36 bytes:
00 20 01 00 01 02 00 53	|. .....S|
75 6e 2d 53 50 41 52 43	|un-SPARC|
2f 53 75 6e 4f 53 2d 34	|/SunOS-4|
2e 30 2e 78 2d 36 2e 30	|.0.x-6.0|
2e 32 39 00 00 00 00 00	|.29.....|
:
53 43 4f 54 54 2f 74 69	|SCOTT/ti|
67 65 72 4d 53 2d 57 49	|gerMS-WI|
4e 44 4f 57 53 20 50 43	|NDOWS PC|
4f 72 61 55 73 65 72 31	|OraUser1|
32 34 36 33 3a 30 31 44	|2463:01D|
3a 5c 57 49 4e 44 4f 57	|:\WINDOW|
53 5c 73 79 73 74 65 6d	|S\system|
5c 4b 52 4e 4c 33 38 36	|\KRNL386|
2e 45 58 45 00 00 00 00	|.EXE....|
:
osntra recv 39 bytes:
00 23 01 00 03 03 05 01	|.#......|
01 01 01 1b 53 45 4c 45	|....SELE|
43 54 20 2a 20 46 52 4f	|CT * FRO|
4d 20 53 45 53 53 49 4f	|M SESSIO|
4e 5f 52 4f 4c 45 53 00	|N_ROLES.|
osntra recv 72 bytes:
00 44 01 00 04 00 02 03	|.D......|
ae 00 00 01 01 01 0e 00	|........|
02 00 00 00 00 00 00 00	|........|
00 00 00 00 05 00 00 28	|.......(|
4f 52 41 2d 30 30 39 34	|ORA-0094|
32 3a 20 74 61 62 6c 65	|2: table|
20 6f 72 20 76 69 65 77	| or view|
20 64 6f 65 73 20 6e 6f	| does no|
74 20 65 78 69 73 74 0a	|t exist.|
:
osntra recv 47 bytes:
00 2b 01 00 03 03 0a 01	|.+......|
01 01 01 23 53 45 4c 45	|...#SELE|
43 54 20 43 6f 6e 66 69	|CT Confi|
67 2c 20 6e 56 61 6c 75	|g, nValu|
65 20 46 52 4f 4d 20 4d	|e FROM M|
53 79 73 43 6f 6e 66 00	|SysConf.|
osntra recv 72 bytes:
00 44 01 00 04 00 02 03	|.D......|
ae 00 00 01 01 01 1b 00	|........|
02 00 00 00 00 00 00 00	|........|
00 00 00 00 0a 00 00 28	|.......(|
4f 52 41 2d 30 30 39 34	|ORA-0094|
32 3a 20 74 61 62 6c 65	|2: table|
20 6f 72 20 76 69 65 77	| or view|
20 64 6f 65 73 20 6e 6f	| does no|
74 20 65 78 69 73 74 0a	|t exist.|
:
osntra recv 63 bytes:
00 3b 01 00 04 00 02 03	|.;......|
eb 00 00 01 01 01 1b 03	|........|
02 00 00 01 00 00 00 00	|........|
00 00 00 00 0b 00 00 1f	|........|
4f 52 41 2d 30 31 30 30	|ORA-0100|
33 3a 20 6e 6f 20 73 74	|3: no st|
61 74 65 6d 65 6e 74 20	|atement |
70 61 72 73 65 64 0a 00	|parsed..|
:
osntra recv 55 bytes:
00 33 01 00 03 03 10 01	|.3......|
01 01 01 2b 53 45 4c 45	|...+SELE|
43 54 20 44 45 50 54 4e	|CT DEPTN|
4f 20 2c 44 4e 41 4d 45	|O ,DNAME|
20 2c 4c 4f 43 20 20 46	| ,LOC F|
52 4f 4d 20 53 43 4f 54	|ROM SCOT|
54 2e 44 45 50 54 20 00	|T.DEPT .|
:
osntra recv 203 bytes:
00 c7 01 00 06 02 03 00	|........|
02 03 4c 01 30 03 05 2a	|..L.0..*|
00 00 00 e7 68 00 00 df	|....h...|
68 00 00 d7 68 03 05 10	|h...h...|
00 00 00 cf 68 00 00 c7	|....h...|
68 00 00 bf 68 03 05 0f	|h...h...|
00 00 00 b7 68 00 00 af	|....h...|
68 00 00 a7 68 07 02 31	|h...h..1|
30 00 0a 41 43 43 4f 55	|0..ACCOU|
4e 54 49 4e 47 00 08 4e	|NTING..N|
45 57 20 59 4f 52 4b 00	|EW YORK.|
07 02 32 30 00 08 52 45	|..20..RE|
53 45 41 52 43 48 00 06	|SEARCH..|
44 41 4c 4c 41 53 00 07	|DALLAS..|
02 33 30 00 05 53 41 4c	|.30..SAL|
45 53 00 07 43 48 49 43	|ES..CHIC|
41 47 4f 00 07 02 34 30	|AGO...40|
00 0a 4f 50 45 52 41 54	|..OPERAT|
49 4f 4e 53 00 06 42 4f	|IONS..BO|
53 54 4f 4e 00 07 02 39	|STON...9|

Installation files and machine settings

Use the Access Add-in manager to uninstall wizards, builders and other unwanted add-ins to reduce memory consumption. Avoid running large applications such as Word, ECSMAIL, Excel at the same time as Access to avoid using memory particularly on a 4Mb machine.

Make sure windows virtual memory swap file setting plus available RAM is not less than 25 MB (defragment and delete unwanted files). Access requires a minumum of 6Mb RAM.

Use 32 bit access (set from 386 Enhanced icon on the Control Panel)- had little effect

oracle.ini

[ORACLE]
ORACLE_HOME=C:\ORAWIN
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
TCP_VENDOR=WINSOCK
TCP_SERVICES_FILE=C:\NFS\SERVICES
RDBMS70=C:\ORAWIN\RDBMS70
PRO15=C:\ORAWIN\PRO15
TCP_HOSTS_FILE=C:\NFS\HOSTS

[ORACLE]
ORA_CONFIG=D:\WINDOWS\ORACLE.INI

FILES=60
BUFFERS=40
BREAK=ON

ECHO OFF
CALL C:\BAT\PUSHENV
PATH %PATH%;D:\ACCESS;C:\ORAWIN\BIN
C:\BIN\SHARE.EXE /L:500
D:\WINDOWS\SMARTDRV.EXE
C:\NFS\RTM {not needed with PCNFSPRO
c:\NFS\rnmnis {not needed with PCNFSPRO
set config=D:\windows\ORACLE.INI
D:\WINDOWS\WIN %1 %2
c:\NFS\rnmnis /u
C:\NFS\RTM /U
C:\BAT\POPENV

ORASRV 1525/TCP

Add a space and a carriage return to the end of the orasrv entry to avoid SQL*NET incorrectly parsing the SERVICES file

137.222.12.33 mjs
137.222.12.1 x

msaccess.ini

[ODBC]
QueryTimeout=100
LoginTimeout=100
TraceSQLMode=1
[ISAM]
MaxBuffersize=4096 (2048 if 8Mb, 512 if 4Mb. default 512)

ORACLE error messages

The following section explains what to do when you encounter various error messages from ORACLE Server through the ODBC ORACLE driver. ORACLE RDBMS Error Messages and codes Manual gives details of error codes and course of action, but the following numbers are not listed. Typing on the sun:

oerr ora nnnn

* ORA-0942 could not execute table, could not find linked table, table or view does not exist

• Remedy: Check that dictionary tables all_indexes, all_catalog and 'all_ind_columns are accessible by the user

* ORA-1002 Fetch out of sequence

Cause: ORACLE owner did not have resource privilege

• Remedy: - grant it

* ORA-1017 Invalid username and passwd

Cause: Password has been changed since last attach
• Remedy: - reattach

* ORA-3114 not connected to ORACLE

Cause: Network drive has become disconnected.
• Remedy: - Close down Windows and reload RNMNIS

* ORA-03121 No interface driver connected -- function not performed

Cause: The ODBC ORACLE driver cannot find ORA6WIN.DLL or has the incorrect version or cannot find one of the SQL*Net components.
• Remedies: Check the following:
  • Make sure the network connection is valid. For example, type ping <servername> for a TCP/IP connection.
  • Search for and delete old versions of ORA6WIN.DLL. ORA6WIN.DLL only supports SQL*NET for Windows from version 6.0.34.2.0 onwards, datestamp 13 April 1992 or later.
  • Check that the PATH variable contains the BIN subdirectory of the <oraclehome> directory (usually C:\ORAWIN\BIN).
  • Check that the CONFIG variable is set (by typing DOS command SET) and that it points to your ORACLE configuration file (ORACLE.INI).
  • Check that SQLTCP.DLL (for TCP/IP), SQLSPX.DLL (for Novell NetWare IPX/SPX), or SQLNMP.DLL (for Named Pipes) is in the ORACLE BIN directory specified in the PATH variable. (If not, SQL*Net was not installed correctly.)
  • Check that ORA7WIN.DLL and COREWIN.DLL are in the ORACLE BIN directory specified in the PATH variable. (If not, SQL*Net was not installed correctly.)
  • Check that the network prefix of the connect string is t for TCP/IP etc
  • Check that there is at least 400K executable program size- shut down other Windows applications such as ECSMAIL, and Telnet. More likely to get this error when using PCNFS v5.1 due to RNMNIS. PCNFS-PRO does not need RNMNIS so is better

* ORA-6105 NETTCP: remote host is unknown (or driver not capable)

Cause: The server may have gone down.
• Action: Use nettest and ping to check the connection.

  Cause: Hostname or network prefix incorrect.

• Remedies:
  • Check the connect string. Try typing the node number explicitly for example, t:137:222.12.1:sid. The number is obtained from the NFS\HOSTS file.
  • Make sure node name is in NFS\HOST file.
  • The network prefix must be 't' for TCP/IP.
  • Make sure the server process name 'orasrv 1525/tcp' is in \NFS\SERVICES. This can get deleted when updating PC-NFS since it overwrites the NFS\SERVICES file, thus losing the ORASRV entry. It may need a blank line as a last line
  Cause: If returning data to Excel- the user is not the owner of the tables

* ORA-6106 NETTCP: socket creation failure

Cause: RTM and RNMNIS were not loaded before attaching tables.
• Remedy: Exit from windows and load.

* ORA-6107 NETTCP: ORACLE network server not found

Cause: No entry in SERVICES file for ORACLE server.
• Remedies:
  • Try t:servername/1525:sid to avoid access to services file
  • Add orasrv 1525/tcp entry to the NFS\SERVICES file. Try adding a blank space after tcp for TCP/IP etc.
  • Check that the reference to the services file in oracle.ini has the correct path

Cause: Version 5 of PC-NFS being used with version 1.1.7.6 of SQL*NET for Windows.
• Remedy: Update SQL*NET for Windows to version 1.1.7.7 from Oracle installer.

* ORA-6108 NETTCP: connect to host failed

Cause: SQL*NET TCP/IP server on remote host is not up or the host itself is not up.
• Remedy: Create a new item in the Windows ORACLE group to run C:\ORAWIN\BIN\NETTEST. This enables one to test if one can login to the server. If the login is unsuccessful, login to ORACLE from the server and test that the server is connected properly by typing

  sqlplus scott/tiger@t:sun:sid

  If this gives 6108 then get the database administrator to display the status of the server by typing:

  tcpctl stat

• If the server is disconnected, restart the server by typing:

  tcpctl start

  Cause: d:\access is not in path.
• Remedy: Exit from windows, and add to WIN.BAT.

* ORA-6114 NETTCP: lookup failure

Cause: The database identifier string in the connect string was not recognised by the server.
• Remedy: Check with the database administrator that the system id of the connect string is correct. Default is sid

* ORA-06120 NETTCP: network driver not loaded

Cause: The JSB VSL socket is not initialised. This error can also occur when ORA6WIN.DLL is loaded but cannot find another SQL*Net component, such as SQLTCP.DLL.
• Remedies:
  • Check that the directories containing the SQL*Net components are in your PATH variable.
  • Check that the ORACLE_HOME variable is set correctly in your D:\WINDOWS\ORACLE.INI file. If ORACLE Server was not installed on your system, ORACLE_HOME should be set to C:\ORAWIN. (It should also be set to your version 6 <oraclehome> directory, usually D:\ORACLE6 in CONFIG.ORA if you do have a local database.)
  • Search for and delete old versions of ORA6WIN.DLL.
  • Check that you have followed all the instructions for the SQL*Net driver you are using. For example, for the SQL*Net for TCP/IP driver, make sure that all the TSRs, such as NMTSR and SOCKTSR, are loaded in the ORACLE_HOME\BIN directory (If not, SQL*Net was not installed correctly). Try a ping and a nettest
  • Make sure that TCP_VENDOR is correctly set in the ORACLE.INI file.
  • Make sure the MSOCKLIB.DLL file is present in the ORACLE_HOME\BIN directory.
  • Make sure the JSB vendor-specific dll (WINSOCK.DLL for PC-NFS) for the selected vendor is present in the ORACLE_HOME\BIN directory.
  • Make sure the VSL.INI file is present in the WINDOWS home directory.
  • Try closing down Windows and restarting

  Cause: May be short of real memory

• Remedy: upgrade the hardware

* ORA-6122 Connection setup failure

Cause: SQL*NET can not find the ORACLE home directory.
• Remedies:
  • Check the WIN.INI file for the ORA_CONFIG entry in the [oracle] section.
  • Check that ORACLE.INI exists in the WINDOWS directory. If not, reinstall SQL*NET TCP/IP for Windows.
  • Check that ORACLE.INI contains a value for ORACLE_HOME pointing to the directory where SQL*NET TCP/IP for Windows has been installed.

* ORA-6136 NETTCP error during connection handshake

Cause: Cannot return data to Excel
• Remedy: The user can only access their own tables

* ORA-9124

Cause: Using unexpected version of PCNFS
• Remedy: Check the tcp_vendor in oracle.ini is appropriate for the version of PCNFS by looking at \WINDOWS\VSL.INI

* ORA-9241 Message not found; product = RDBMS; facility = ORA; language = NULL

Cause: Applications are either built using ORA7WIN (for version 7) or ORA6WIN (for version 6) libraries. If using ORA7WIN libraries then the application will look at the ORACLE.INI file to resolve ORACLE environment variables. Applications built using ORA6WIN libraries will access the CONFIG environment variable. If this CONFIG environment variable is not present then this error will occur.
• Remedies:
  • Check that the CONFIG variable is set in your AUTOEXEC.BAT or WIN.BAT file and that it points to your ORACLE configuration file (ORACLE.INI).
  • Set up a CONFIG.ORA file to hold environment variables (ORACLE_HOME should be C:\ORAWIN) and reference it with a CONFIG environment variable.

  Cause: The ODBC ORACLE driver searches for error messages in the subdirectory that normally contains the ORACLE error messages. If you receive this error, it means that the ODBC ORACLE driver cannot find the error messages.

• Remedies:
  • Make sure that SQL15WIN.DLL is present in \ORAWIN\BIN
  • Check that the path includes a reference to \ORAWIN\BIN.
  • Check that the ORACLE_HOME variable is set correctly in your D:\WINDOWS\ORACLE.INI file. If ORACLE Server was not installed on your system, ORACLE_HOME should be set to C:\ORAWIN.
  • If ORACLE Server version 6 was already installed on your system, ORACLE_HOME should be set twice. The first time, it should be set to your version 6 <oraclehome> directory, usually D:\ORACLE6. The second time, in the [ORACLE] section of the file, it should be set to C:\ORAWIN.
  • If you did not have any ORACLE software on your workstation, make sure that you copied all the .MSB files from C:\ORAWIN\RDBMS70 to C:\ORAWIN\DBS.
  • Check that the appropriate JSB Virtual Socket library files are present by looking at the VSL.INI file in \WINDOWS. PC-NFS version 5.0a uses winsock so should be able to find MSOCKLIB.DLL and MWINSOCK.DLL in \ORAWIN\BIN.

  The ODBC ORACLE driver should now be able to print the ORACLE Server error message, enabling you to fix the problem that generated the error.

* ORA-9301 Local kernel only supported in standard mode

Cause: Trying to use Access as a front end to a local ORACLE version 5 or 6 database (connect string s:).
• Remedy: Not possible even if one runs Windows in standard mode.

ODBC error messages

The following section explains what to do when you encounter various ODBC error messages.

* Unknown string resource

Cause: Tried to do outer join with attached tables
• Remedies:
  • use pass-through query instead
  • try enclosing the tablename in square brackets

* IM003 Driver specified by data source could not be loaded

Cause: The ODBC Driver Manager is attempting to load the ODBC ORACLE driver (SQORA.DLL). SQORA.DLL loads ORA6WIN.DLL to connect to the ORACLE server. You can receive this message if it cannot find ORA6WIN.DLL or finds the wrong version of ORA6WIN.DLL.
• Remedies:
  • Search for and delete old versions of ORA6WIN.DLL. The correct version of the ORA6WIN.DLL was installed by the ODBC ORACLE driver in the SYSTEM subdirectory of your Windows directory.
  • Make sure that ORA6WIN.DLL was installed when the ODBC ORACLE driver was installed.

* ODBC.DLL Data source not found and no default driver specified

Cause: This happens when transferring to another machine, and internal variables relating to attached files differ.
• Remedy: Reattach the files. Note that if one attaches the same table twice, the second will have the suffix 1. The first can then be deleted and the second renamed so that the forms etc will contine to work.