Microsoft KB Archive/96000

{| = Q+E: Connecting to an Oracle Database =
 * width="100%"|

Last reviewed: September 12, 1996

Article ID: Q96000 The information in this article applies to:
 * Q+E for Microsoft Excel for Windows, version 3.0a
 * Q+E for Microsoft Excel for OS/2, version 3.0

SUMMARY
The following information discusses how to use Q+E for Microsoft Excel to access an Oracle database system.

Note: Oracle 7 is not supported with current version of Q+E for Microsoft Excel.

MORE INFORMATION
The Oracle database system uses a client-server architecture. This means that Q+E runs on one or more client computers that are attached to a network while the Oracle database system runs on a separate server computer on the network.

To access an Oracle database through Q+E, you must run the proper SQL*Net layer before you start Microsoft Windows. SQL*Net driver is an Oracle file and can be obtained by contacting Oracle Corporation. Oracle recommends that customers use versions of Windows dated 10/31/90 or later.

In testing, Q+E functioned correctly with the following networks, using the appropriate SQL*Net driver:

Network       SQL*Net Driver ---

Microsoft LAN Manager       SQLNTB.EXE Novell Netware              SQLSPX.EXE DecNET Pathworks 4.0        SQLDNT.EXE Banyan Vines              SQLVIN.EXE The Oracle driver that shipped with Q+E version 3.0a for Microsoft Excel should work on all networks (provided that Oracle supplies the appropriate SQL*Net connection). If Q+E is unable to connect to the Oracle database driver, try running Windows in standard mode and make sure that Microsoft Excel and Oracle are included in the path statement of your AUTOEXEC.BAT file (often, a terminate-and-stay-resident (TSR) program will function properly in standard mode but not in 386 enhanced mode).

If Q+E still can't connect to the Oracle database driver, try the following:


 * Remove unnecessary programs from Load= and Run= lines of your WIN.INI file.
 * Remove unnecessary TSR's.
 * Check the SET TEMP statement and free disk space.
 * Make sure the line &quot;SET CONFIG = C:\ORACLE\CONFIG.ORA&quot; is in the AUTOEXEC.BAT file.

PROCEDURES FOR USING Q+E WITH ORACLE
The information below discusses the following procedures for using Q+E with Oracle:


 * Oracle Logon and Logoff Procedures
 * Opening and Saving Oracle files
 * Editing Oracle Records
 * Defining Oracle Tables and Fields
 * Expressions and SQL supported by the Q+E Oracle Driver

Logging On and Logging Off of Oracle
Before you can use Q+E to access Oracle tables, you must log on to an Oracle server. If you make Oracle the default database system when you install Q+E, Q+E will request logon information the first time you try to open or define a table. If Oracle is not your default system, choose Logon from the File menu.

To log on to Oracle:

 From the File menu, choose Logon. Select Oracle and choose the OK button.  In the Server Name box, enter the name of the server computer that contains the tables you want to access. The complete form of an Oracle server name is      driver_prefix:computer_name:sid where driver_prefix is a letter identifying the Network protocol you are using. Some driver_prefixes used to identify the network protocols are: Protocol      Letter

Named Pipes        P      Spx                 X      NetBIOS             B      TCP/IP              T      DECNet              D      Oracle Async        A

Example Server Name: X:Server_name

Note: The Server Name is case sensitive. (Check your Oracle  documentation for other protocols)  In the User Name box, type your user name. In the Password box, type your password. Choose the OK button.

Note: You can log on to Oracle a maximum of two times per Q+E session. When you want to exit the Q+E program, Q+E automatically logs you off of Oracle. You may also log off from Oracle manually if you want to free up memory resources while using Q+E.

To log off of Oracle:


 * 1) From the File menu, choose Logoff.
 * 2) Choose Oracle and choose the OK button.

Opening and Saving Oracle Files
When you are logged on to Oracle and you make Oracle the Source in the Open dialog box (from the File menu, choose Open), you will see options that are specific to Oracle. &quot;User Name&quot; is the current user name. To change the user name, select an item in the User Name box and choose the OK button. When the current User Name is dbo, the system tables are displayed in the Table List box. System tables begin with &quot;sys&quot; (without the quotation marks).

With Oracle set as the current Source, you can choose the Options button in the Open dialog box to specify which object types are displayed in the Table List box. Some of the options in the Table List box are:

Procedures - this check box is unavailable (dimmed).

Set Default - Select this check box to make your choices the default for any Oracle tables you open in the future. If you want to select Oracle records from Microsoft Excel, you must specify the source in the SELECT statement. To do this, add a prefix to the first filename in the FROM clause. FROM Oracle| When you use choose Save As from the File menu to save query results to an Oracle table, Current User Name specifies the user name for the new table. To create the table under a different User Name, enter the name for the table in the form &quot;user_name.table_name&quot; (without the quotation marks).

Editing Oracle Records
To edit, add, or delete records in an Oracle table, you must have modify privileges within Oracle for that table. For more information about editing, see the Section &quot;Maintaining Database Files&quot; in your &quot;Q+E for Microsoft Excel User's Guide.&quot;

Defining Oracle Tables and Fields
To create new Oracle tables or to modify an existing database definition and save it as a new table, choose the Define command from the File menu. Once you modify an existing table definition with Q+E, you cannot save it back to the same table.

You can also use the Define command to delete an Oracle table if you have delete table privileges.

When you are defining fields for an Oracle table, the field type must be one of the following Oracle types:

CHAR - contains letters, numbers, or any punctuation on your keyboard, up to 240 characters. It is a variable length.

LONG - contains long, multiline textual data, up to 65,535 characters. It is a variable length. Q+E cannot display or edit text values of more than 10,000 characters. You cannot add conditions to or sort on a field that has a long data type. Only one long field can appear in a single table.

NUMBERS - contains numeric values in one of two forms. If WIDTH and DECIMAL values are not specified, contains floating point values with 40 digits of precision. If WIDTH and DECIMAL are specified, DECIMAL indicates the number of digits to the right of the decimal point and WIDTH defines the maximum number of digits in the field.

DATE - contains date and time values. The earliest date that can be  stored is January 1, 4712 B.C., and the latest date that can be   stored is December 31, 4712 A.D.

RAW - contains up to 240 bytes of binary data. It is a variable length.

Expressions and SQL supported by the Q+E Oracle Drive
You can use any expressions or SQL supported by Oracle to define computed columns and to edit or write SQL statements in the SQL Query dialog box. For more information see your Oracle documentation.