Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 last published in April 2003

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.