&ora_version $lda = &ora_login($database, $name, $password) $csr = &ora_open($lda, $stmt) &ora_bind($csr, $var, ...) &ora_fetch($csr) &ora_close($csr) &ora_do($lda, $stmt) &ora_logoff($lda) $ora_debug $ora_errno $ora_errstr
Any program wishing to access an Oracle database must first log in to Oracle using ora_login. This is called with three parameters, the system ID of the Oracle database to be used, and the Oracle username and password. The return value is a login identifier (an ORACLE Login Data Area).
To specify the SQL statement to be executed, the program must call ora_open or ora_do. These functions take two parameters: a login identifier (obtained from ora_login) and the SQL statement to be executed. The return value from ora_open is a statement identifier (an ORACLE cursor) for use with ora_fetch or ora_bind. The return value from ora_do is the constant string OK. The ora_do function is only suitable for statements which do not return data or contain substitution variables.
If the SQL statement contains substitution variables ora_bind is used to assign values to them. This function takes a statement identifier (obtained from ora_open) as its first parameter, followed by as many parameters as are required by the statement.
To retrieve the data returned from an SQL SELECT statement, the program should make successive calls to ora_fetch. This function takes a single parameter, a statement identifier (obtained from ora_open). In an array context, the return value is an array containing the data, one element per field. In a scalar context, no data is actually fetched and the return value is the number of fields available from the query.
When all the data desired has been returned from an ora_open() call, the statement identifier should be released using the ora_close function. This function returns the string OK. Every ora_open call should have a corresponding ora_close, even if it did not return any data.
When the program no longer needs to access a given database, the login identifier should be released using the ora_logoff function. This function returns the string OK.
All functions return a null string to indicate failure. In the case of ora_fetch, if $ora_errno is zero, this indicates the end of the data.
$csr = &ora_open($csr, "insert into phonelist values(:1, :2)");
The two names :1 and :2 are called substitution variables. The function ora_bind is used to assign values to these variables. For example, the following statements would add two new people to the list:
&ora_bind($csr, "Annette", "472-8836");
&ora_bind($csr, "Brian", "937-1823");
Note that the substitution variables must be assigned consecutively beginning from 1 for each SQL statement, as ora_bind() assigns its parameters in this order. Named substitution variables (for example, :NAME, :TELNO) are not permitted.
format top =
Name Phone
==== =====
.
format STDOUT =
@<<<<<<<<<< @>>>>>>>>>>
$name, $phone
.
die ("You should use oraperl, not perl\n") unless defined &ora_login;
$lda = &ora_login("t", "kstock", "kstock")
|| die $ora_errstr;
$csr = &ora_open($lda, "select * from telno order by name")
|| die $ora_errstr;
$nfields = &ora_fetch($csr);
print "Query will return $nfields fields\n\n";
while (($name, $phone) = &ora_fetch($csr))
{
write;
}
die "fetch error: $ora_errstr" if $ora_errno;
do ora_close($csr) || die "can't close cursor";
do ora_logoff($lda) || die "can't log off Oracle";
To determine whether debugging is available, you could use something like this:
defined($ora_debug) && print "debugging available\n"; defined($ora_debug) || print "debugging not available\n";
At present, only flags 8 (program execution), 32 (string/numeric conversion) and 128 (use of malloc and free) are supported.
Debugging option 32 only reports internal string/numeric translations, not those performed on the data retrieved from the database.
Oracle Documentation: SQL Language Reference Manual Programmer's Guide to the Oracle Call Interfaces
Perl documentation: Programming Perl by Larry Wall and Randall Schwartz perl(1)