Run MS SQL stored procedure from Oracle 10g and store result in Oracle 10g table

[mytechreg]

Hi,

My case is : I have an application running on MS SQL 2008 R2 in Server A, which there are a couple of stored procedures in there. I have also a Oracle 10g in Server B. Now that, I created a table in the Oracle 10g DB in Server B and would like to run the stored procedures in Server A, so that the result set of the stored procedures in Server A will be stored in the Server B oracle table.

As I m a newbie in this 2 env, can any one walk me thru how I can do this ?

Many thanks in advance.

 

[johnherman]

Here is a possible solution. You will need some sort of API (like ODBC) for SQL Server to communicate with Oracle. There will also be difficulties in tracking the success of the process (such as network failure).

The easiest solution is to write the output of the SQL Server SP to a table in SQL Server, then write an ODBC job to insert those rows into an Oracle table, and then after success, clean out the SQL Server table to prepare for the next run.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[johnherman]

Another possibility is to write the SP results into a SQL Server table and then use Microsoft SSIS to move the table to Oracle. Then run an Oracle SP to load the target table. After everything succeeds, truncate/rebuild the SQL Server and Oracle staging tables.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[Beilstwh]

And the easiest way is to run the sql server stored procedure in sql server and directly write into the oracle table. Not that difficult to do.

Bill
Lead Application Developer
New York State, USA

 

[mytechreg]

Thanks for all your suggestions.

My original idea is what Bill mentioned, "run the sql server stored procedure in sql server and directly write into the oracle table", but i have no idea that what has to be configured in both server A (SQL server) and server B (Oracle) before that can be done.

I am confused of how to do that. Any advice is welcome.

Thanks again.

 

[fredericofonseca]

approaches are as mentioned
linked servers - I think this was Bill suggestion (although one could also argue that using a SSIS package to execute the stored proc returning a recordset and inserting this on a oracle destination would be what he was suggesting - see below).

SSIS - execute the proc against sql server, returning a recordset (or inserting into a table and then selecting from it) and inserting into a oracle destination.
If volumes are high I would go with this option and if you are using Enterprise version I would also use the Microsoft Attunity driver for Oracle to load the data.
If you don't have Enterprise edition then CozyRoc would be another option to use.

mix of SSIS, flatfile, sqlloader with direct path settings and caching configured


search the net for sql server linked oracle servers and you will find documentation on those.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Beilstwh]

I should have expanded my answer. I was going to suggest a linked server on the SQL server. To see instructions simple query google with the following key words

microsoft linked server oracle

Bill
Lead Application Developer
New York State, USA

 

[garani]

Hi

I agree creating a link from SQL server to Oracle is one/might be best option.

Alternatively You can create a database link from Oracle to SQL server using HSODBC and use the link to access SQL server objects

Let's say you want to refresh Table A from SQL server to Table B in Oracle periodically

DBMS_JOB===>CALL ORACLE SP===>READ SQL SERVER TABLE USING HSODBC LINK AND POPULATE ORACLE TABLE B

I think you can also try executing stored procedure in SQL server using the link from oracle


garan

 

[Beilstwh]

I agree with garani. I have done both. I have oracle reading and updating sql server tables though both oracle sql and T-SQL using the DBMS_HS_PASSTHROUGH package. I also have sql server updating oracle tables using linked servers. Both work fine.

Bill
Lead Application Developer
New York State, USA

 

[mytechreg]

Thanks for all your suggestions.

As instructed by my manager, I'm not allowed to make changes to the SQL server A and nothing can be done on the SQL server A, NO SSIS and no customized Stored Proc...etc, I am only allowed to run the Stored Proc in SQL server A (sorry not mentioned this part in my previous message). Therefore, the SQL server A needs to be remained as it is while in Oracle 10g Server B, it has to create the HSODBC DB link and some kind of coding in here.

Sum up all your kind suggestions and my preliminary ideas are:

1. Create DB link in Oracle 10g(Server B) using HSODBC
2. Use the DB link in Oracle 10g(Server B) to call and run the Stored Proc in SQL 2008 R2(Server A). For this, I assume the results of the Stored Proc in SQL 2008 R2(Server A) can be populated in the table of Oracle 10g(Server B).

Do you think the above are feasible ? If yes, will that be very complicated for a newbie? Or any special steps I should be aware?

Your input or useful ref link that can help my ideas are always appreciated.

Thanks.

 

[garani]

Hi

HSODBC is little complex for a newbie but still you can do again your oracle server should be running on Windows NT/7/8 so that you can configure ODBC connection. If it is running on Unix there might be additional complexity. This was the old method which we used HSODBC.ORA instead of tnsnames.ora for configuring


1. You can try involking SQL server stored procedure using the package DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE i guess this came in oracle 10g


Here hsa is the database link from oracle to sqlserver and create_Receipt is the sql server stored procedure

Please refer below link

https://community.oracle.com/thread/1009253

declare
dummy integer;
string1 varchar2(100);
string2 varchar2(100);
begin
dummy:= DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@hsa('Create_Receipt "'||string1||'" , "'||string2||'"' );
end;


garan