File Coverage

blib/lib/Oraperl.pm
Criterion Covered Total %
statement 16 60 26.7
branch 1 20 5.0
condition 0 7 0.0
subroutine 5 17 29.4
pod 9 9 100.0
total 31 113 27.4


line stmt bran cond sub pod time code
1             # Oraperl Emulation Interface for Perl 5 DBD::Oracle DBI
2             #
3             # Oraperl.pm
4             #
5             # Copyright (c) 1994,1995 Tim Bunce
6             #
7             # See the COPYRIGHT section in the Oracle.pm file for terms.
8             #
9             # To use this interface use one of the following invocations:
10             #
11             # use Oraperl;
12             # or
13             # eval 'use Oraperl; 1;' || die $@ if $] >= 5;
14             #
15             # The second form allows oraperl scripts to be used with
16             # both oraperl and perl 5.
17              
18             package Oraperl;
19              
20             require 5.004;
21              
22 1     1   15 use DBI 1.21;
  1         30  
  1         16  
23 1     1   15 use Exporter;
  1         9  
  1         15  
24              
25             $VERSION = substr(q$Revision: 1.44 $, 10);
26              
27             @ISA = qw(Exporter);
28              
29             @EXPORT = qw(
30             &ora_login &ora_open &ora_bind &ora_fetch &ora_close
31             &ora_logoff &ora_do &ora_titles &ora_lengths &ora_types
32             &ora_commit &ora_rollback &ora_autocommit &ora_version
33             &ora_readblob
34             $ora_cache $ora_long $ora_trunc $ora_errno $ora_errstr
35             $ora_verno $ora_debug
36             );
37              
38             $debug    = 0 unless defined $debug;
39             $debugdbi = 0;
40             # $safe # set true/false before 'use Oraperl' if needed.
41             $safe = 1 unless defined $safe;
42              
43             # Help those who get core dumps from non-'safe' Oraperl (bad cursors)
44 1     1   50 use sigtrap qw(ILL);
  1         10  
  1         16  
45             if (!$safe) {
46                 $SIG{BUS} = $SIG{SEGV} = sub {
47             print STDERR "Add BEGIN { \$Oraperl::safe=1 } above 'use Oraperl'.\n"
48             unless $safe;
49             goto &sigtrap::trap;
50                 };
51             }
52              
53              
54             # Install Driver (use of install_driver is a special case here)
55             $drh = DBI->install_driver('Oracle');
56             if ($drh) {
57                 print "DBD::Oracle driver installed as $drh\n" if $debug;
58                 $drh->trace($debug);
59                 $drh->{CompatMode} = 1;
60                 $drh->{Warn} = 0;
61             }
62              
63              
64 1     1   19 use strict;
  1         9  
  1         15  
