Open theory opened 2 years ago
If you have access to your tnanames.ora
, use $TWO_TASK
and add an entry like
conn_name = ( DESCRIPTION = ( ADDRESS_LIST =
( ADDRESS = ( HOST = db-host )( PORT = 1521 )( PROTOCOL = TCP )))
( CONNECT_DATA = ( SERVICE_NAME = SERV.ICE )( SERVER = DEDICATED )))
and then
% setenv TWO_TASK conn_name
$ export TWO_TASK=conn_name
Your connection should then just be
$ sqlplus USER/PASS@conn_name
$ perl -MDBI -wE'my $dbh = DBI->connect ("dbi:Oracle:", "USER", "PASS");'
When you do not have access to tnsnames.ora modification, you can set $TWO_TASK
like this:
% setenv TWO_TASK //db-host:1521/SERV.ICE
$ export TWO_TASK=//db-host:1521/SERV.ICE
Oh, right, TWO_TASK
. Is there no way to specify it purely in the connection string, no tnsnames.ora
, no TWO_TASK
? Would it make sense to add a method for it, like svc=
or something? Or does instant client also only support those two methods?
(Sorry for the basic questions, I'm an Oracle dilettante keeping Sqitch working on Oracle.)
If you have the tnanames.ora
entry as you show above, isn't conn_name
a SID for the net service? Won't this work?
DBI->connect('dbi:oracle:sid=conn_name')
Tonight I properly noticed this bit from the docs:
The connection string is always of the form: "dbi:Oracle:
" There are several ways to identify a database:
- If the database is local, specifying the SID or service name will be enough.
- If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file
- To connect without TNSNAMES.ORA file, you can use an EZCONNECT url, of the form: //host[:port][/service_name]
And now I feel like a right idiot. So I started modifying URI::oracle in URI::db to have it preferentially generate EZCONNECT URLs and fall back on DBI-style key/value pairs only when there are query parameters in the URL, figuring anyone needing to use a SID could put it in the query. But then I got to thinking: what if someone needs query params when connecting to a service name? Is that supported? Does this work?
DBI->connect('dbi:Oracle://example.com:1522/pdb1:SERVER=POOLED')
Doesn't seem like it would. A service_name
param would simplify the case of using a service name and other params, so it would then be:
DBI->connect('dbi:Oracle:host=example.com:port=1522:service_name=pdb1:SERVER=POOLED')
Does that make sense?
Does DBD::Oracle support "Easy Connect Plus" syntax? https://download.oracle.com/ocomdocs/global/Oracle-Net-19c-Easy-Connect-Plus.pdf
Does DBD::Oracle support "Easy Connect Plus" syntax?
That's what the docs say, and they show this example:
$dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password');
But I don't understand how it can work with other params, unless they're required to be passed in the options instead of the DSN? Something like
$dbh = DBI->connect("dbi:Oracle://myhost:1522/ORCL",'username', 'password', {
ora_server => 'POOLED',
});
If all params aside from host
, port
, and sid
can be passed that way, I think I can work with that.
That is the (original) Easy Connect syntax without the additional 19c optional ?/& syntax.
My DBD::Oracle VM is AWOL so I can't quickly check this newer syntax.
I have often used a SID to connect, but apparently Oracle 12c and later added support for Net Services, which are also named, but not by
sid=$name
. I learned of this fact in this comment. Any pointers for how to connect using a net services name instead of a SID? Will be happy to contribute a documentation PR if appropriate.