Ask the Expert

Using dblink when defining cursor in PL/SQL routine

Is it possible to use a database link when defining a cursor in a PL/SQL routine? We are looking to run a statement against several databases from a central database and load the results into a table and thought that we could cursor through the names in link$ and somehow use them in the cursor definition to get the info that we need from the other databases. Any suggestions?

    Requires Free Membership to View

See if you can pull the link name you need from the dba_db_links view.

SQL> desc dba_db_links
 Name                       Null?    Type
 -------------------------- -------- ---------------
 OWNER                      NOT NULL VARCHAR2(30)
 DB_LINK                    NOT NULL VARCHAR2(128)
 USERNAME                            VARCHAR2(30)
 HOST                                VARCHAR2(2000)
 CREATED                    NOT NULL DATE
Create a cursor that reads all the links. Loop through the links cursor and dynamically create a select statement to pull the data you want back. If you're unfamiliar with dynamic SQL, see the Oracle docs and look at the chapter on Dynamic SQL (chapter 8).

An example of how you might build the dynamic SQL statment would be:

v_stmt := 'INSERT INTO central_db_table SELECT * FROM other_db_table@' || cursor_rec.db_link ;

For More Information

This was first published in April 2003

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: