How do you connect to a Net Service instead of a SID?

[theory]

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.

[Tux]

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

[theory]

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.)

[theory]

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')

[theory]

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:

1. If the database is local, specifying the SID or service name will be enough.
2. If the database is defined in a TNSNAMES.ORA file, you can use the service name given in the file
3. 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?

[cjbj]

Does DBD::Oracle support "Easy Connect Plus" syntax? https://download.oracle.com/ocomdocs/global/Oracle-Net-19c-Easy-Connect-Plus.pdf

[theory]

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.

[cjbj]

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.

[djzort]

This has been sitting here for about 4 years. Probably we can either close it or update the README with what we have learned?