Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


executing oracle procedure from sql server


executing oracle procedure from sql server

Author
Message
mathewspsimon
mathewspsimon
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 231
Hi Gurus,I have a retail system which is running on Sql server 2005 and I have Oracel E-Buz suite ERP.For my automatic replenishment request to work in Oracle I have to insert into Oracle some data from sql server.I would like to know how to execute an Oracle procedure from sql server.the parameters are dynamic like date,item and quantity which will vary from day to day.
please help please.
sql_lock
sql_lock
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1848 Visits: 1934
Hi

Firstly you will need to create a linked server (if you already haven't).

Then follow the blog below.
http://blogs.msdn.com/joaquinv/archive/2008/10/23/execute-oracle-stored-procedure-in-sql-server.aspx
Eswin
Eswin
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1697 Visits: 1078
QL Server Linked Servers feature lets you access Oracle data and data from other OLE DB/ODBc compatible data sources from SQL Server. Here are the basic steps for setting up an Oracle linked server.

1. Install and Configure the Oracle Client Software
Oracle client software provides the network libraries required to establish connectivity to an Oracle database system.Download the software from http://www.oracle.com/technology/software/products/database/oracle10g/index.html. Install the software on your SQL Server system and configure it by using Oracle Net Configuration Assistant.

2. Create the Linked Server
Create a linked server by using the T-SQL command

EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'

The name of the linked server is Oracle-LinkedServer.The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider. MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.The final required parameter is the data source name, Oracle Server.

3. Add Logins for the Linked Server
Next, provide the SQL Server system with an Oracle login to access the Oracle database by using the sp_addlinkedsrvlogin command

EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login. The fourth and fifth parameters supply login and password values for the Oracle system.

4. Query the Linked Server
To test the connection, run a sample query using the linked server name. Linked servers support updates as well as queries.To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name. For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM
OracleLinkedServer..SCOTT.EMP

5. List the Linked Servers
To list your linked servers and show the OLE DB provider that they employ, use the sp_linkedserver stored procedure.

# Setting Up an Oracle Linked Server
# By: Michael Otey

Tanx :-D
rhys.campbell
rhys.campbell
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 123
I've used this for executing a MySQL proc from SQL Server, I presume similar will work for Oracle.

EXEC('CALL usp_myProcedure(''2009-06-08 00:00:00'')') AT MySQLServer;

Where MySQLServer is the name of your linked server.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search