Calling Functions across a database link

[SpiritOfLennon]

Hi,
I have two Oracle databases on a WAN, one Oracle 7 and one Oracle 8. I have a function to check credit on the Oracle 8 data base and I want to call this function from the Oracle 7 database. I know I could just replicate the function in the Oracle 7 database and point it at the Oracle 8 database, however I am concerned about the network traffic that may be involved in this. So my question is, what is the best way to handle this scenario, is it possible to call a function remotely and just return the result or should I be looking at creating a procedure or package that can be called remote? Any advice would be appreciated. Thanks. SOL
I'm only guessing but my guess work generally works for me.

 

[Kenrae]

You can create a synonym for the remote procedure (at least you can do this with a package), and then call it.

 

[Fredzo]

What you need to do is create the function/stored procedure
in the remote database, create a link between the two, and
use EXECUTE IMMEDIATE to call the function.

Here's an example of a SELECT:

EXECUTE IMMEDIATE 'SELECT CRDPTR_LOGLOC_ID
FROM GW_CRDPTR_LOGLOC@' || wk_card_location || '
WHERE ICPS_PTR_ABBREV = tr_abbrev'
INTO wk_ptr_id
USING wk_card_location_abbrev;

Here's an example of a remote function call returning
a value:

EXECUTE IMMEDIATE 'BEGIN :rtn_value := FN_GW_SEND_CARD_TO_PRINTER@' ||
wk_card_location ||
'crd_id, :dest_link, :dest_ptr); END;'
USING OUT wk_rtn_value,
IN card_id_rec.CRD_ID,
IN wk_dest_link,
IN wk_dest_ptr;

 

[SpiritOfLennon]

Thanks for all the advice but I've discovered that all you have to do is call the function at the database link
e.g. function_name@database link! SOL
I'm only guessing but my guess work generally works for me.