|
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;
- 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’
- 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’.
- 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.
- 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.
- 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.
- 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’.
- 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.
Regards,
John Garmany
Burleson Oracle Consulting
Kittrell, NC, USA, 27544
www.dba-oracle.com
www.remote-dba.net
|
|
|
