Infrastructure at your Service

Jérôme Witt

How to connect to an Oracle database using the perl DBI module and DBD::Oracle “as sysdba”?

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:

[email protected]:/home/oracle/ [DB11] id oracle
uid=500(oracle) gid=500(oracle) groups=500(oracle),502(dba)
[email protected]:/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 statementn";
while (my $row = $sth->fetchrow_array())
{
   print "Database $ENV{ORACLE_SID}-> sysdate: $rown";
}

The result:

[email protected]:/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”).

6 Comments

  • Paul says:

    Thanks so much ! This issue bothered me for a quite while. It works for remote dbs too:

    use DBI;
    use DBD::Oracle qw(:ora_session_modes);

    my $dbh

  • Sunny says:

    Thank you very much..was struggling so hard !!! to run a simple sql statement!! u ve made my day!! 😉
    Regs
    Sunny

  • Deepak says:

    H can we use the following

    require BD::Oracle

    I have a program which should be used for Oracle and Sybase, How can I get around using DBD::Oracle with Sybase also

    • Jérôme Witt says:

      Hi Deepak,

      Unfortunately, i haven’t completed this step in my career until now.
      I would start to have a look on the DBD::Sybase which is available on CPAN..

      Feel free to reply once you got some results 🙂 I’m interested too

      Regards,
      Jerome

  • Vasu Somanna says:

    DBI connection issue resolved with below $dsn order
    DBI->connect( “dbi:Oracle:$phost:$pport/$sid”,

    Thanks
    Vasu.S

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Jérôme Witt
Jérôme Witt

Delivery Manager and Senior Consultant