Q

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 ;
EXECUTE IMMEDIATE (v_stmt) ;

For More Information


This was first published in April 2003

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close