65              
66             sub _func_ref {
67 5     5   45     my $name = shift;
68 5 50       47     my $pkg = ($Oraperl::safe) ? "DBI" : "DBD::Oracle";
69 5         40     \&{"${pkg}::$name"};
  5         64  
70             }
71              
72             sub _warn {
73 0     0         my $prev_warn = shift;
74 0 0             if ($_[0] =~ /^(Bad|Duplicate) free/) {
75 0 0         return unless $ENV{PERL_DBD_DUMP} eq 'dump';
76 0           print STDERR "Aborting with a core dump for diagnostics (PERL_DBD_DUMP)\n";
77 0           CORE::dump;
78                 }
79 0 0             $prev_warn ? &$prev_warn(@_) : warn @_;
80             }
81              
82              
83             # -----------------------------------------------------------------
84             #
85             # $lda = &ora_login($system_id, $name, $password)
86             # &ora_logoff($lda)
87              
88             sub ora_login {
89 0     0 1       my($system_id, $name, $password) = @_;
90 0   0           local($Oraperl::prev_warn) = $SIG{'__WARN__'} || 0; # must be local
91 0     0         local($SIG{'__WARN__'}) = sub { _warn($Oraperl::prev_warn, @_) };
  0            
92 0               return DBI->connect("dbi:Oracle:$system_id", $name, $password, {
93             PrintError => 0, AutoCommit => 0
94                 });
95             }
96             sub ora_logoff {
97 0     0 1       my($dbh) = @_;
98 0 0             return if !$dbh;
99 0   0           local($Oraperl::prev_warn) = $SIG{'__WARN__'} || 0; # must be local
100 0     0         local($SIG{'__WARN__'}) = sub { _warn($Oraperl::prev_warn, @_) };
  0            
101 0               $dbh->disconnect();
102             }
103              
104              
105              
106             # -----------------------------------------------------------------
107             #
108             # $csr = &ora_open($lda, $stmt [, $cache])
109             # &ora_bind($csr, $var, ...)
110             # &ora_fetch($csr [, $trunc])
111             # &ora_do($lda, $stmt)
112             # &ora_close($csr)
113              
114             sub ora_open {
115 0     0 1       my($lda, $stmt) = @_;
116 0               $Oraperl::ora_cache_o = $_[2]; # temp hack to pass cache through
117              
118 0 0             my $csr = $lda->prepare($stmt) or return undef;
119              
120             # only execute here if no bind vars specified
121 0 0 0           $csr->execute or return undef unless $csr->{NUM_OF_PARAMS};
122              
123 0               $csr;
124             }
125              
126             *ora_bind  = _func_ref('st::execute');
127             *ora_fetch = \&{"DBD::Oracle::st::ora_fetch"};
128             *ora_close = _func_ref('st::finish');
129              
130             sub ora_do {
131             # error => undef
132             # 0 => "0E0" (0 but true)
133             # >0 => >0
134 0     0 1       my($lda, $stmt, @params) = @_; # @params are an extension to the original Oraperl.
135              
136 0               return $lda->do($stmt, undef, @params); # SEE DEFAULT METHOD IN DBI.pm
137              
138             # OLD CODE:
139             # $csr is local, cursor will be closed on exit
140 0 0             my $csr = $lda->prepare($stmt) or return undef;
141             # Oracle OCI will automatically execute DDL statements in prepare()!
142             # We must be carefull not to execute them again! This needs careful
143             # examination and thought.
144             # Perhaps oracle is smart enough not to execute them again?
145 0               my $ret = $csr->execute(@params);
146 0               my $rows = $csr->rows;
147 0 0             ($rows == 0) ? "0E0" : $rows;
148             }
149              
150              
151             # -----------------------------------------------------------------
152             #
153             # &ora_titles($csr [, $truncate])
154             # &ora_lengths($csr)
155             # &ora_types($csr)
156              
157             sub ora_titles{
158 0     0 1       my($csr, $trunc) = @_;
159 0 0             warn "ora_titles: truncate option not implemented" if $trunc;
160 0               @{$csr->{'NAME'}};
  0            
161             }
162             sub ora_lengths{
163 0     0 1       @{shift->{'ora_lengths'}} # oracle specific
  0            
164             }
165             sub ora_types{
166 0     0 1       @{shift->{'ora_types'}} # oracle specific
  0            
167             }
168              
169              
170             # -----------------------------------------------------------------
171             #
172             # &ora_commit($lda)
173             # &ora_rollback($lda)
174             # &ora_autocommit($lda, $on_off)
175             # &ora_version
176              
177             *ora_commit   = _func_ref('db::commit');
178             *ora_rollback = _func_ref('db::rollback');
179              
180             sub ora_autocommit {
181 0     0 1       my($lda, $mode) = @_;
182 0               $lda->{AutoCommit} = $mode;
183 0               "0E0";
184             }
185             sub ora_version {
186 0     0 1       my($sw) = DBI->internal;
187 0               print "\n";
188 0               print "Oraperl emulation interface version $Oraperl::VERSION\n";
189 0               print "$Oraperl::drh->{Attribution}\n";
190 0               print "$sw->{Attribution}\n\n";
191             }
192              
193              
194             # -----------------------------------------------------------------
195             #
196             # $ora_errno
197             # $ora_errstr
198             *Oraperl::ora_errno  = \$DBI::err;
199             *Oraperl::ora_errstr = \$DBI::errstr;
200              
201              
202             # -----------------------------------------------------------------
203             #
204             # $ora_verno
205             # $ora_debug not supported, use $h->debug(2) where $h is $lda or $csr
206             # $ora_cache not supported
207             # $ora_long used at ora_open()
208             # $ora_trunc used at ora_open()
209              
210             $Oraperl::ora_verno = '3.000'; # to distinguish it from oraperl 2.4
211              
212             # ora_long is left unset so that the DBI $h->{LongReadLen} attrib will be used
213             # by default. If ora_long is set then LongReadLen will be ignored (sadly) but
214             # that behaviour may change later to only apply to oraperl mode handles.
215             #$Oraperl::ora_long = 80; # 80, oraperl default
216             $Oraperl::ora_trunc = 0; # long trunc is error, oraperl default
217              
218              
219             # -----------------------------------------------------------------
220             #
221             # Non-oraperl extensions added here to make it easy to still run
222             # script using oraperl (by avoiding $csr->blob_read(...))
223              
224             *ora_readblob = _func_ref('st::blob_read');
225              
226              
227             1;
228             __END__
229            
230             =head1 NAME
231            
232             Oraperl - Perl access to Oracle databases for old oraperl scripts
233            
234             =head1 SYNOPSIS
235            
236             eval 'use Oraperl; 1;' || die $@ if $] >= 5; # ADD THIS LINE TO OLD SCRIPTS
237            
238             $lda = &ora_login($system_id, $name, $password)
239             $csr = &ora_open($lda, $stmt [, $cache])
240             &ora_bind($csr, $var, ...)
241             &ora_fetch($csr [, $trunc])
242             &ora_close($csr)
243             &ora_logoff($lda)
244            
245             &ora_do($lda, $stmt)
246            
247             &ora_titles($csr)
248             &ora_lengths($csr)
249             &ora_types($csr)
250             &ora_commit($lda)
251             &ora_rollback($lda)
252             &ora_autocommit($lda, $on_off)
253             &ora_version()
254            
255             $ora_cache
256             $ora_long
257             $ora_trunc
258             $ora_errno
259             $ora_errstr
260             $ora_verno
261            
262             $ora_debug
263            
264             =head1 DESCRIPTION
265            
266             Oraperl is an extension to Perl which allows access to Oracle databases.
267            
268             The original oraperl was a Perl 4 binary with Oracle OCI compiled into it.
269             The Perl 5 Oraperl module described here is distributed with L<DBD::Oracle>
270             (a database driver what operates within L<DBI>) and adds an extra layer over
271             L<DBI> method calls.
272             The Oraperl module should only be used to allow existing Perl 4 oraperl scripts
273             to run with minimal changes; any new development should use L<DBI> directly.
274            
275             The functions which make up this extension are described in the
276             following sections. All functions return a false or undefined (in the
277             Perl sense) value to indicate failure. You do not need to understand
278             the references to OCI in these descriptions. They are here to help
279             those who wish to extend the routines or to port them to new machines.
280            
281             The text in this document is largely unchanged from the original Perl4
282             oraperl manual written by Kevin Stock <kstock@auspex.fr>. Any comments
283             specific to the DBD::Oracle Oraperl emulation are prefixed by B<DBD:>.
284             See the DBD::Oracle and DBI manuals for more information.
285            
286             B<DBD:> In order to make the oraperl function definitions available in
287             perl5 you need to arrange to 'use' the Oraperl.pm module in each file
288             or package which uses them. You can do this by simply adding S<C<use
289             Oraperl;>> in each file or package. If you need to make the scripts work
290             with both the perl4 oraperl and perl5 you should add add the following
291             text instead:
292            
293             eval 'use Oraperl; 1;' || die $@ if $] >= 5;
294            
295             =head2 Principal Functions
296            
297             The main functions for database access are &ora_login(), &ora_open(),
298             &ora_bind(), &ora_fetch(), &ora_close(), &ora_do() and &ora_logoff().
299            
300             =over 2
301            
302             =item * ora_login
303            
304             $lda = &ora_login($system_id, $username, $password)
305            
306             In order to access information held within an Oracle database, a
307             program must first log in to it by calling the &ora_login() function.
308             This function is called with three parameters, the system ID (see
309             below) of the Oracle database to be used, and the Oracle username and
310             password. The value returned is a login identifier (actually an Oracle
311             Login Data Area) referred to below as $lda.
312            
313             Multiple logins may be active simultaneously. This allows a simple
314             mechanism for correlating or transferring data between databases.
315            
316             Most Oracle programs (for example, SQL*Plus or SQL*Forms) examine the
317             environment variable ORACLE_SID or TWO_TASK to determine which database
318             to connect to. In an environment which uses several different
319             databases, it is easy to make a mistake, and attempt to run a program
320             on the wrong one. Also, it is cumbersome to create a program which
321             works with more than one database simultaneously. Therefore, Oraperl
322             requires the system ID to be passed as a parameter. However, if the
323             system ID parameter is an empty string then oracle will use the
324             existing value of ORACLE_SID or TWO_TASK in the usual manner.
325            
326             Example:
327            
328             $lda = &ora_login('personnel', 'scott', 'tiger') || die $ora_errstr;
329            
330             This function is equivalent to the OCI olon and orlon functions.
331            
332             B<DBD:> note that a name is assumed to be a TNS alias if it does not
333             appear as the name of a SID in /etc/oratab or /var/opt/oracle/oratab.
334             See the code in Oracle.pm for the full logic of database name handling.
335            
336             B<DBD:> Since the returned $lda is a Perl5 reference the database login
337             identifier is now automatically released if $lda is overwritten or goes
338             out of scope.
339            
340             =item * ora_open
341            
342             $csr = &ora_open($lda, $statement [, $cache])
343            
344             To specify an SQL statement to be executed, the program must call the
345             &ora_open() function. This function takes at least two parameters: a
346             login identifier (obtained from &ora_login()) and the SQL statement to
347             be executed. An optional third parameter specifies the size of the row
348             cache to be used for a SELECT statement. The value returned from
349             &ora_open() is a statement identifier (actually an ORACLE Cursor)
350             referred to below as $csr.
351            
352             If the row cache size is not specified, a default size is
353             used. As distributed, the default is five rows, but this
354             may have been changed at your installation (see the
355             &ora_version() function and $ora_cache variable below).
356            
357             Examples:
358            
359             $csr = &ora_open($lda, 'select ename, sal from emp order by ename', 10);
360            
361             $csr = &ora_open($lda, 'insert into dept values(:1, :2, :3)');
362            
363             This function is equivalent to the OCI oopen and oparse functions. For
364             statements which do not contain substitution variables (see the section
365             Substitution Variables below), it also uses of the oexec function. For
366             SELECT statements, it also makes use of the odescr and odefin functions
367             to allocate memory for the values to be returned from the database.
368            
369             =item * ora_bind
370            
371             &ora_bind($csr, $var, ...)
372            
373             If an SQL statement contains substitution variables (see the section
374             Substitution Variables below), &ora_bind() is used to assign actual
375             values to them. This function takes a statement identifier (obtained
376             from &ora_open()) as its first parameter, followed by as many
377             parameters as are required by the statement.
378            
379             Example:
380            
381             &ora_bind($csr, 50, 'management', 'Paris');
382            
383             This function is equivalent to the OCI obndrn and oexec statements.
384            
385             The OCI obndrn function does not allow empty strings to be bound. As
386             distributed, $ora_bind therefore replaces empty strings with a single
387             space. However, a compilation option allows this substitution to be
388             suppressed, causing &ora_bind() to fail. The output from the
389             &ora_version() function specifies which is the case at your installation.
390            
391             =item * ora_fetch
392            
393             $nfields = &ora_fetch($csr)
394            
395             @data = &ora_fetch($csr [, $trunc])
396            
397             The &ora_fetch() function is used in conjunction with a SQL SELECT
398             statement to retrieve information from a database. This function takes
399             one mandatory parameter, a statement identifier (obtained from
400             &ora_open()).
401            
402             Used in a scalar context, the function returns the number of fields
403             returned by the query but no data is actually fetched. This may be
404             useful in a program which allows a user to enter a statement interactively.
405            
406             Example:
407            
408             $nfields = &ora_fetch($csr);
409            
410             Used in an array context, the value returned is an array containing the
411             data, one element per field. Note that this will not work as expected:
412            
413             @data = &ora_fetch($csr) || die "..."; # WRONG
414            
415             The || forces a scalar context so ora_fetch returns the number of fields.
416            
417             An optional second parameter may be supplied to indicate whether the
418             truncation of a LONG or LONG RAW field is to be permitted (non-zero) or
419             considered an error (zero). If this parameter is not specified, the
420             value of the global variable $ora_trunc is used instead. Truncation of
421             other datatypes is always considered a error.
422            
423             B<DBD:> The optional second parameter to ora_fetch is not supported.
424             A DBI usage error will be generated if a second parameter is supplied.
425             Use the global variable $ora_trunc instead. Also note that the
426             experimental DBI blob_read method can be used to retrieve a long:
427            
428             $csr->blob_read($field, $offset, $len [, \$dest, $destoffset]);
429            
430             If truncation occurs, $ora_errno will be set to 1406. &ora_fetch()
431             will complete successfully if truncation is permitted, otherwise it
432             will fail.
433            
434             &ora_fetch() will fail at the end of the data or if an error occurs. It
435             is possible to distinguish between these cases by testing the value of
436             the variable $ora_errno. This will be zero for end of data, non-zero if
437             an error has occurred.
438            
439             Example:
440            
441             while (($deptno, $dname, $loc) = &ora_fetch($csr))
442             {
443             warn "Truncated!!!" if $ora_errno == 1406;
444             # do something with the data
445             }
446             warn $ora_errstr if $ora_errno;
447            
448             This function is equivalent to the OCI ofetch function.
449            
450             =item * ora_close
451            
452             &ora_close($csr)
453            
454             If an SQL statement is no longer required (for example, all the data
455             selected has been processed, or no more rows are to be inserted) then
456             the statement identifier should be released. This is done by calling
457             the &ora_close() function with the statement identifier as its only
458             parameter.
459            
460             This function is equivalent to the OCI oclose function.
461            
462             B<DBD:> Since $csr is a Perl5 reference the statement/cursor is now
463             automatically closed if $csr is overwritten or goes out of scope.
464            
465            
466             =item * ora_do
467            
468             &ora_do($lda, $statement)
469            
470             Not all SQL statements return data or contain substitution
471             variables. In these cases the &ora_do() function may be
472             used as an alternative to &ora_open() and &ora_close().
473             This function takes two parameters, a login identifier and
474             the statement to be executed.
475            
476             Example:
477            
478             &ora_do($lda, 'drop table employee');
479            
480             This function is roughly equivalent to
481            
482             &ora_close( &ora_open($lda, $statement) )
483            
484             B<DBD:> oraperl v2 used to return the string 'OK' to indicate
485             success with a zero numeric value. The Oraperl emulation now
486             uses the string '0E0' to achieve the same effect since it does
487             not cause any C<-w> warnings when used in a numeric context.
488            
489             =item * ora_logoff
490            
491             &ora_logoff($lda)
492            
493             When the program no longer needs to access a given database, the login
494             identifier should be released using the &ora_logoff() function.
495            
496             This function is equivalent to the OCI ologoff function.
497            
498             B<DBD:> Since $lda is a Perl5 reference the database login identifier
499             is now automatically released if $lda is overwritten or goes out of scope.
500            
501             =back
502            
503             =head2 Ancillary Functions
504            
505             Additional functions available are: &ora_titles(),
506             &ora_lengths(), &ora_types(), &ora_autocommit(),
507             &ora_commit(), &ora_rollback() and &ora_version().
508            
509             The first three are of most use within a program which
510             allows statements to be entered interactively. See, for
511             example, the sample program sql which is supplied with
512             Oraperl and may have been installed at your site.
513            
514             =over 2
515            
516             =item * ora_titles
517            
518             @titles = &ora_titles($csr)
519            
520             A program may determine the field titles of an executed
521             query by calling &ora_titles(). This function takes a
522             single parameter, a statement identifier (obtained from
523             &ora_open()) indicating the query for which the titles are
524             required. The titles are returned as an array of strings,
525             one for each column.
526            
527             Titles are truncated to the length of the field, as reported
528             by the &ora_lengths() function.
529            
530             B<DBD:> oraperl v2.2 actually changed the behaviour such that the
531             titles were not truncated unless an optional second parameter was
532             true. This was not reflected in the oraperl manual. The Oraperl
533             emulation adopts the non truncating behaviour and doesn't support the
534             truncate parameter.
535            
536            
537             =item * ora_lengths
538            
539             @lengths = &ora_lengths($csr)
540            
541             A program may determine the length of each of the fields
542             returned by a query by calling the &ora_lengths() function.
543             This function takes a single parameter, a statement
544             identifier (obtained from &ora_open()) indicating the query
545             for which the lengths are required. The lengths are
546             returned as an array of integers, one for each column.
547            
548            
549             =item * ora_types
550            
551             @types = &ora_types($csr)
552            
553             A program may determine the type of each of the fields returned by a
554             query by calling the &ora_types() function. This function takes a
555             single parameter, a statement identifier (obtained from &ora_open())
556             indicating the query for which the lengths are required. The types are
557             returned as an array of integers, one for each field.
558            
559             These types are defined in your OCI documentation. The correct
560             interpretation for Oracle v6 is given in the file oraperl.ph.
561            
562            
563             =item * ora_autocommit
564            
565             &ora_autocommit($lda, $on_or_off)
566            
567             Autocommit mode (in which each transaction is committed immediately,
568             without waiting for an explicit commit) may be enabled or disabled
569             using &ora_autocommit(). This function takes two parameters, a login
570             identifier (obtained from &ora_login()) and a true/false value
571             indicating whether autocommit is to be enabled (non-zero) or disabled
572             (zero). By default, autocommit is off.
573            
574             Note that autocommit can only be set per login, not per statement. If
575             you need to control autocommit by statement (for example, to allow
576             deletions to be rolled back, but insertions to be committed
577             immediately) you should make multiple calls to &ora_login() and use a
578             separate login identifier for each statement.
579            
580            
581             =item * ora_commit, ora_rollback
582            
583             &ora_commit($lda)
584             &ora_rollback($lda)
585            
586             Modifications to a database may be committed or rolled back using the
587             &ora_commit() and &ora_rollback() functions. These functions take a
588             single parameter, a login identifier obtained from &ora_login().
589            
590             Transactions which have been committed (either explicitly by a call to
591             &ora_commit() or implicitly through the use of &ora_autocommit())
592             cannot be subsequently rolled back.
593            
594             Note that commit and rollback can only be used per login, not per
595             statement. If you need to commit or rollback by statement you should
596             make multiple calls to &ora_login() and use a separate login identifier
597             for each statement.
598            
599            
600             =item * ora_version
601            
602             &ora_version()
603            
604             The &ora_version() function prints the version number and
605             copyright information concerning Oraperl. It also prints
606             the values of various compilation time options. It does not
607             return any value, and should not normally be used in a
608             program.
609            
610             Example:
611            
612             perl -MOraperl -e 'ora_version()'
613            
614             This is Oraperl, version 2, patch level 0.
615            
616             Debugging is available, including the -D flag.
617             Default fetch row cache size is 5.
618             Empty bind values are replaced by a space.
619            
620             Perl is copyright by Larry Wall; type oraperl -v for details.
621             Additions for oraperl: Copyright 1991, 1992, Kevin Stock.
622            
623             Oraperl may be distributed under the same conditions as Perl.
624            
625             This function is the equivalent of Perl's C<-v> flag.
626            
627             B<DBD:> The Oraperl emulation printout is similar but not identical.
628            
629             =back
630            
631             =head1 VARIABLES
632            
633             Six special variables are provided, $ora_cache, $ora_long,
634             $ora_trunc, $ora_errno, $ora_errstr and $ora_verno.
635            
636             =head2 Customisation Variables
637            
638             These variables are used to dictate the behaviour of Oraperl
639             under certain conditions.
640            
641             =over 2
642            
643             =item * $ora_cache
644            
645             The $ora_cache variable determines the default cache size used by the
646             &ora_open() function for SELECT statements if an explicit cache size is
647             not given.
648            
649             It is initialised to the default value reported by &ora_version() but
650             may be set within a program to apply to all subsequent calls to
651             &ora_open(). Cursors which are already open are not affected. As
652             distributed, the default value is five, but may have been altered at
653             your installation.
654            
655             As a special case, assigning zero to $ora_cache resets it to the
656             default value. Attempting to set $ora_cache to a negative value results
657             in a warning.
658            
659            
660             =item * $ora_long
661            
662             Normally, Oraperl interrogates the database to determine the length of
663             each field and allocates buffer space accordingly. This is not
664             possible for fields of type LONG or LONGRAW. To allocate space
665             according to the maximum possible length (65535 bytes) would obviously
666             be extremely wasteful of memory.
667            
668             Therefore, when &ora_open() determines that a field is a LONG type, it
669             allocates the amount of space indicated by the $ora_long variable. This
670             is initially set to 80 (for compatibility with Oracle products) but may
671             be set within a program to whatever size is required.
672            
673             $ora_long is only used when fetching data, not when inserting it.
674            
675            
676             =item * $ora_trunc
677            
678             Since Oraperl cannot determine exactly the maximum length of a LONG
679             field, it is possible that the length indicated by $ora_long is not
680             sufficient to store the data fetched. In such a case, the optional
681             second parameter to &ora_fetch() indicates whether the truncation
682             should be allowed or should provoke an error.
683            
684             If this second parameter is not specified, the value of $ora_trunc is
685             used as a default. This only applies to LONG and LONGRAW data types.
686             Truncation of a field of any other type is always considered an error
687             (principally because it indicates a bug in Oraperl).
688            
689             =back
690            
691             =head2 Status Variables
692            
693             These variables report information about error conditions or about
694             Oraperl itself. They may only be read; a fatal error occurs if a
695             program attempts to change them.
696            
697             =over 2
698            
699             =item * $ora_errno
700            
701             $ora_errno contains the Oracle error code provoked by the last function
702             call.
703            
704             There are two cases of particular interest concerning &ora_fetch(). If
705             a LONG or LONGRAW field is truncated (and truncation is allowed) then
706             &ora_fetch() will complete successfully but $ora_errno will be set to
707             1406 to indicate the truncation. When &ora_fetch() fails, $ora_errno
708             will be set to zero if this was due to the end of data or an error code
709             if it was due to an actual error.
710            
711            
712             =item * $ora_errstr
713            
714             The $ora_errstr variable contains the Oracle error message
715             corresponding to the current value of $ora_errno.
716            
717            
718             =item * $ora_verno
719            
720             The $ora_verno variable contains the version number of Oraperl in the
721             form v.ppp where v is the major version number and ppp is the
722             patchlevel. For example, in Oraperl version 3, patch level 142,
723             $ora_verno would contain the value 3.142 (more or less, allowing for
724             floating point error).
725            
726             =back
727            
728            
729             =head1 SUBSTITUTION VARIABLES
730            
731             Oraperl allows an SQL statement to contain substitution variables.
732             These consist of a colon followed by a number. For example, a program
733             which added records to a telephone list might use the following call to
734             &ora_open():
735            
736             $csr = &ora_open($csr, "insert into telno values(:1, :2)");
737            
738             The two names :1 and :2 are called substitution variables. The
739             function &ora_bind() is used to assign values to these variables. For
740             example, the following statements would add two new people to the
741             list:
742            
743             &ora_bind($csr, "Annette", "472-8836");
744             &ora_bind($csr, "Brian", "937-1823");
745            
746             Note that the substitution variables must be assigned consecutively
747             beginning from 1 for each SQL statement, as &ora_bind() assigns its
748             parameters in this order. Named substitution variables (for example,
749             :NAME, :TELNO) are not permitted.
750            
751             B<DBD:> Substitution variables are now bound as type 1 (VARCHAR2)
752             and not type 5 (STRING) by default. This can alter the behaviour of
753             SQL code which compares a char field with a substitution variable.
754             See the String Comparison section in the Datatypes chapter of the
755             Oracle OCI manual for more details.
756            
757             You can work around this by using DBD::Oracle's ability to specify
758             the Oracle type to be used on a per field basis:
759            
760             $char_attrib = { ora_type => 5 }; # 5 = STRING (ala oraperl2.4)
761             $csr = ora_open($dbh, "select foo from bar where x=:1 and y=:2");
762             $csr->bind_param(1, $value_x, $char_attrib);
763             $csr->bind_param(2, $value_y, $char_attrib);
764             ora_bind($csr); # bind with no parameters since we've done bind_param()'s
765            
766            
767             =head1 DEBUGGING
768            
769             B<DBD:> The Oraperl $ora_debug variable is not supported. However
770             detailed debugging can be enabled at any time by executing
771            
772             $h->debug(2);
773            
774             where $h is either a $lda or a $csr. If debugging is enabled on an
775             $lda then it is automatically passed on to any cursors returned by
776             &ora_open().
777            
778             =head1 EXAMPLE
779            
780             format STDOUT_TOP =
781             Name Phone
782             ==== =====
783             .
784            
785             format STDOUT =
786             @<<<<<<<<<< @>>>>>>>>>>
787             $name, $phone
788             .
789            
790             die "You should use oraperl, not perl\n" unless defined &ora_login;
791             $ora_debug = shift if $ARGV[0] =~ /^\-#/;
792            
793             $lda = &ora_login('t', 'kstock', 'kstock')
794             || die $ora_errstr;
795             $csr = &ora_open($lda, 'select * from telno order by name')
796             || die $ora_errstr;
797            
798             $nfields = &ora_fetch($csr);
799             print "Query will return $nfields fields\n\n";
800            
801             while (($name, $phone) = &ora_fetch($csr)) { write; }
802             warn $ora_errstr if $ora_errno;
803            
804             die "fetch error: $ora_errstr" if $ora_errno;
805            
806             do ora_close($csr) || die "can't close cursor";
807             do ora_logoff($lda) || die "can't log off Oracle";
808            
809            
810             =head1 NOTES
811            
812             In keeping with the philosophy of Perl, there is no pre-defined limit
813             to the number of simultaneous logins or SQL statements which may be
814             active, nor to the number of data fields which may be returned by a
815             query. The only limits are those imposed by the amount of memory
816             available, or by Oracle.
817            
818            
819             =head1 WARNINGS
820            
821             The Oraperl emulation software shares no code with the original
822             oraperl. It is built on top of the new Perl5 DBI and DBD::Oracle
823             modules. These modules are still evolving. (One of the goals of
824             the Oraperl emulation software is to allow useful work to be done
825             with the DBI and DBD::Oracle modules whilst insulating users from
826             the ongoing changes in their interfaces.)
827            
828             It is quite possible, indeed probable, that some differences in
829             behaviour will exist. These are probably confined to error handling.
830            
831             B<All> differences in behaviour which are not documented here should be
832             reported to to dbi-users@perl.org.
833            
834            
835             =head1 SEE ALSO
836            
837             =over 2
838            
839             =item Oracle Documentation
840            
841             SQL Language Reference Manual.
842             Programmer's Guide to the Oracle Call Interfaces.
843            
844             =item Books
845            
846             Programming Perl by Larry Wall and Randal Schwartz.
847             Learning Perl by Randal Schwartz.
848            
849             =item Manual Pages
850            
851             perl(1)
852            
853             =back
854            
855             =head1 AUTHOR
856            
857             Original Oraperl 2.4 code and documentation
858             by Kevin Stock <kstock@auspex.fr>.
859            
860             DBI and Oraperl emulation using DBD::Oracle by Tim Bunce.
861            
862             =head1 MAINTAINER
863            
864             As of DBD::Oracle release 1.17 in February 2006 The Pythian Group, Inc.
865             (L<http://www.pythian.com>) are taking the lead in maintaining DBD::Oracle with
866             my assistance and gratitude.
867            
868             =head1 COPYRIGHT
869            
870             Copyright (c) 1994-2006 Tim Bunce. Ireland.
871            
872             The DBD::Oracle module is free open source software; you can
873             redistribute it and/or modify it under the same terms as Perl 5.
874            
875             =cut
876