Q+E: Connecting to an Oracle Database

• 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

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 "SET CONFIG = C:\ORACLE\CONFIG.ORA" 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:

1. From the File menu, choose Logon.

2. Select Oracle and choose the OK button.

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

4. In the User Name box, type your user name.

5. In the Password box, type your password.

6. Choose the OK button.

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. "User Name" 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 "sys" (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.

   FROM Oracle|<tablename>

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 "Maintaining Database Files" in your "Q+E for Microsoft Excel User's Guide."

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.

REFERENCES

"Q+E for Microsoft Excel User's Guide", version 3.0a included with Microsoft Excel version 3.0 and Microsoft Excel for OS/2 version 3.0, Appendix D, page 147

"Q+E for Windows User's Guide", Pioneer Software Version 2.6.1, pages 187-195

READMEQE.TXT file, dated 10/15/91, from disk 1 of the WINXL30A Fulfillment Package