NAME

DBD::Oracle - Oracle database driver for the DBI module

SYNOPSIS

  use DBI;

  $dbh = DBI->connect("dbi:Oracle:", $user, $passwd);

  # See the DBI module documentation for full details

DESCRIPTION

DBD::Oracle is a Perl module which works with the DBI module to provide access to Oracle databases.

CONNECTING TO ORACLE

This is a topic which often causes problems. Mainly due to Oracle's many and sometimes complex ways of specifying and connecting to databases. (James Taylor and Lane Sharman have contributed much of the text in this section.)

Oracle environment variables

Oracle typically uses two environment variables to specify default connections: ORACLE_SID and TWO_TASK.

ORACLE_SID is really unnecessary to set since TWO_TASK provides the same functionality in addition to allowing remote connections.

  % setenv TWO_TASK T:hostname:ORACLE_SID            # for csh shell
  $ TWO_TASK=T:hostname:ORACLE_SID export TWO_TASK   # for sh shell

  % sqlplus username/password

Note that if you have *both* local and remote databases, and you have ORACLE_SID *and* TWO_TASK set, and you don't specify a fully qualified connect string on the command line, TWO_TASK takes precedence over ORACLE_SID (i.e. you get connected to remote system).

  TWO_TASK=P:sid

will use the pipe driver for local connections using SQL*Net v1.

  TWO_TASK=T:machine:sid

will use TCP/IP (or D for DECNET, etc.) for remote SQL*Net v1 connection.

  TWO_TASK=dbname

will use the info stored in the SQL*Net v2 tnsnames.ora configuration file for local or remote connections.

The ORACLE_HOME environment variable should be set correctly. It can be left unset if you aren't using any of Oracle's executables, but it is not recommended and error messages may not display.

Discouraging the use of ORACLE_SID makes it easier on the users to see what is going on. (It's unfortunate that TWO_TASK couldn't be renamed, since it makes no sense to the end user, and doesn't have the ORACLE prefix).

Using DBD::Oracle

Below are various ways of connecting to an oracle database using SQL*Net 1.x and SQL*Net 2.x. "Machine" is the computer the database is running on, "SID" is the SID of the database, "DB" is the SQL*Net 2.x connection descriptor for the database.

  BEGIN { 
     $ENV{ORACLE_HOME} = '/home/oracle/product/7.x.x';
     $ENV{TWO_TASK}    = 'DB'; 
  }
  $dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');
  #  - or -
  $dbh = DBI->connect('dbi:Oracle:','scott/tiger');

works for SQL*Net 2.x, so does

  $ENV{TWO_TASK}    = 'T:Machine:SID';

for SQL*Net 1.x connections. For local connections you can use the pipe driver:

  $ENV{TWO_TASK}    = 'P:SID';

Here are some variations (not setting TWO_TASK)

  $dbh = DBI->connect('dbi:Oracle:T:Machine:SID','username','password')

  $dbh = DBI->connect('dbi:Oracle:','username@T:Machine:SID','password')

  $dbh = DBI->connect('dbi:Oracle:','username@DB','password')

  $dbh = DBI->connect('dbi:Oracle:DB','username','password')

  $dbh = DBI->connect('dbi:Oracle:DB','username/password','')

  $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST= foobar)(PORT=1521))
  (CONNECT_DATA=(SID=foobarSID)))}, "")

If you are having problems with login taking a long time (>10 secs say) then you might have tripped up on an Oracle bug. Yoy can try using one of the ...@DB variants as a workaround. E.g.,

  $dbh = DBI->connect('','username/password@DB','');

On the other hand, that may cause you to trip up on another Oracle bug that causes alternating connection attempts to fail! (In reality only a small proportion of people experience these problems.)

Optimizing Oracle's listner

