Well, I have read many posts about this, but was unable to find a suitable solution up to now … Google was not my friend

The most popular answers featured only the first part of the code,

the connection set-up:

use DBI;
#
# some code
#
my $dbh = DBI->connect("dbi:Oracle:","$Usr","$Pwd",

                                     {ora_session_mode => ORA_SYSDBA});

Unfortunately, leaving the variable $User and $Pwd empty did not do the trick, see error below.

DBI connect('','',...) failed: ORA-01017: invalid username/password; logon denied (DBD ERROR: OCISessionBegin)

Ok, but what about internal connection sqlplus '/ as sysdba'?

After some research in the documentation, I came across two important points which we should keep in mind:

• DBI is a database independent, generic interface module!
• DBD::Oracle is the Oracle specific module for Oracle.

The DBD::Oracle documentation (available on http://search.cpan.org)  and some other sources offered the solution:

In Perl special constants are defined in the database driver module DBD::Oracle. These constants have to be passed as part of the connect options to make connecting as SYSDBA or SYSOPER possible for members of the operating system groups OPER,DBA (works also for the windows members of the ORA_DBA group, under condition that the SQLNET.ORA parameter SQLNET.AUTHENTICATION SERVICES are set to NTS):

use DBD::Oracle qw(:ora_session_modes); # imports SYSDBA or SYSOPER

Et voilà, simply add the above line in your code and enjoy. Cool!

In the demo presented below, we connect as a non-privileged user, that means not using the SYSDBA or SYSOPER privileges. The special constant :ora_session_modes won't be imported, however we want the Unix user "oracle" to be able to connect without password. Therefore, we create the appropriate database user with prefix ops$ :

SQL>  SHOW PARAMETER os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

SQL> CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
SQL> GRANT CONNECT TO ops$oracle;

In a second step, we must login as Unix user:

oracle@vmtestora:/home/oracle/ [DB11] id oracle

uid=500(oracle) gid=500(oracle) groups=500(oracle),502(dba)

oracle@vmtestora:/home/oracle/ [DB11] sqlplus /

Connected.

With this few code we just fetch the sysdate from the database using the "ops$" identification:

#!/u00/app/oracle/product/11.2.0/db_2_0/perl/bin/perl
use DBI;

use DBD::Oracle;

my $dbh = DBI->connect("dbi:Oracle:","/",undef)
                       or die "$DBI::errstr";
my $sth = $dbh->prepare("select sysdate from dual") or die "$DBI::errstr";

$sth->execute() or die "couldn't execute statement\n";
while (my $row = $sth->fetchrow_array())
{
   print "Database $ENV{ORACLE_SID}-> sysdate: $row\n";
}

The result:

oracle@vmtestora:/home/oracle/ [DB11]./test.pl
Database DB11-> sysdate: 22-DEC-10

As you can see, a "normal" database connection without pasword doesn't require the special constants (like ":ora_session_modes").