Lessons learned: Integrating Oracle and SQL Server

This tip was originally published on SearchSQLServer.com.

Working with both Oracle and SQL Server databases presents a variety of challenges -- precisely why we've compiled this collection of short lessons by Kevin Kline, our resident Monitoring/Administration expert. If you have questions about working with Oracle and SQL Server, submit them today.

[IMAGE][IMAGE]
Kevin Kline Your Instructor: Kevin Kline, Monitoring/Administration Expert
Kline is director of SQL Server Solutions at Quest Software and president of the international Professional Association for SQL Server (PASS). Here he addresses the following topics:

  [IMAGE] Executing an Oracle stored procedure on SQL Server
  [IMAGE] Connecting an Oracle application to SQL Server
  [IMAGE] Importing data from other databases to SQL Server

Excecuting an Oracle stored procedure on SQL Server

Question: Is it possible to execute an Oracle stored procedure on SQL Server? If so, how?

Kline: Oracle uses its own dialect of SQL called PL/SQL (procedural language extension to Structured Query Language), while SQL Server uses T-SQL (Transact-SQL). The two have very different rules about variables, parameters, conditional processing, error handling and so forth. So unless the Oracle stored procedure contains only ANSI SQL statements, it won't work on SQL Server. However, Microsoft recen...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment Oracle data and application integration Users caution to look before you leap with Oracle Fusion Applications Oracle CRM On Demand data integration raises big issues Q&A: Oracle's Lenley Hensarling on integrating Value Chain apps with EnterpriseOne Oracle brings Fusion Middleware into the modern age Risk analysis software now part of Oracle Agile PLM software portfolio SearchOracle.com podcasts Special Report: Collaborate '09 Oracle and Java: Some think advisory board would comfort users Oracle New Year's resolutions, part 1: Advice for navigating 2009 Oracle to acquire application service management firm Oracle stored procedures Stored procedure to autotransfer data between Oracle servers Insufficient privileges error when creating stored procedure ORA-01422 error when procedure returns more than one row Calling procedure inside another procedure in anonymous block Can I make a second connection to Oracle without losing the first? How to create an index using a procedure in Oracle Oracle updates Microsoft developer tools Procedure compiles but does not execute Tool in Oracle 10g to keep track of execution of queries ORA-04082: NEW or OLD references not allowed in table level triggers

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary stored procedure  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

tly introduced a new tool that aids in the migration process from Oracle to SQL Server. You should investigate the SQL Server Migration Assistant. You should also note that moving from Oracle to SQL Server is not considered a simple point-n-click process. It usually involves a healthy dose of planning, analysis and good ol' fashion elbow grease.

Click for the original question and response.

Connecting an Oracle application to SQL Server

Question: I am currently working on a project with Oracle9i Forms at the front end and Microsoft SQL Server at the back end. What steps should I take to connect an Oracle9i Forms application with a SQL Server database?

Kline: I can't think of an easy way off of the top of my head. There's the Oracle Transparent Gateway. But that is certainly not an easy approach. There are also a few tools available using Oracle's Migration Workbench. You may also be able to connect using a JDBC driver. Check out Microsoft's JDBC driver for SQL Server. You can also find a list of JDBC drivers at DevX.com.

Click for the original question and response.

Importing data from other databases to SQL Server

Question: Can a stored procedure be used to import data from other types of databases and text files to SQL Server?

Kline: Absolutely! I've written many stored procedures to import data from other servers, usually Oracle and Access as well as from text files. When importing data from other servers, you'll have an easier time if you set up the source server as a Linked Server (see SQL Server Books Online for more details).

Once you've set up the Linked Server, you can write SQL statements directly against the Linked Server. For example, you could write the statement INSERT INTO local_table…SELECT col1, col2,… FROM linked_server.db1.dbo.foo.

When importing data from text files, simply use the BULK INSERT statement or, my preference, call the BCP command line utility using XP_CMDSHELL within the stored procedure. It takes a little work to get the exact XP_CMDSHELL string correct. All you have to do is encapsulate the BCP command-line string within a parameter for XP_CMDSHELL extended stored procedure.

Click for the original question and response.

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.