Stored Procedure over a distribute environment

[Ezequiel]

I have the following problem:

I need to develop a Stored Procedure that updates/selects info to a different databes (i.e. "select ... from employee@db1" or else "select ... from employee@db2&quot.
Is there a way to make the @db1 / db2 dynamically so that I don't have to make a SP for each database?

Thanks in advance.
---
Ezequiel Glinsky
eze@bumeran.com
Buenos Aires, Argentina

 

[MikeJones]

You could do it with dynamic sql, but it would make the code a bit messy, or perhaps you could have a stored procedure which did "select ... from employee@other_db" and then another SP which would switch where Other_db pointed too.

HTH,

Mike.

 

[Ezequiel]

Thanks Mike, I guess that's what we'll have to do.
---
Ezequiel Glinsky
eze@bumeran.com
Buenos Aires, Argentina

 

[KrojerT]

Hi, if both remote databases are online you can code both select/update statements and determine which is executed by a parameter to the procedure. In one case I did this this way.

Thomas

 

[Fredzo]

I disagree about the dynamic SQL comment. I'm using dynamic SQL in 8.0.x and 8.1.x and find it quite effective. I'm doing the same thing as you, calling the same stored procedure in a number of distributed databases.

In Oracle 8.1.6 it looks like this:

wk_sql_stmt := 'BEGIN :1 := FN_GW_INSERT_RESULTS@' ||
wk_database_link; END;';

EXECUTE IMMEDIATE wk_sql_stmt;

In this case the SP returns a value, hence the "BEGIN" and the ":1" bind variable. One thing to remember - link names need to be concatenated, they can't be bind variables.

Oracle has spent a lot of effort improving Dynamic SLQ in 8i. I would encourage using it - I HATE hardcoding site info into procedures.