Install Procedural Option Oracle

Options Oracle Software
Install Procedural Option Oracle Tutorial
Install Procedural Option Oracle Vm
Install Procedural Option Oracle Commands
Install Procedural Option Oracle Sql

Oracle Database 19c. Oracle Database 19c offers market-leading performance, scalability, reliability, and security both on-premises and in the cloud. And since it’s the Long Term Release of the Oracle Database 12c and 18c family of products, it provides the highest level of release stability and longest time frame for support and bug fixes. And how to install the Procedural Option is described in the installation documentation. Sybrand Bakker Senior Oracle DBA Received on Thu Jan 25 2007 - 04:28:02 CST.

-->

Original product version: Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Workgroup Edition
Original KB number: 280106

Summary

This article describes how to set up a linked server from a computer that is running Microsoft SQL Server to an Oracle database and provides basic troubleshooting steps for common errors you may experience when you set up a linked server to Oracle. Most of the information in this article is applicable to environments that are configured to use Microsoft OLEDB Provider for Oracle (MSDAORA). Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use Oracle's OLE DB provider.

For more information on configuring a linked server using Oracle’s OLEDB provider review How to get up and running with Oracle and Linked Servers.

Important

The current version of the Microsoft ODBC Driver for Oracle complies with the ODBC 2.5 specification, while the OLE DB Provider for Oracle is a native Oracle 7 OCI API provider. Both the driver and provider use the SQL*Net Client (or Net8 client for Oracle 8x) and the Oracle Call Interface (OCI) library, and other Oracle client components, to connect to Oracle databases and retrieve data. The Oracle client components are important, and must be configured correctly to successfully connect to Oracle databases using both the driver and the provider.

From Microsoft Data Access Components (MDAC) version 2.5 and later versions, both the Microsoft ODBC Driver and OLE DB Provider support ONLY Oracle 7 and Oracle 8i with the following limitations:

Oracle 8.x-specific data types, such as CLOB, BLOB, BFILE, NCHAR, NCLOB, and NVARCHAR2, are not supported.
The Unicode feature against Oracle 7.x and 8.x servers is not supported.
Multiple Oracle client instances, or multiple Oracle homes, are not supported because they rely on the first occurrence of the Oracle home in the SYSTEM PATH variable.
Returning multiple resultsets from a stored procedure or batch SQL statement is not supported using ADO or OLEDB.
Nested outer joins are not supported.
XML persistence is not supported.
Version greater than 8i are not supported using these drivers.

Note

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

Steps to set up a linked server to Oracle

You must install the Oracle client software on the computer that is running SQL Server where the linked server is set up.
Install the driver you want on the computer that is running SQL Server. Microsoft only supports Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle. If you use a third-party provider or a third-party driver to connect to Oracle, you must contact the respective vendor for any problems that you may experience by using their provider or driver.
If you use Microsoft OLE DB Provider for Oracle and Microsoft ODBC Driver for Oracle, consider the following:
- Both the OLE DB provider and the ODBC driver that are included with Microsoft Data Access Components (MDAC) require SQL*Net 2.3.x, or a later version. You must install the Oracle 7.3.x client software, or a later version, on the client computer. The client computer is the computer that is running SQL Server.
- Make sure that you have MDAC 2.5, or a later version, installed on the computer that is running SQL Server. With MDAC 2.1, or with an earlier version, you cannot connect to databases that use Oracle 8. x or a later version.
- To enable MDAC 2.5, or later versions, to work with Oracle client software, the registry must be modified on the client computer that is running SQL Server as indicated in the following table.
Restart the computer that is running SQL Server after you install the Oracle client software.
On the computer that is running SQL Server, set up a linked server by using the following script.
Note
If you use Microsoft ODBC Driver for Oracle, you can use the @datasrc parameter to specify a DSN name. For a DSN-less connection, the provider string is supplied through the *@provstr* parameter. With Microsoft OLE DB Provider for Oracle, use the Oracle server alias that is configured in the TNSNames.Ora file for the *@datasrc* parameter. For more information, see the 'sp_addlinkedserver' topic in SQL Server Books Online.

Common error messages and how to troubleshoot them

Important

This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base: 322756 How to back up and restore the registry in Windows

You can use either of the following two methods to retrieve extended information about any error that you experience when you execute a distributed query.

Method 1
Connect to SQL Server using SQL Server Management Studio and run the following code to turn on trace flag 7300.
Method 2
Capture the 'OLEDB Errors' event that is located in the 'Errors and Warnings' event category in SQL Profiler. The error message format is the following:
Interface::Method failed with hex-error code.
You can look up hex-error code in the Oledberr.h file that is included with the MDAC Software Development Kit (SDK).

