Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Atlanta
    Posts
    6

    Unanswered: Calling a remote pl/sql pkg

    Is it possible to execute a stored procedure in one instance from another instance?

    Specifically, I have a stored proc that executes a query and generates a report using DBMS_OUTPUT. I want to be able to run the same report on several instances without storing a copy in each instance (to simplify version control).

    Obivously, I want the stored proc to act on the data in the instance from which it is called.

    Thanks, Dan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Calling a remote pl/sql pkg

    Originally posted by dmck43
    Is it possible to execute a stored procedure in one instance from another instance?

    Specifically, I have a stored proc that executes a query and generates a report using DBMS_OUTPUT. I want to be able to run the same report on several instances without storing a copy in each instance (to simplify version control).

    Obivously, I want the stored proc to act on the data in the instance from which it is called.

    Thanks, Dan
    Yes, but you need to be somewhat clever.
    I do something similar by using a "dynamic" database link.
    I created an additional procedure which accepts as its arguments
    "appropriate values" to created a DATABASE LINK, called REMOTE,
    which "points" to the desired instance.

    Now it the "main" procedure it always just references object @REMOTE.

    Just a Small Matter Of Programming (SMOP).

  3. #3
    Join Date
    May 2003
    Location
    Atlanta
    Posts
    6
    Thanks! You know it that pesky SMOP that gives me the most trouble!

    But, if I get your drift, I could:

    CREATE OR REPLACE PROCEDURE GET_DATA ( instance_name IN VARCHAR2) AS
    BEGIN
    .
    .
    .
    SELECT MY_DATA FROM MY_TABLE@instance_name;

    OR would it have to be ?

    EXECUTE IMMEDIATE 'SELECT MY_DATA FROM MY_TABLE@' || instance_name;
    .
    .
    .
    Thanks again, Dan

  4. #4
    Join Date
    May 2003
    Location
    Atlanta
    Posts
    6
    The above assumed I had created the necessary DATABASE LINK's using:

    CREATE DATABASE LINK T1_A USING 't_a';
    CREATE DATABASE LINK T1_B USING 't_b';
    etc.

    and the same user with appropriate privileges was on both local and remote instances!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •