Transfer Oracle to SqlServer via DTS using StoredProcedure that return CURSOR REF

Question

Post reply

Transfer Oracle to SqlServer via DTS using StoredProcedure that return CURSOR REF

maria canahua

SSC Enthusiast

Points: 113
More actions
February 4, 2006 at 9:27 pm

#76483

Hi:
Please, help me with this:
I need to obtain data from Oracle 9i into Sql Server(MSDE) using DTS, not linked server.
In Oracle exist a StoredProcedure that return a cursor ref, because we process data before to return a set of result.
The target of that cursor ref that is returned by storeProcedure in oracle is a table on MSDE.
How can I do this?
Is it possible?, exists another way?
Thanks,

Maria
February 7, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 1

"In Oracle exist a StoredProcedure that return a cursor ref, because we process data before to return a set of result.

The target of that cursor ref that is returned by storeProcedure in oracle is a table on MSDE."

I'm trying to visualize what exactly you're trying to do. You wrote that you have a stored procedure on Oracle, that when executed, returns a cursor reference to a table on MSDE. You also state that the data is processed on the Oracle side.

So if the data is processed on the Oracle side, what is it that you want DTS to do for you?

maria canahua SSC Enthusiast Points: 113 More actions · Answer 2

HI,

We do that because a store procedura in oracle return a result set(cursor) that we need to insert in MSDE, that belogs to other system, ie, System on MSDE need to use the data that return the store procedure in oracle, and the way we think is with DTS,

thanks,

for your answer

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 3

Maria,

So this stored procedure returns a result rowset or a cursor of a result rowset?

Maybe the "cursor" is the result rowset, but I have always related a cursor to a pointer in the result rowset. Then you can use cursor methods to move, edit, update the rowset.

If you are just trying to get a result rowset from Oracle to SQL, that should be a transform task that connects an Oracle Source to the SQL destination.

If you are trying to use some kind of cursor manipulation on a result rowset, you will probably have to do that using TSQL. I personally avoid cursor usage. It reminds me too much of mainframe processing.

Ian Yates SSCoach Points: 19738 More actions · Answer 4

You are still confusing me by what you want??

So you have a stored procedure on Oracle... What does it do? Does it get all of its data from Oracle databases + tables or does it also refer to some other third party system?

Then you want to take the data from the oracle stored proc and insert it into a SQL Server table... DTS could certainly be the way to go - it just depends on whether it is a ORACLE cursor being returned (which I imagine SQL Server will have no idea how to handle) or a standard resultset... Could you provide the code of your Oracle proc?

Are you able to set up your Oracle server as a linked server in SQL Server? You could then execute the Oracle procedure from within TSQL code - if it returns a standard resultset then from your TSQL code you could do something like

insert into MyTable(col1, col2, col3, ...)

exec MyOracleServer.database.owner.procedure

Or you might need to use openQuery instead? Last time I had to use linked servers with Oracle I had all sorts of fun times!! :

maria canahua SSC Enthusiast Points: 113 More actions · Answer 5

Hi:

This is an example that we use in Oracle to return the result of Select sentence in a Stored Procedure:

--------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE curspkg_join AS

TYPE t_cursor IS REF CURSOR ;

Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);

END curspkg_join;

CREATE OR REPLACE PACKAGE BODY curspkg_join AS

Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)

IS

v_cursor t_cursor;

BEGIN

IF n_EMPNO <> 0

THEN

OPEN v_cursor FOR

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND EMP.EMPNO = n_EMPNO;

ELSE

OPEN v_cursor FOR

SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

END IF;

io_cursor := v_cursor;

END open_join_cursor1;

END curspkg_join;

--------------------------------------------------------------------------------------------------------

This result, we need to insert in a table of MSDE, and work with this table in MSDE, because the system that use, not have relation with oracle, only obtain data and then process it in MSDE.

About linked server, is it best of DTS?, I read it consume bandwith? is it correct.

Is there any other way that store procedure return the result of select sentence? Please if you can tell me. We found this way and use it, because we know SQL SERVER, not much Oracle.

Please help me

Excuse my english, I speak spanish.

Thanks again.

maria

maria canahua SSC Enthusiast Points: 113 More actions · Answer 6

Hi, anyone can help me please!

I need your answer

thanks