Hi,
I have heard that PRO-IV will only connect to 1 Database at a time.
We are thinking of converting to Oracle and need to connect to many Databases.
Anyone have any ideas?????
Thanks
Rob
5 replies to this topic
#1
Rob Donovan
-
- Admin
-
- 1,647 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
#2
Guest_bob neville_*
Guest_bob neville_*
-
- Guests
Posted 27 October 1999 - 12:50 PM
If you are using Unix there is no problem. You just amend your .profile file for each user to point to the correct database.
However, if you are on NT it is a major problem.
In the PRO4.ini file you have the connect parameter which
takes the form username/password@host_name (the same as a normal SQL call). This will route via SQLNET. We had a problem for about 3 months where users were getting thrown out of the system with an Oracle end-of-communication-channel error. The only way this could be resolved was to remove the @host_name part of the connect string and all
was OK (under very light loads it seemed OK but with 2+ users disaster struck !!).
As you can't use the @host_string part of the call you are limited to using the default database set up in the registry.
We are using multiple schemas within the same database to enable us to emulate a multiple database environment, this is OK for development but not ideal for live.
We use version 4.6, Oracle 8.0.5.1.5a and NT4.
However, if you are on NT it is a major problem.
In the PRO4.ini file you have the connect parameter which
takes the form username/password@host_name (the same as a normal SQL call). This will route via SQLNET. We had a problem for about 3 months where users were getting thrown out of the system with an Oracle end-of-communication-channel error. The only way this could be resolved was to remove the @host_name part of the connect string and all
was OK (under very light loads it seemed OK but with 2+ users disaster struck !!).
As you can't use the @host_string part of the call you are limited to using the default database set up in the registry.
We are using multiple schemas within the same database to enable us to emulate a multiple database environment, this is OK for development but not ideal for live.
We use version 4.6, Oracle 8.0.5.1.5a and NT4.
#3
Chris MacAndrew
-
- Members
-
- 13 posts
Member
- Gender:Male
- Location:Edinburgh, United Kingdom
Posted 27 October 1999 - 01:01 PM
Hi,we are currently doing this at the moment! We have several ProIV
commercial system which integrate to Oracle Financials, aswell as
having edicated ProIV/Oracle databases.
The way this is achieved (ProIV Could not tell us) is as follows.
1. Create the rquired number of Oracle Instances/databases.
2. Use one as a 'primary' point of reference and use this within
the users login profile (Unix) - variable ORACLE_SID to point to
this database/instance.
3. Within this 'primary' SID, you have to create links to connect
the required databases. This has to be done in the database
via SQL.
CREATE PUBLIC DATABASE LINK link-name CONNECT TO user IDENTIFED BY password
USING 'other-database-name.world';
4. Within the 'primary' SID create synonyms (also PROIV filename)
i.e there is a file in ProIV called CUSTOMER (see below)
The ar.ar_customer..... table below exists in our Oracle Financials DB.
CREATE SYNONYM CUSTOMER FOR AR.AR_CUSTOMER_PROFILE_AMOUNTS@link-name;
Repeat this process for all tables required for all required databases.
Any problems, get in touch!!
Regards
Chris.
commercial system which integrate to Oracle Financials, aswell as
having edicated ProIV/Oracle databases.
The way this is achieved (ProIV Could not tell us) is as follows.
1. Create the rquired number of Oracle Instances/databases.
2. Use one as a 'primary' point of reference and use this within
the users login profile (Unix) - variable ORACLE_SID to point to
this database/instance.
3. Within this 'primary' SID, you have to create links to connect
the required databases. This has to be done in the database
via SQL.
CREATE PUBLIC DATABASE LINK link-name CONNECT TO user IDENTIFED BY password
USING 'other-database-name.world';
4. Within the 'primary' SID create synonyms (also PROIV filename)
i.e there is a file in ProIV called CUSTOMER (see below)
The ar.ar_customer..... table below exists in our Oracle Financials DB.
CREATE SYNONYM CUSTOMER FOR AR.AR_CUSTOMER_PROFILE_AMOUNTS@link-name;
Repeat this process for all tables required for all required databases.
Any problems, get in touch!!
Regards
Chris.
#4
Rob Donovan
-
- Admin
-
- 1,647 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 27 October 1999 - 01:02 PM
Hi,
Yeh, I know about that, but I don't want to logout of PRO-IV
I want to be able to access 2 Databases in the same function.
Rob
Yeh, I know about that, but I don't want to logout of PRO-IV
I want to be able to access 2 Databases in the same function.
Rob
#5
Joseph Bove
-
- Members
-
- 756 posts
ProIV Guru
- Gender:Male
- Location:Ramsey, United States
Posted 28 October 1999 - 02:30 PM
Chris,
I was curious, with your solution, can the end customer easily setup alternate regions?
Many of our clients set up at least two regions: live and test. For the most part, we provide the scripts necessary from them to create their multiple regions. (Our scripts set the SID and other values so that each user can access any instance of our applications.)
If the system administrator of the end user is able to set up multiple regions without a great deal of work, your solution sounds better than what we have for our needs....
Joseph
I was curious, with your solution, can the end customer easily setup alternate regions?
Many of our clients set up at least two regions: live and test. For the most part, we provide the scripts necessary from them to create their multiple regions. (Our scripts set the SID and other values so that each user can access any instance of our applications.)
If the system administrator of the end user is able to set up multiple regions without a great deal of work, your solution sounds better than what we have for our needs....
Joseph
#6
Guest_David Montgomery_*
Guest_David Montgomery_*
-
- Guests
Posted 16 December 1999 - 06:59 PM
Rob,
I am assuming that you have separate physical Oracle databases rather than a single database with multiple schemas. If you have multiple schemas, there are a number if different solutions.
For separate physical databases, use the following:-
In the alternate file name in @F, use the following format:
%DATABASE%USER.TABLE
Lets say your database is 'TEST1', user is 'OPS' and it contains the table 'MYTABLE', the alternate in @F would be:-
TEST1%OPS.MYTABLE
In the initialisation file (for unix) .proivrc mention:-
[DATABASE - TEST1]
FILETYPE=ORACLE
CONNECTION='???'
where ??? represents the connection parameters for the server/database.
PROIV will access the table MYTABLE from the remote database using the connection specified. In this way you can use serveral databases together. They may be Oracle/Sybase or any other.
SQL_USERNAME & SQL_PASSWORD etc still hold the DEFAULT database.
I am assuming that you have separate physical Oracle databases rather than a single database with multiple schemas. If you have multiple schemas, there are a number if different solutions.
For separate physical databases, use the following:-
In the alternate file name in @F, use the following format:
%DATABASE%USER.TABLE
Lets say your database is 'TEST1', user is 'OPS' and it contains the table 'MYTABLE', the alternate in @F would be:-
TEST1%OPS.MYTABLE
In the initialisation file (for unix) .proivrc mention:-
[DATABASE - TEST1]
FILETYPE=ORACLE
CONNECTION='???'
where ??? represents the connection parameters for the server/database.
PROIV will access the table MYTABLE from the remote database using the connection specified. In this way you can use serveral databases together. They may be Oracle/Sybase or any other.
SQL_USERNAME & SQL_PASSWORD etc still hold the DEFAULT database.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
