I would like to know if anyone shares my opinions about Oracle database connections ...
Current state (I?m using TIS TE 4.0.3)
The current available connection types for Oracle are :
- Oracle OCI ? Using Oracle JDBC OCI driver (actually, oci8)
Generated database URL for this type of connection: jdbc
racle
ci8:@local_service_name
- Oracle SID ? Using Oracle JDBC thin driver
Generated database URL for this type of connection: jdbc
racle:thin:@server:1521:sid
- Oracle Service Name ? Using Oracle JDBC thin driver
Generated database URL for this type of connection: jdbc
racle:thin:@(description=(address=(protocol=tcp)(host=server)(port=1521))(connect_data=(service_name=service_name)))
Current limitations ...
Actually, the way Oracle database URLs are generated are too restrictive.
How about using thin driver with TNS alias (available since Oracle 10gR2)?
How about using thin driver with a specific connection descriptor (LOAD_BALANCE, FAILOVER, ?)?
How about using thin driver with connection descriptor stored in a directory (LDAP)?
How about ??
Following link points to Oracle 10gR2 documentation about the supported database specifiers in jdbc database URLs:
http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/urls.htm#sthref482
Suggestions ...
The way I would like to see things in Talend Studio in order to create Oracle Database connections is:
When creating a database connection in the metadata/Db Connections (In Window ?New Database Connection on repository ? Step 2/2?, section ?Database Settings?) :
* In the combo box "DB Type", show only one option for Oracle : ?Oracle Database?
* ... (Who can tell me what the real impact is when choosing the database version?)
* Add a new Combo box for Oracle connections named ?Connection type? with following options:
- JDBC OCI driver
- JDBC thin driver
When choosing ?JDBC OCI driver? ? keep other fields as they currently are (I have no suggestions to make right now).
When choosing ?JDBC thin driver? ? put both ?SID? and ?Service Name? field with a mutual exclusion mechanism (radio buttons?)
* A further step is to add an additional option allowing specifying a custom database specifier (in the connection string jdbc
racle:driver_type:@database_specifier)
instead of the automatically generated one (with the server, port, sid/service name/local service name fields).
The tOracle* components will need some modifications in order to display same information when not using an existing connection.
I?m not going to talk about the schema field (Oracle schema) in this topic. Oracle shema deserves a specific topic that I will create soon.