Oraperl Manual

Oraperl is an extension to Perl which allows access to Oracle databases. In order to make the oraperl function definitions available in perl5 you need to arrange to 'use' the Oraperl.pm module in each file or package which uses them. You can do this by simply adding use Oraperl; in each file or package.

Put a following line in your each script:

eval 'use Oraperl; 1;' || die $@ if $] >= 5;

There are following important perl functions to understand to work with oraperl module.

• &ora_login() - is used to login to an oralce database.

• &ora_open() - is used to execute any SQL command.

• &ora_bind() - is used to assign actual values to substitution variables.

• &ora_fetch() - is used to used in conjunction with a SQL SELECT statement to retrieve information from a database.

• &ora_close() - is used to close oralce connection.

• &ora_do() - is used to as an alternative to &ora_open() and &ora_close().

• &ora_logoff - is used to log out of oracle.

There are other ancillary functions which we will discuss in this session.

Database Connection using ora_login():

In order to access information held within an Oracle database, a program must first log in to it by calling the ora_login() function. This function is called with three parameters, the system ID of the Oracle database to be used, and the Oracle username and password. The value returned is a login identifier actually an Oracle Login Data Area.

Syntax:

$lda = &ora_login($system_id, $username, $password)

Here if $system_id is empty then this call will use ORALDE_SID or TASK_TWO envrionment variables to identify an oracle sytem.

$lda is Login Data Area and will be used in all subsequent calls.

Example:

The following example will connect a database called tutorialspoint with username as guest and password as guest123

my $system_id = "tutorialspoint";
my $username = "guest";
my $password = "guest123";
$lda = &ora_login($system_id, $username, $password)

Execute SQL Command using ora_open():

The function ora_open takes at least two parameters: a login identifier obtained from &ora_login() and the SQL statement to be executed. An optional third parameter specifies the size of the row cache to be used for a SELECT statement.

If the row cache size is not specified, a default size is used. As distributed, the default is five rows, but this may have been changed at your installation. Check $ora_cache variable.

Syntax:

$csr = &ora_open($lda, $statement [, $cache]);

The value returned from ora_open() is a statement identifier actually an Oracle curosr represented as $csr.

Example:

Try out following example

$csr = &ora_open($lda, 'select ename, sal from emp order by ename', 10);
$csr = &ora_open($lda, 'insert into dept values(:1, :2, :3)');

For the second call variable values will be subsitituted by ora_bind() function.

Bind Variable values using ora_bind():

If If an SQL statement contains substitution variables ( see above example with values :1, :2 and :3 ) then actual values are supplied using ora_bind function. This takes a statement identifier obtained from ora_open() call as its first parameter, followed by as many parameters as are required by the statement.

Syntax:

&ora_bind($csr, $var, ...);

Example:

Try out following example to supply values to the above INSERT statement

&ora_bind($csr, 50, 'management', 'Paris');

Fetching values from database using ora_fetch():

The ora_fetch function is used in conjunction with a SQL SELECT statement to retrieve information from a database. This function takes one mandatory parameter, a statement identifier obtained from ora_open().

In scalar context the function returns the number of fields returned by the query but no data is actually fetched.

In array context the function returns an array containing the data, one element per field.

Syntax:

# Calling in scalar context
$nfields = &ora_fetch($csr);
# Calling in array conext
@data = &ora_fetch($csr);

Each call to this function will returna single row. To fetch multiple rows you would need to call this function multiple timepossible inside a while loop.

The function ora_fetch() will fail at the end of the data or if an error occurs. It is possible to distinguish between these cases by testing the value of the variable $ora_errno. This will be zero for end of data, non-zero if an error has occurred.

Example:

Try out following example to fetch values from database.

 while (($deptno, $dname, $loc) = &ora_fetch($csr))
 {
   warn "Truncated!!!" if $ora_errno == 1406;
   # do something with the data
 }
 warn $ora_errstr if $ora_errno;

Closing database connection using ora_close:

If an SQL statement is no longer required then the statement identifier should be released. This is done by calling the &ora_close() function with the statement identifier as its only parameter.

Syntax:

&ora_close($csr);

Using ora_do() function:

All SQL statements do not return data or contain substitution variables. So in such cases ora_do function may be used as an alternative to &ora_open() and &ora_close().

This function takes two parameters, a login identifier and the statement to be executed.

Syntax:

&ora_do($lda, $statement)

Example:

The follwoing two examples will do same job:

ora_do:

&ora_do($lda, 'drop table employee');

ora_open and ora_close

&ora_close( &ora_open($lda, 'drop table employee') );

Ancillary Functions:

ora_titles() Function:

A program may determine the field titles of an executed query by calling &ora_titles(). This function takes a single parameter, a statement identifier obtained from &ora_open() indicating the query for which the titles are required. The titles are returned as an array of strings, one for each column.

Syntax:

@titles = &ora_titles($csr);

ora_lengths() Function:

A program may determine the length of each of the fields returned by a query by calling the &ora_lengths() function. This function takes a single parameter, a statement identifier obtained from &ora_open() indicating the query for which the lengths are required. The lengths are returned as an array of integers, one for each column.

Syntax:

@types = &ora_types($csr);

ora_autocommit() Function:

Autocommit mode in which each transaction is committed immediately, without waiting for an explicit commit may be enabled or disabled using &ora_autocommit().

Syntax:

&ora_autocommit($lda, $on_or_off);

This function takes two parameters, a login identifier obtained from &ora_login() and a true/false value indicating whether autocommit is to be enabled (non-zero) or disabled (zero). By default, autocommit is off.

ora_commit, ora_rollback Functions:

Modifications to a database may be committed or rolled back using the &ora_commit() and &ora_rollback() functions. These functions take a single parameter, a login identifier obtained from &ora_login().

Syntax:

&ora_commit($lda);
&ora_rollback($lda);

ora_version() Function:

The &ora_version() function prints the version number and copyright information concerning Oraperl. It also prints the values of various compilation time options.

Syntax:

&ora_version();

This function is the equivalent of Perl's -v flag.

Oraperl Variables:

Six special variables are provided, $ora_cache, $ora_long, $ora_trunc, $ora_errno, $ora_errstr and $ora_verno.

• $ora_cache: The $ora_cache variable determines the default cache size used by the &ora_open() function for SELECT statements if an explicit cache size is not given.

• $ora_long: Oraperl interrogates the database to determine the length of each field and allocates buffer space accordingly. This is not possible for fields of type LONG or LONGRAW. To allocate space according to the maximum possible length (65535 bytes) would obviously be extremely wasteful of memory. Therefore, when &ora_open() determines that a field is a LONG type, it allocates the amount of space indicated by the $ora_long variable.

• $ora_trunc: Since Oraperl cannot determine exactly the maximum length of a LONG field, it is possible that the length indicated by $ora_long is not sufficient to store the data fetched. In such a case, the optional second parameter to &ora_fetch() indicates whether the truncation should be allowed or should provoke an error.

  If this second parameter is not specified, the value of $ora_trunc is used as a default. This only applies to LONG and LONGRAW data types.

• $ora_errno : contains the Oracle error code provoked by the last function call.

• $ora_errstr: contains the Oracle error message corresponding to the current value of $ora_errno.

• $ora_verno: contains the version number of Oraperl in the form v.ppp where v is the major version number and ppp is the patchlevel.