The following is a list of common error messages that may occur, together with information about how to troubleshoot the error message.

Note

If you are using SQL Server 2005 or later versions, these error messages may be slightly different. However, the error IDs of these error messages are same as them in older versions of SQL Server. Therefore, you can identify them by the error IDs.For performance-related issues, search SQL Server Books Online for the Optimizing Distributed Queries topic.

Message 1
Error 7399: The OLE DB provider '%ls' for linked server '%ls' reported an error. %ls
Turn on trace flag 7300 or use SQL Profiler to capture the OLEDB Errors event to retrieve extended OLEDB error information.
Message 2a
'ORA-12154: TNS:could not resolve service name'
Message 2b
'The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 (or greater) client software installation'
These errors occur when there is a connectivity issue to Oracle server. Review Techniques to troubleshoot connectivity issues to Oracle server section below for additional troubleshooting.
Message 3
Error 7302: Cannot create an instance of OLE DB provider ‘MSDAORA’ for linked server '%ls'.
Make sure that the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle.
Note
If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following methods.
- Method 1Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerProvidersProviderName.
- Method 2Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options , and then click to select the Allow InProcess check box.
Message 4
Error 7303: Cannot initialize the data source object of OLE DB provider ‘MSDAORA’ for linked server '%ls'. [OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied] OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d].
This error message indicates that the linked server does not have correct login mapping. You can execute the sp_helplinkedsrvlogin stored procedure to set the login information correctly. Also, verify that you have specified the correct parameters for the linked server configuration.
Message 5
Error 7306: Cannot open the table ' %ls' from OLE DB provider 'MSDAORA' for linked server '%ls'. The specified table does not exist. [OLE/DB provider returned message: Table does not exist.][OLE/DB provider returned message: ORA-00942: table or view does not exist] OLE DB error trace [OLE/DB Provider 'MSDAORA' IOpenRowset::OpenRowset returned 0x80040e37: The specified table does not exist.].
Error 7312: Invalid use of schema and/or catalog for OLE DB provider '%ls' for linked server '%ls'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
Error 7313: An invalid schema or catalog was specified for the provider '%ls' for linked server '%ls'.
Error 7314: The OLE DB provider '%ls' for linked server '%ls' does not contain the table '%ls'. The table either does not exist or the current user does not have permissions on that table.
If you receive these error messages, a table may be missing in the Oracle schema or you may not have permissions on that table. Verify that the schema name has been typed by using uppercase. The alphabetical case of the table and of the columns should be as specified in the Oracle system tables.
On the Oracle side, a table or a column that is created without double quotation marks is stored in uppercase. If the table or the column is enclosed in double quotation marks, the table or the column is stored as is.
The following call shows if the table exists in the Oracle schema. This call also shows the exact table name.
Message 6
Error 7413: Could not connect to linked server '%ls' (OLE DB Provider '%ls'). Enable delegation or use a remote SQL Server login for the current user. Msg 18456, Level 14, State 1, Line 1 Login failed for user '.
This error message indicates that a distributed query is being attempted for a Microsoft Windows authenticated login without an explicit login mapping. In an operating-system environment in which security delegation is not supported, Windows NT authenticated logins need an explicit mapping to a remote login and password created using sp_addlinkedsrvlogin.
Message 7
Error 7391: The operation could not be performed because OLE DB provider 'MSDAORA' for linked server '%ls' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]
Verify that the OCI versions are registered correctly as described earlier in this article.
Note
If the registry entries are all correct, the MtxOCI.dll file is loaded. If the MtxOCI.dll file is not loaded, you cannot perform distributed transactions against Oracle by using Microsoft OLE DB Provider for Oracle or by using Microsoft ODBC Driver for Oracle. If you are using a third-party provider and you receive Error 7391, verify that the OLE DB provider that you are using supports distributed transactions. If the OLE DB provider does support distributed transactions, verify that the Microsoft Distributed Transaction Coordinator (MSDTC) is running and has network access enabled.
Message 8
Error 7392: Cannot start a transaction for OLE DB provider 'MSDAORA'for linked server '%ls'. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionLocal::StartTransaction returned 0x8004d013: ISOLEVEL=4096].
The OLE DB provider returned error 7392 because only one transaction can be active for this session. This error indicates that a data modification statement is being attempted against an OLE DB provider when the connection is in an explicit or implicit transaction, and the OLE DB provider does not support nested transactions. SQL Server requires this support so that, on certain error conditions, it can terminate the effects of the data modification statement while continuing with the transaction.
If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB provider. Therefore, execute SET XACT_ABORT ON before you execute data modification statements against remote tables in an implicit or explicit transaction. Do this in case the OLE DB provider that you are using does not support nested transactions.

