Oracle App Server Tips

Mark Rittman


Building Portal Applications Using Remote Database Links

I’ve just had to put together some charts and reports with Oracle Portal against a remote database, and thought it’d be worth jotting down how this is done, as it’s not that obvious immediately.

If you’re building an application, charts, forms or reports using the Oracle Portal wizards, and the data is held on a different database from the one Portal is running on, you need some method of ‘reaching across’ and accessing the data on the remote server. The way you do this is as follows;

  1. Log in to portal using the PORTAL userid, then go to the navigator page, then click on the ‘database objects’ tab.
    Click on the PUBLIC user (you’re going to create a PUBLIC DATABASE LINK), then at the top of the screen, where it says ‘create new…’ click on ‘database link’
  2. In the ‘database link’ text box, enter a name for your database link (for example, ‘proddb_link’). In the ‘schema’ box, leave it as ‘PUBLIC’.
  3. On the next page, select the ‘specific user’ radio box, and enter the username and password of the schema you’re going to connect to.
  4. On the next page, enter the tnsnames.ora name for the server you’re going to connect to, if you’ve got an entry in the portal tnsnames.ora file for the server you’re going to connect to. If not, click the second option, and enter the host address, service name, protocol and port.
  5. If it all works ok, you’ll then get brought back the database schema navigator page. If it doesn’t work, you’ve probably specified the connect details incorrectly.
  6. Next, at the top of the screen, where it says ‘create new…’, this time select ‘synonym’ (you’re now going to create a public synonym for each table you want to view). Enter the synonym name (in most cases make it the same as the table name you want to select from), leave schema as ‘public’, and make the object type ‘table’.
  7. On the next page, under object name, type in the name of the table you want to select from on the remote system (leave out the schema name). In the database link field, select the database link name you’ve just created.

Then, when you create your form, report, chart or other application built using the Portal application wizards, when you come to the part where you select the table to base the item off, overtype the standard ‘SCOTT.EMP’ entry with the synonyms you created, then press ‘add’. Your table on the remote database will then show up and can be used for the application.




John Garmany
Burleson Oracle Consulting

Kittrell, NC, USA, 27544 





Burleson Consulting
Kittrell, NC, 27544

Phone (800) 766-

Copyright © 1996 - 2015 by Burleson . All rights reserved.