Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Unanswered: Connect to different Oracle databases in PL/SQL code

    Hi,

    Here around 250 oracle databases in our company. Extract information from each database and load them in my target databases.

    Info like database name, host name, port number…etc

    So here I' have written the Oracle Procedure, looks like we can't use

    CONNECT abc/password@' || DB_CUR1.DB_NAME ||';'


    DB_CUR1.DB_NAME --> define the cursor and db_name has list of 250 oracle database name(SID).

    I knew its possible using database link, but need to create 250 database links.

    Please let me know if there is any alternative way to connect to each database store them in target database


    Any help that will make job easy

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please let me know if there is any alternative way to connect to each database store them in target database
    SQL*Net is the only way to connect to a remote Oracle instance.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    As you said, SQL*Net is only way! Will you please provide more detail steps

    Thank You

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It appears that you'll have to create 250 database links.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by nsrdata View Post
    As you said, SQL*Net is only way! Will you please provide more detail steps
    You have a chicken & the egg dilemma.
    For each remote instance you need a tnsnames.ora file entry which includes hostname, service_name & port.
    Only you or others at YOUR site can provide these details.
    Sorry, but my crystal ball in is the shop for repair.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by anacedent View Post
    Sorry, but my crystal ball in is the shop for repair.
    Was it made by Toyota, by chance?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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