Before we connect to Oracle, we must establish a few DBI variable-naming conventions (listed in Table B-1). Table B-1. Conventional Perl DBI variable names Name | Description | $dbh | The database handle created on database connection. | $sth | The SQL statement handle. | $drh | The driver handle, mostly used internally by the Perl DBI package. | $h | Can represent any of the three main handles above. | $rc | A general DBI return code, mostly used in a Boolean context. | $rv | A general DBI return value, often used numerically . | @ary | A list of returned scalars, or a row fetched from the database. | $rows | The number of rows processed . | $fh | A file handle, often used to change any default output from STDOUT . | undef | Perl's generic undefined value is used in DBI for NULLs. | %attr | A general name for hashes used to store various attributes. | connect The connect Perl DBI constructor method generates our main database handle, $dbh : use DBI; my $data_source = "dbi:Oracle:orcl"; my $user = "scott"; my $password = "tiger"; my %attr = (RaiseError => 0, AutoCommit => 1); my $dbh = DBI->connect( $data_source , $username, $password, \%attr) or die $DBI::errstr; Note the following characteristics of the connection string held above in $data_source : -
$data_source is composed of three elements separated by colons. However if $data_source is undefined, the connect method will replace it with the environmental variable DBI_DSN, making the following possible: DBI->connect(undef, $username, $password) This becomes interpreted as: DBI->connect( $ENV{DBI_DSN} , $username, $password) -
Sometimes the "Oracle" driver part of the connection string may be missing, as in: DBI->connect("dbi::orcl", $username, $password) In this case, the environmental variable DBI_DRIVER is assumed, as if the code actually looked like this: DBI->connect("dbi: $ENV{DBI_DRIVER} :orcl", $username, $password) -
Sometimes, the actual target database string, such as orcl , may be missing: DBI->connect("dbi:Oracle:", $username, $password) In this case TWO_TASK, or subsequently ORACLE_SID, is assumed: DBI->connect("dbi:Oracle: $ENV{TWO_TASK} ", $username, $password) Looking inside the $dbh variable Assuming that everything goes well, we should now have a valid database handle stored in the $dbh variable. But what's actually inside this? Let's find out: my $dbh = DBI->connect('dbi:Oracle:orcl', 'scott', 'tiger'); print "dbh >", $dbh, "<\n"; Blessed references give us both the class label and an object reference: dbh >DBI::db=HASH(0x466cd40)< What we have in $dbh is the key to a DBI::db object. However, Perl DBI is unusual in Perl. It operates within a hierarchy of objects rather than just one. As well as having DBI::db objects, we later hang SQL statement objects off these objects (like baubles from a Christmas tree). Each database handle gets its own collection of statement handles. This hierarchy can be seen in Figure B-1. Figure B-1. Database handles and statement handles Each of these handles can also be assigned its own collection of initial and modifiable attributes. Let's see that connection code again: my %attr = (RaiseError => 0, AutoCommit => 1); $dbh = DBI->connect($data_source, $username, $password, \%attr ); You'll often see variations on this theme, with anonymous hashes used instead: $dbh = DBI->connect($data_source, $username, $password, {RaiseError => 0, AutoCommit => 1} ); # Anon. Hash We cover the main generic handle attributes in Table B-2 (many of these are read-only) and the database handle specific attributes in Table B-3. Reading and occasionally resetting these attributes is straightforward: $old_value = $h->{AttributeName}; # Reading $h->{AttributeName} = $some_new_value; # Setting Table B-2. Main generic handle attributes Attribute | Description | PrintError | Forces errors to generate warnings. Default is on. | RaiseError | Forces errors to make the program die. PrintError runs before RaiseError , if both are on. Default is off. | Warn | Enabled by default to generate useful warnings. | ShowErrorStatement | Appends DBI statement text to the end of other error messages usually generated by the database. | Kids | For driver handles Kids is the current number of related database handles. For database handles it's the number of associated statement handles. | CachedKids | For a driver handle, references database connections created by connect_cached . For database handles, this references prepare_cached statements. | Taint | If switched on, all "fetched" data is tainted if Perl is in taint mode and method arguments are checked for taintedness. | LongReadLen | Controls the maximum length of long fields such as the various LOBs (large objects). The default LongReadLen value of 80 returns undef for all long fields. | LongTruncOk | If any long data exceeds the LongReadLen value, the fetch will fail. If set to true, the long data is truncated appropriately. Default is off. | FetchHashKeyName | Used with the fetchrow_hashref method and defaulted to NAME , which may return column names in a mixture of upper and lowercase. Set to NAME_uc or NAME_lc to force uppercase or lowercase, respectively. | ChopBlanks | For fixed-width fields, controls blanks trimming. | Table B-3. Main database handle attributes Attribute | Description | AutoCommit | Automatically commits DML statements when set to true. Defaults to true in order to line up with JDBC and ODBC standards. Robust transactions in production code should switch this attribute off and use the eval operator, which fills the $@ variable with relevant information if RaiseError throws the eval statement (if switched on; see earlier). This behavior is used to create try-catch [1] structures: $dbh->{AutoCommit} = 0; # Turn off! :-) $dbh->{RaiseError} = 1; # Turn on! 8-) eval { # try do_some_stuff( ); do_some_other_stuff( ); $dbh->commit; }; if ($@) { # catch warn "Transaction failed: $@"; $dbh->rollback; do_some_other_cleanup_stuff( ); } | Driver | Holds the parent driver's handle. Useful for finding the name of the driver on a multi-driver system: print $dbh->{Driver}->{Name}, "\n"; | Name | Holds the TNSNAME of the database, where TNSNAME is part of the connection string, dbi:Oracle:TNSNAME . | Statement | Holds the latest prepared or executed statement string. | RowCacheSize | A driver hint for row cache sizes for SELECT statements. Very useful for speeding up DBI. | [1] See Pete Jordan's Exception.pm module for more explicit try-catch structures: http://www.cpan.org/authors/id/P/PJ/PJORDAN/ Alternative Oracle connection scenarios There are several alternatives for connecting to Oracle. You can use the first alternative, shown in the following example, if you don't have access to a tnsnames.ora file: $dbh = DBI->connect("dbi:Oracle:host=myhost.com;sid=orcl", $username, $password); You can specify the port number in the connection, as shown in the next example. If you don't specify the port number, DBD::Oracle will try ports 1526 and 1521 in that order. Other variations, which are particularly appropriate for older SQL*Net systems, can be used if TWO_TASK or ORACLE_SID have not been set: $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@orcl','password'); $dbh = DBI->connect('dbi:Oracle:orcl','username','password'); $dbh = DBI->connect('dbi:Oracle:orcl','username/password',''); $dbh = DBI->connect('dbi:Oracle:host=foobar;sid=orcl;port=1521', 'scott/tiger', ''); $dbh = DBI->connect('dbi:Oracle:', q{scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost) (PORT=1521))(CONNECT_DATA=(SID=orcl)))}, ""); Oracle-specific connection attributes You can select three connection attributes especially for Oracle: - ora_session_mode
-
Used to connect with SYSDBA or SYSOPER authorization: DBI->connect($data_source, $username, $password, { ora_session_mode => 2 }); # SYSDBA DBI->connect($data_source, $username, $password, { ora_session_mode => 4 }); # SYSOPER - ora_oratab_orahome
-
Set this attribute to true when you are using a DBD::Oracle version built against Oracle7. Doing so changes $ENV{ORACLE_HOME} to the Oracle home directory specified in /etc/oratab , if the database is listed there: DBI->connect($data_source, $username, $password, { ora_oratab_orahome => 1 }); # True - ora_module_name
-
Passed to the SET_MODULE function in the DBMS_APPLICATION_INFO package, which identifies this calling Perl application for monitoring and performance tuning purposes. In the following example, $0 is the built-in Perl scalar variable holding the name of the Perl script.: DBI->connect($dsn, $user, $passwd, { ora_module_name => DBI->connect($dsn, $user, $passwd, { ora_module_name => $0 } ); } ); connect_cached The connect_cached method is virtually identical in appearance to connect , described in the previous section: $dbh = DBI->connect_cached($data_source, $username, $password, \%attr); New database handles are cached. Whenever another call is now made to connect_cached using identical connection parameters, the cached database handle is returned if it is still available. If the handle is not available, a new one is created, as with connect . available_drivers The available_drivers method lets us know which DBD drivers (such as DBD::Oracle ) are available on the system: @ary = DBI->available_drivers; data_sources The data_sources method lists the available database targets. This method is useful for populating drop-down CGI or Perl/Tk boxes to choose a target database before connection. If no `Oracle' parameter is supplied, the environmental variable DBI_DRIVER is assumed: @ary = DBI->data_sources('Oracle'); ( DBD::Oracle reads oratab and tnsnames.ora to get this information.) trace The trace method lets you set the desired debug trace level. Various debug trace levels (shown in Table B-4) are possible under DBI. The default is to turn off tracing. Table B-4. Tracing levels Level | Description | | Tracing is disabled. | 1 | Useful for overviews. | 2 | For more serious debug work. | 3, 4, 5... | Ever more complex detail for hard- core developers. | Typical calls might look like this: DBI->trace(0); # Turn tracing off. DBI->trace(1); # Turn tracing on, STDERR output. DBI->trace(2, "my_trace_file.txt"); # Increase trace level, and # redirect to named file. | You can use another method in conjunction with trace for your own debug messages, as shown here: DBI->trace_msg( $message ) ; DBI->trace_msg( $message, $min_level ) ; If the trace level is greater than 0, this will write $message to either STDERR or any other nominated trace file, or you can specify the minimum level it should report on. | | For further trace ability, Perl DBI also holds the very latest handle information in the following handles: - $DBI::err :
-
Holds the Oracle error code from the last method called. - $DBI::errstr :
-
Returns the latest Oracle error message. Let's test this by setting up a piece of code we know will go wrong: $sth = $dbh->prepare('SELECT Should_go_wrong" from dual'); # Quote! :) print "DBI::err: >", $DBI::err, "<\n"; print "DBI::errstr: >", $DBI::errstr, "<\n"; This produces the following output: DBI->err: >1740< DBI->errstr: >ORA-01740: missing double quote in identifier (DBD ERROR: OCIStmtPrepare)< |