Summary SQL Server Real-Time query of Oracle databases

Assume that you have a network ordering system based on Microsoft. NET, but the QC maintenance system still uses an old Oracle database application. If your customer places an order such as product replacement during the warranty period, the order will not be charged. In this case, you need to obtain real-time query results from the Oracle database. By establishing a link server (linked Server), you can query customer data stored in the Oracle database in real time from SQL server to find out who is your existing customer.

When your data is distributed across different SQL Server databases, you can perform cross-Server Distributed queries by connecting to the Server. When all database servers are SQL Server, the configuration of the linked Server is very easy and covers all the things you need to know in the SQL Server online manual. However, when some data is stored on the Oracle database server, this may bring you many challenges. For example, setting a connection server is not easy. You must understand that you need to set up an Oracle connection Server in Enterprise Manager of SQL Server. This SQL Server is a client for Oracle. Therefore, you must install and configure the Oracle client software on the Server where SQL Server is located. Because Oracle only supports databases after Oracle 8, we assume that you are using databases after Oracle 8. The client software required by SQL Server is provided in the Oracle Net8 function library.

Note 1:

When connecting to the server, the ole db Provider for Oracle provided by Microsoft is used. The Oracle network function library used is SQL * Net 2.3.0.4 or a later version, however, this is provided by the Oracle 7.3 database. In other words, to set the Oracle database to the connection Server of SQL Server, the Oracle database must be of version 7.3.3.4 or later, and be matched with an appropriate SQL * Net or Net 8 library.

Source:
1. Oracle 7 Server Getting Started release 7.3.4
2. SQL Server Books Online: OLE DB Provider for Oracle

In Oracle databases, a schema represents a single database that SQL Server experts are familiar with (note 2 ). When connecting to the Oracle database, you must provide the schema name, password, and host online string (host string ). Each specific Oracle account has one Oracle schema and only one schema. Therefore, the schema name is equivalent to the account name of the schema owner. You can query the Data dictionary of Oracle to get more information about the schema.

Oracle online strings can also be referred to as service names or system identification materials (SID ). The so-called SQL Server database instance is called a database in Oracle ). Therefore, when installing Oracle Server, the Installer Oracle Universal Installer (similar to the Setup program of SQL Server) will ask you what the SID Name is, the name of the Oracle database.

NOTE 2:

The schema interpretation mentioned by the original author is somewhat problematic. Oracle schema can be considered as a set of all database objects owned by the same user (schema objects. For example, the full name of the EMP table created by scott is SCOTT. EMP, and SCOTT is the schema name of EMP. Therefore, schema name is an Oracle database user account. But it cannot be compared with databases! Because the database architecture of SQL Server includes data files and log files, the schema objects of Oracle only exists in tablespace. To avoid confusion among some readers, we hereby describe.

If you set the SID to Ora817 when installing Oracle Server on Windows (the same as the example in this article), a service named OracleServiceORA817 will be available in the system.

1. Quick understanding of Oracle parameter settings
2. Case Analysis Oracle Functions
3. Oracle Sequence
4. Do you know about Oracle triggers?
5. Detailed description of Oracle System privileges