Access Oracle through SQL Server 2008

Previously wrote an article about SQL Server access to the MySQL database, and recently just encountered the need to access Oracle, the configuration process is recorded for your reference.

Preparatory work

Do the following on the host that requires access to the Oracle database:

1. Installing the SQL Server database: SQL Server R2 Express

2. Installing the Oracle Client Access program: Oracle 10g clients

3. Configure the Oracle client Tnsnames.ora file, which needs to be aware of the database alias "Oradb" in the file, which will be used when creating linked Server below.

oradb =  (DESCRIPTION =     hostip) (PORT = 1521))     ))  

4. Ensure that the host can access the remote Oracle database through Sqlplus.

Create linked Server

Open SQL Server Management Studio into the Server Objects list, right-Linked Servers, click "New Linked Server ..."

Fill in the following items in the general interface:

Linked Server: Fill out the Linked server name

Provider: Select Microsoft OLE DB Provider for Oracle

Product name: Complete Oracle

Data source: Fill in the database aliases mentioned above ORADB

Click on Security, select "be made using this security context", assuming that the Oracle database has a user ID called admin, enter the access account in "Remote login" and click "OK" to end the creation.

When you're done, you'll see the Oracle database link you just created in linked Servers.

Access Oracle

Next, you can access the Oracle database on the SQL Server side, the query syntax is as follows:

SELECT * from Linked_name. Account_name. table_name;

Combined with the above example, if you want to query the contents of the Admin user under the Jobs table, the corresponding SQL statement should be:

SELECT * from Orasvr. Admin.jobs;

When using the above method for data query, if there is a conversion error when the Oracle table has a number column, you can convert the number type to a string (also, modify the column property to numbers (10), and set good digits for the Count column). It is more common to consider factors such as view queries, such as personal feelings or the way strings are converted.

Of course, if you query too many columns and each number column turns to varchar, then the best way to do this is to use the OPENQUERY function:

SELECT * from OPENQUERY (linked_name, ' SELECT * from table_name ');

In accordance with the syntax of OPENQUERY, the above SQL statement strain is:

SELECT * from OPENQUERY (orasvr, ' SELECT * from JOBS ');

This allows us to easily access the Oracle database on the SQL Server side and make the appropriate data adjustments.

Access Oracle through SQL Server 2008