in reply to Connecting 'as sysdba' using DBD::Oracle

Just installed DBD::Oracle and the following works for me: 
#!/usr/bin/perl

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

my $dbhost = "my.server.name";
my $dbuser = "sys"; # not "system" or "/"
my $dbpass = "password";

my $sid = "SIDNAME";

my $dbh = DBI->connect(
  "dbi:Oracle:host=$dbhost;sid=$sid", 
  $dbuser, 
  $dbpass, 
  { ora_session_mode => ORA_SYSDBA }
);

if ($dbh) {
  print "Connected\n";
} else {
  die $DBI::errstr;
}

$dbh->disconnect;

[download]
Server is Oracle9i Enterprise Edition Release 9.2.0.6.0

Update: No TNS file, no ORACLE_SID environment variable.

-- Time flies when you don't know what you're doing

Replies are listed 'Best First'.
Re^2: Connecting 'as sysdba' using DBD::Oracle
by newbie01.perl (Sexton) on Nov 27, 2009 at 12:49 UTC

    Hi,

    Your solution work because technically you are supplying the username and password which simulates connecting from the network to the database

    I believe the issue here is if you are going to use the script to run locally on the server, there should not be a need to supply username and password because you are already on the server itself. The main idea for this is to not having to specify passwords on your scripts for security reason.

[reply]
      I know that this is an old thread, but I was having the same problem and Google popped it to the top of the list when I searched for 'perl oracle connect as sysdba'. Just in case anyone else starts looking for an answer to this, the solution is to use a dsn of 'dbi:Oracle:' and undef for the username and password. Provided that you are running as oracle (or are a member of the dba group) on the host where the database is running and you have the ORACLE_SID variable set properly and you use the ora_session_mode => ORA_SYSDBA pair in the connection hash you will connect OK. At least that worked for me.
[reply]
        I have some insight into this now. I did an strace of the process and the short answer is that there is conflicting posts online about the right way to do this.

        When installing DBD::Oracle, the advice is to build it against the Oracle Instant Client so that database upgrades don't break DBD::Oracle. When you need DBD::Oracle *and* you need to connect '/ as sysdba', you can't use the instant client because you are requesting an internal connection to the database. This attempts to exec the $ENV{ORACLE_HOME}/bin/oracle binary which does not exist.

        For this to work, you have to build/link against the full database install.

[reply]
        Thanks, this suggestion helped me to connect as sysdba..
[reply]

In Section Seekers of Perl Wisdom