Q+E: Connecting to an Oracle DatabaseLast reviewed: September 12, 1996Article ID: Q96000 |
The information in this article applies to:
SUMMARYThe 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 INFORMATIONThe 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.EXEThe 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:
PROCEDURES FOR USING Q+E WITH ORACLEThe information below discusses the following procedures for using Q+E with Oracle:
Logging On and Logging Off of OracleBefore 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:
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:
Opening and Saving Oracle FilesWhen 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.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|<tablename>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 "user_name.table_name" (without the quotation marks).
Editing Oracle RecordsTo 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 FieldsTo 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 DriveYou 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
|
KBCategory: kb3rdparty
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |