Connecting Database

[bakher]

Hello All,

I have 2 databases say Development and QA. How can i connect QA database from Development ie thru(sql editor).

Is it possibel to run QA store proc from Development(SQL EDITOR)

Thanks

 

[THoey]

You will need to look up DATABASE LINKS in the software. Too much information to provide you right here. And, yes, you can call a procedure from the LINKED DB, although I am not sure if it runs on the LINKED DB's data or on the local data. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[AOLEURO]

Hi

You use CREATE DATABASE LINK ....

Like this:

create database link QA connect to SCHEMA identified by PASSWORD using ‘QA.world’;

AQ = Service name on the AQ server
AQ.world is a net service name insight TNSNAMES.ORA on the development server.

Just remember that all above is between the to severs – and not your Client. So TNSNAMES.ORA has to be correct on the development server.


And then you use a table on the development server – you write extension just like a connection string:

Select empno from employee@AQ;

And yes it is possible to run code and data - but be careful with the code, because performance is down - if your code walks in and out from SQL and PL/SQL engine because it happens between the servers with data.
Regards
Allan
Icq: 346225948

 

[AOLEURO]

Hi again

I did not see your reply Terry.

Yes data can be combined between servers – just remember that data are transferred to the PL/SQL engine server. In the example all data from AQ are transferred to development server – just consider the AQ server as a remote database.

It is dangers to use statements where Oracle ex. tries to make a JOIN where it get a row from development server and ask AQ for data on that row – and get another row from development and ask for AQ for data on that row – and so on.

Your performance is down.

You can this:

Select empno from employee E, (select empno from employee@AQ where deptno = 3 ) AWemp
Where E.empno = AWemp.empno;

Oracle gets all rows from AQ before JOIN in the development server.

..But it is best to avoid JOINs between servers – get data and do something with them.

Sometimes it is best to make a materialized view in the development server.


…But it depends
Regards
Allan
Icq: 346225948