[By Lane Sharman <lane@bienlogic.com>] I spent a LOT of time optimizing listener.ora and I am including it here for anyone to benefit from. My connections over tnslistener on the same humble Netra 1 take an average of 10-20 millies according to tnsping. If anyone knows how to make it better, please let me know!

 LISTENER =
  (ADDRESS_LIST =
        (ADDRESS = 
          (PROTOCOL = TCP)
          (Host = aa.bbb.cc.d)
          (Port = 1521)
                                        (QUEUESIZE=10)
        )
  )

 STARTUP_WAIT_TIME_LISTENER = 0
 CONNECT_TIMEOUT_LISTENER = 10
 TRACE_LEVEL_LISTENER = OFF
 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = xxxx)
      (ORACLE_HOME = /xxx/local/oracle7-3)
                        (PRESPAWN_MAX = 40)
                        (PRESPAWN_LIST=
                                (PRESPAWN_DESC=(PROTOCOL=tcp) (POOL_SIZE=40) (TIMEOUT=120))
                        )
    )
  )

1) When the application is co-located on the host AND there is no need for outside SQLNet connectivity, stop the listener. You do not need it. Get your application/cgi/whatever working using pipes and shared memory. I am convinced that this is one of the connection bugs (sockets over the same machine). Note the $ENV{ORAPIPES} env var. The essential code to do this at the end of this section.

2) Be careful in how you implement the multi-threaded server. Currently I am not using it in the initxxxx.ora file but will be doing some more testing.

3) Be sure to create user rollback segments and use them; do not use the system rollback segments; however, you must also create a small rollback space for the system as well.

5) Use large tuning settings and get lots of RAM. Check out all the parameters you can set in v$parameters because there are quite a few not documented you may to set in your initxxx.ora file.

6) Use svrmgrl to control oracle from the command line. Write lots of small SQL scripts to get at V$ info.

  use DBI;
  # Environmental variables used by Oracle 
  $ENV{ORACLE_SID}   = "xxx";
  $ENV{ORACLE_HOME}  = "/opt/oracle7";
  $ENV{EPC_DISABLED} = "TRUE";
  $ENV{ORAPIPES} = "V2";
  my $dbname = "xxx";
  my $dbuser = "xxx";
  my $dbpass = "xxx";
  my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass)
             || die "Unale to connect to $dbname: $DBI::errstr\n";

Oracle utilities

If you are still having problems connecting then the Oracle adapters utility may offer some help. Run these two commands:

  $ORACLE_HOME/bin/adapters
  $ORACLE_HOME/bin/adapters $ORACLE_HOME/bin/sqlplus

and check the output. The "Protocol Adapters" section should be the same. It should include at least "IPC Protocol Adapter" and "TCP/IP Protocol Adapter".

If it generates any errors which look relevant then please talk to yor Oracle technical support (and not the dbi-users mailing list). Thanks. Thanks to Mark Dedlow for this information.

International NLS / 8-bit text issues

If 8-bit text is returned as '?' characters or can't be inserted make sure the following environment vaiables are set correctly: NLS_LANG, ORA_NLS, ORA_NLS32 Thanks to Robin Langdon <robin@igis.se> for this information. Example: $ENV{NLS_LANG} = "american_america.we8iso8859p1"; $ENV{ORA_NLS} = "/home/oracle/ocommon/nls/admin/data"; $ENV{ORA_NLS32} = "/home/oracle/ocommon/nls/admin/data";

Also From: Yngvi Thor Sigurjonsson <yngvi@hagkaup.is> If you are using 8-bit characters and "export" for backups make sure that you have NLS_LANG set when export is run. Otherwise you might get unusable backups with ? replacing all your beloved characters. We were lucky once when we noticed that our exports were damaged before disaster struck.

Remember that the database has to be created with an 8-bit character set.

Also note that the NLS files $ORACLE_HOME/ocommon/nls/admin/data changed extension (from .d to .nlb) between 7.2.3 and 7.3.2.

SEE ALSO

DBI

AUTHOR

DBD::Oracle by Tim Bunce.

COPYRIGHT

The DBD::Oracle module is Copyright (c) 1995,1996,1997 Tim Bunce. England. The DBD::Oracle module is free software; you can redistribute it and/or modify it under the same terms as Perl itself with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.

ACKNOWLEDGEMENTS

See also "ACKNOWLEDGEMENTS" in DBI.

cpanm DBD::Oracle

perl -MCPAN -e shell
install DBD::Oracle