Techniques to troubleshoot connectivity issues to Oracle server

To debug the Oracle connectivity issues with either the Microsoft ODBC driver for Oracle or the Microsoft OLE DB Provider for Oracle, follow these steps:

Use the Oracle SQL Plus utility (a command line-based query utility) to verify that you can connect to Oracle and retrieve data.
Note
If you cannot connect to Oracle and retrieve data, you either have a bad install or configuration of the Oracle Client Components or you have not correctly created a Transparent Network Substrate (TNS) service alias for the Oracle server when you used the SQL*Net Easy Configuration or Oracle Net8 Easy Configuration utility. Contact your Oracle database administrator (DBA) to verify that the Oracle components that you must have are correctly installed and configured.
Verify the version of the Oracle client (SQL*Net version) that is installed on the computer. Both the Microsoft ODBC driver for Oracle and the Microsoft OLE DB Provider for Oracle require the installation of SQL*Net version 2.3 or later on the client computer.
The connectivity from SQL Plus (the Oracle client query tool) may appear to function, but you must restart your computer for the ODBC/OLE DB connectivity to function correctly.
Note
When you use Oracle 8i, the .rgs file is empty.
If the Oracle client is installed, and you receive an error that indicates that Oracle Client Components 7.3 or later must be installed on the computer, then verify that the environmental variable PATH on the client computer contains the folder in which the Oracle client was installed such as, Oracle_RootBin. If you cannot find this folder, then add the folder to the PATH variable to resolve the error.
Verify that the Ociw32.dll file is in the Oracle_Rootbin folder. This .dll file cannot exist at any other location on the client computer. Make sure that the Oracle Client Component DLLs (for example, the Core40.dll file and the Ora*.dll file) do not exist outside the Oracle_Root folder or subfolders.
Verify that a single Oracle client version is installed on the computer. Multiple versions of SQL*Net cannot exist on the same client computer with interferes and with critical operations (for example, TNS and alias lookups).
Microsoft recommends that you have a local install of the Oracle client and not do this by mapping a remote Oracle client on your computer and then include it in the path of the system to connect to Oracle through ODBC/OLE DB. But the provider and driver are tested with a locally installed Oracle client and not on a network share.

Installing Oracle Database

To install Oracle database on your computer, you need to download the installer from the download page of Oracle website.

After having the installation files which are in ZIP format, you need to extract them into a specific folder on your computer.

The following picture shows the structure of the folder of the Oracle installation files after extraction.

Now you need to double-click the setup.exe file to start the installation process. There will be 9 steps which mostly automatically execute.

Step 1. The installer asks you to provide your email address to get the latest security issues and updates. You can ignore it by clicking the Next button

Because I didn’t provide the email address, the Oracle database installer confirm it, you just need to click the No button to continue.

Step 2. In step 2, Oracle installer ask you to whether you want to create and configure a database, install database software only or just upgrade an existing database. Because you install the Oracle database at the first time, choose the option 1 and click the Next button.

Step 3. The installer allows you to choose the system class. Because you install Oracle on your computer, not a server, therefore, you choose the first option: desktop class and click the Next button.

Step 4. This step allows you to specify the Windows user account to install and configure Oracle Home for enhanced security. Choose the third option: “Use Windows Built-in Account”.

Step 5. in this step you can (1) choose the folder on which Oracle database will be installed, (2) Global database name and password, (3) pluggable database name.

Step 6. The installer performs the prerequisite check.

Step 7. The installer shows you the summary of the information such as global settings, database information, etc. You need to review the information and click the install button if everything is fine.

Step 8. The installer starts installing Oracle database. It will take a few minutes to complete, depending on your computer.

You will see the Database Configuration Assistant window. Click the Password management… button to enter the password for Oracle database accounts.

Enter the password for SYS and SYSTEM accounts and then click OK button.

Step 9. Once installation completes successfully, the installer will inform you as shown in the following screenshot. Click the Close button to close the window.

Connecting to Oracle Database

First, launch the SQL developer application provided by the Oracle Database.

Second, right-click the connections node and choose New Connection … menu item to create a new connection.

Third, enter the information that you provided during the installation process as shown in the following screenshot. Click the Connect button to connect to the Oracle Database.

Options Oracle Software

SQL developer will display all objects as shown below.