NCCI Perl Documentation OraConnection(3)

NAME

OraConnection - Connection to a database

SYNOPSIS

# Access to modules (see the NOTES section for setup)

BEGIN {

...

push (@INC, "../sys/csbase/lib");

...

$ENV{'ORACLE_HOME'} = "../sys/oracle_home";

}

use OraConnection;

$db = new OraConnection;

$db->connect ($databaseName, $username, $password,

$optionalOptimizerGoal, $optionalDefaultDateFormat);

$tableNames = $db->tableNames ();

$viewNames = $db->viewNames ();

$tableOrViewNames = $db->tableOrViewNames ();

$dbLinkNames = $db->dbLinkNames ();

$sequenceNames = $db->sequenceNames ();

$colNames = $db->columnNames ("MY_TABLE");

$quotedValue = $db->quote ("Don't shoot the piano player");

# Repetitive execution

$insertStmt = "INSERT INTO TBL (who, when) VALUES (" .

db->quote ("Clint") . ", SYSDATE)";

# First execution, fetching statement handle for other exec's

$sth = $db->execMulti ($insertStmt);

# Second execution

$db->reExec ($sth);

# You MUST call finish to free the statement handle

$db->finish ($sth);

# One-time execution - no finish() required or allowed

$db->exec ($insertStmt);

$query = "SELECT height, weight from INFO " .

"WHERE name=" . db->quote ("Clint");

# Fetch EXACTLY one result row. Fails if 0 or

# multiple rows

$resultRow = $db->fetchSingle ($query);

($height, $weight) = @$resultRow;

19/Mar/01 Last change: perl 5.004, patch 01 1

NCCI Perl Documentation OraConnection(3)

# Direct fetch of height

$height = $db->get ("INFO", "height", "name", "Clint");

# Fetch all result rows.

($resultRows, $rowCount) = $db->fetchAll ($query);

foreach $resultRow (@$resultRows) {

($height, $weight) = @$resultRow;

}

# Count of all rows and rows matching various

# column values

$rowCount = $db->rowCount ("INFO");

$rowCount = $db->matchingRowCount ("INFO",

"height", "72", "weight", "195");

# Sequence number values

$sequenceVal = $db->currval ($sequenceName);

$sequenceVal = $db->nextval ($sequenceName);

$minValue = $db->minValue ($sequenceName);

$maxValue = $db->maxValue ($sequenceName);

$incrementBy = $db->incrementBy ($sequenceName);

# Not required - done automatically if $db

# goes "out of scope"

$db->disconnect ();

DESCRIPTION

This Perl module encapsulates a connection to an Oracle

database. It provides methods to connect and disconnect

from the database, as well as execute common queries.

Error handling is provided in several flavors: text and html

output are provided to either stdout or a provided file

handle.

The module uses the DBI Database Independent Perl5 interface

class.

ERRORS

Problems encountered by routines in this class are divided

up into these flavors:

Fatal Error

Something really bad happened when dealing with the

underlying database, which make it unlikely that the

calling program will want to continue. Examples are a

failure to connect to the database.

SQL Error

An error was encountered preparing or executing an SQL

19/Mar/01 Last change: perl 5.004, patch 01 2

NCCI Perl Documentation OraConnection(3)

statement.

Warning

Nothing too serious.

System Error

The caller has made some error in using the methods of

this class. For example, trying to execute an SQL

statement prior to connecting to the database.

This OraConnection module provides a default behaviour in

each of the above situations. The behaviour can be

overridden for an OraConnection instance by providing a

method which which (re-)implements the databaseProblem()

method.

Case Sensitivity Issues

Table and column names are, by default, case insensitive in

Oracle. However, there are some situations where mixed case

names can creep into an Oracle database: when importing an

Access database or when column names are reserved words.

In most situations, specifying a table or column name is

folded to upper case. However, if you need to specify a

mixed case name, you will need to enclose the table or

column name in double quotes. For example:

# Fetch column MYCOLUMN from table MYTABLE

# where column COLOR = 'red'.

$dbc->get ("MyTable", "MyColumn", "Color" => "red");

# Fetch column MYCOLUMN from table MyTable

# where column Color = 'red'.

$dbc->get ("\"MyTable\"", "MyColumn", "\"Color\"" => "red");

RDB Support

Access to RDB databases is provided through the Oracle

Translucent Gateway. If you have such a gateway set up,

OraConnection is designed to work "seamlessly" using a

combination of the functionality provided by the gateway and

direct access to underlying RDB tables.

You will need to call setDatabaseFlavor ("RDB") after the

connect () is issued and prior to any work on the database.

One issue is the mapping of data types:

19/Mar/01 Last change: perl 5.004, patch 01 3

NCCI Perl Documentation OraConnection(3)

RDB native_

Type data_

Code type Oracle Type data_type

7 SMALLINT NUMBER (5, 0) NUMBER

8 INTEGER NUMBER (10, 0) NUMBER

10 REAL FLOAT (24) FLOAT

14 CHAR CHAR (fld_len) CHAR

35 DATE VMS DATE DATE

37 VARCHAR VARCHAR2 (fld_len) VARCHAR2

?? TINYINT NUMBER (3, 0) NUMBER

?? BIGINT NUMBER (20, 0) NUMBER

?? DOUBLE PRECISION FLOAT (53) FLOAT

Support for LONG and LONG RAW

OraConnection has basic handling for the LONG and LONG RAW

Oracle field types. You can insert a record with a single

LONG or LONG RAW column value using the insertLong() and

insertLongRaw() methods. You can fetch a LONG or LONG RAW

value using the get() method.

NOTE: Handling of LONG and LONG RAW requires at least

version 1.06 of the DBI module and 0.61 of DBD::Oracle. NO

CHECK IS MADE that these version are being used and your

code may well HANG on earlier versions of the DBI and

DBD::Oracle.

FUNCTIONS

*** SETUP

OraConnection->new();

Constructor. No arguments are accepted.

databaseProblem ($problemType, $problemDescription, $errcode)

Default problem handler.

$problemType - One of "fatalError", "sqlError",

"warning", "systemError"

$problemDescription - Text describing the problem. This

message is either plain text or html formatted text.

$errcode - The integer Oracle error code.

overrideDatabaseProblemHandler ($handlerClass)

Provide/declare a class which implements the

databaseProblem method. If $handlerClass is undef, the

default handler in this module is used.

19/Mar/01 Last change: perl 5.004, patch 01 4

NCCI Perl Documentation OraConnection(3)

databaseProblemHandler ()

Fetch the class which currently implements the

databaseProblem method. If this class is the handler

(i.e. the default handler is in use), we will return

undef.

setMsgFormat ($format)

Select the format for error messages. Currently MUST be

one of 'html' or 'text'.

*** DATABASE CONNECTION

connect ($databaseName, $username, $password, $goal, $dformat)

Connect to the Oracle database and return the Oracle db

handle.

The $databaseName is the "TNS" name of the database on

your local system. The $username and $password specify

the login context.

The optional $goal and optional $dformat specify the

OPTIMIZER_GOAL and default date format to use for this

connection. See the setOptimizerGoal() and

setDefaultDateFormat() methods below.

If either the prepare or execute fails, perform fatal

processing.

Returns the underlying database handle of the Perl DBI

class.

setDatabaseFlavor ($flavor)

Specify the flavor of the database. The choices are

"Oracle" or "RDB". "Oracle" is assumed, if "" or undef

is passed.

setLinkName ($linkName)

Specify the name of a link to add to table names for

composed queries. For example, to make queries of the

form "SELECT * from tblname@linkname" you would invoke

->setLinkName("linkname") after the connect().

The "@" is optional to this method. Note that the

linkName() method will NOT return the leading "@",

regardless of whether it was specified on the call to

setLinkName().

To clear a linkname, call ->setLinkName("");

setSchema ($schemaName)

Specify the name of a schema to add to table names for

composed queries, overriding the default schema for the

19/Mar/01 Last change: perl 5.004, patch 01 5

NCCI Perl Documentation OraConnection(3)

username on the connect() call. For example, to make

queries of the form "SELECT * from myschema.tblname" you

would invoke ->setSchema("myschema") after the

connect().

To clear a linkname, call ->setSchema("");

setOptimizerGoal ($goal)

Set this connection's OPTIMIZER_GOAL. The supplied $goal

must be one of the known optimizer goals listed below

(case insensitive). The setting of OPTIMIZER_GOAL

specifies the optimization mode to use when handling

database queries:

CHOOSE Tells the optimizer to search the data

dictionary views for data on at least

one related table (referenced in the SQL

statement). If data exists, the optimizer

will optimize the statement according to

the cost-based approach. If no data exists

for any tables being referenced, the

optimizer will use rule-based optimization.

ALL_ROWS Chooses cost-based optimization with the

goal of best throughput.

FIRST_ROWS Chooses cost-based optimization with the

goal of best response time.

RULE Chooses rule-based optimization regardless

of the presence of data in the data

dictionary views related to the tables

being referenced.

On success, this method returns 1.

If an invalid goal is supplied,

no action is taken and this method return undef.

setDefaultDateFormat ($dformat)

Set this connection's default date format. The supplied

$dformat must be a valid Oracle date format. Some

examples of valid date formats (using the date Saturday

October 31, 1998):

Format Example output

19/Mar/01 Last change: perl 5.004, patch 01 6

NCCI Perl Documentation OraConnection(3)

"Day Month DD, YYYY" Saturday October 31, 1998

"Dy Mon DDTH, YY" Sat Oct 31st, 98

"DD mon, Year" 31 oct, Nineteen-Ninety-Eight

"CC YYYY Month Day" 20 1998 October Saturday

"YY,D,DD,DDD" 98,7,31,304

"YYYYSP" ONE THOUSAND NINE HUNDRED NINETY EIGHT

"YYYY: Q" 1998: 4

If an invalid date format is supplied, perform fatal

processing. This method always returns 1.

disconnect ()

Break the connection to Oracle.

Even though a connection to Oracle MUST be disconnected,

this user is typically not required to execute this

method. Since the DESTROY method for this class calls

disconnect(), the user can effect a disconnect by

destroying the object or letting it go out of scope.

connected ()

Return 1 if connected, 0 or undef otherwise.

dataSource ()

Return the DBI 'datasource'. For Oracle database

connections, this is a string with the form

"dbi:Oracle:" . databaseName().

dbh ()

Return the DBI database handle. This is useful for

issuing requests directly to the DBI layer.

driverName ()

Return the DBI 'drivername'. For Oracle database

connections, this is the string "Oracle".

databaseName ()

Return the name of the database - the first argument on

the call to connect().

username ()

Return the username - the second argument on the call to

connect().

password ()

Return the password - the third argument on the call to

connect().

databaseFlavor ()

Return the database flavor specified on a call to

setDatabaseFlavor().

19/Mar/01 Last change: perl 5.004, patch 01 7

NCCI Perl Documentation OraConnection(3)

schemaName ()

Return any currently specified schema name provided on a

call to setSchema().

method will NOT return the leading "@", regardless of whether it

was specified on the call to setLinkName().

linkName () Return any currently specified linkname. This

optimizerGoal ()

Return an upper-caser version of the optimizer goal as

specified in the optional argument to connect() or in a

call to setOptimizerGoal(). If the optimizer goal was

not specified on the connect() and no setOptimizerGoal()

call has been made, this method returns undef.

defaultDateFormat ()

Return the default date format as specified in the

optional argument to connect() or in a call to

setDefaultDateFormat(). If the default date format was

not specified on the connect() and no

setDefaultDateFormat() call has been made, this method

returns undef.

quote ($quotable)

Return the 'quote' of the given string in the context of

the particular database connection. This routine is

prefereable to trying to quote values yourself, because

different database connections have different syntax(es)

for quoting values. For example, quoting the value

don't might yield 'don''t' in one variation of SQL

and "don't" in another, and $don't in another.

*** DATA DICTIONARY METHODS

tableNames ()

Returns a reference to a list of the names of the tables

in this database. The list is sorted alphabetically by

table name. NOTE: the result must be dereferenced - see

the example below.

viewNames ()

Returns a reference to a list of the names of the views

in this database. The list is sorted alphabetically by

view name. NOTE: the result must be dereferenced - see

the example below.

tableOrViewNames ()

Returns a reference to a list of the names of the tables

and views in this database. The list is sorted

alphabetically by the table or view name. NOTE: the

result must be dereferenced - see the example below.

19/Mar/01 Last change: perl 5.004, patch 01 8

NCCI Perl Documentation OraConnection(3)

dbLinkNames ()

Returns a reference to a list of the names of the DB

links in this database. The list is sorted

alphabetically by DB link name. NOTE: the result must

be dereferenced - see the example below.

sequenceNames ()

Returns a reference to a list of the names of the

sequences in this database. The list is sorted

alphabetically by sequence name. NOTE: the result must

be dereferenced - see the example below.

synonymNames ()

Returns a reference to a list of the names of the

synonyms in this database. The list is sorted

alphabetically by synonym name. NOTE: the result must

be dereferenced - see the example below.

comment ($tableName, $columnName)

Return the value of the comment attribute in the

database for a given table or column in a table. If the

column is not specified, the value of the comment for

the table is returned. The table and column names are

case insensitive, unless they are enclosed in double

quotes. Returns undef if the table name or column name

(if specified in the call) does not exist. Returns ""

if the comment attribute is not set.

setComment ($comment, $tableName, $columnName)

Set the value of the comment attribute in the database

for a given table or column in a table. If the column

is not specified, the value of the comment for the table

is set.

tabtype ($tableOrViewName)

Return the table type of a given named table or view.

Examples of table types are "TABLE" and "VIEW". The

table/view name is case insensitive, unless it is

enclosed in double quotes. If the table or view does

not exist, an SQL error is triggered.

tableAttribute ($tableName, $attribute)

Returns a given named attribute of a named table. The

table name and attribute are case insensitive. If the

table or attribute do not exist, an SQL error is

triggered. This is a low level routine for accessing

attributes - refer to the TABLE ATTRIBUTES METHODS

section below for specific methods to fetch table

attributes.

columnNames ($tableName)

Return a reference to a list of column names in a given

19/Mar/01 Last change: perl 5.004, patch 01 9

NCCI Perl Documentation OraConnection(3)

table. The table name is case insensitive, unless it is

enclosed in double quotes. If the table does not exist,

an SQL error is triggered. The returned list is not

sorted in any particular order. NOTE: the result must

be dereferenced - see the example below.

nullable ($tableName, $columnName)

Return 1 or 0 if the given named column in the given

named table is nullable or not. The table name and

column names are case insensitive. If the table or

column do not exist, an SQL error is triggered.

columnAttribute ($tableName, $columnName, $attribute)

Returns a given named attribute of a named column in a

named table. The table name, column name, and attrubute

are case insensitive. If the table, column, or

attribute do not exist, an SQL error is triggered. This

is a low level routine for accessing attributes - refer

to the COLUMN ATTRIBUTES METHODS section below for

specific methods to fetch column attributes.

*** KEY AND INDEX METHODS

primaryKeyNames ($tableName)

Return a reference to a list of primary key names in a

given table. The table name is case insensitive, unless

it is enclosed in double quotes. If the table does not

exist, an SQL error is triggered.

foreignKeyNames ($tableName)

Return a reference to a list of foreign key names in a

given table. The table name is case insensitive, unless

it is enclosed in double quotes. If the table does not

exist, an SQL error is triggered.

indexNames ($tableName)

Return a reference to a list of index names for indices

in a given table. The table name is case insensitive,

unless it is enclosed in double quotes. If the table

does not exist, an SQL error is triggered.

primaryKeyPosition ($tableName, $columnName)

Position of the given column in the primary key of the

given table. Returns undef if the column is not part of

the primary key of the table. Otherwise, returns the

1-based index of its position in the primary key.

primaryKeyColumnNames ($primaryKeyName)

Given the name of a primary key (see primaryKeyNames),

return a reference to a list of column names which make

up the primary key, in order of their position. If the

primary key does not exist, an SQL error is triggered.

19/Mar/01 Last change: perl 5.004, patch 01 10

NCCI Perl Documentation OraConnection(3)

foreignKeyColumnNames ($foreignKeyName)

Given the name of a foreign key (see foreignKeyNames),

return a reference to a list of column names which make

up the foreign key, in order of their position. If the

foreign key does not exist, an SQL error is triggered.

foreignKeyTarget ($foreignKeyName)

Given the name of a foreign key (see foreignKeyNames),

return the target (R_CONSTRAINT_NAME) of the foreign

key. This is typically the name of a primary key. If

the foreign key does not exist, an SQL error is

triggered.

indexColumnNames ($indexName)

Given the name of a index (see indexNames), return a

reference to a list of column names which make up the

index, in order of their position. If the index does

not exist, an SQL error is triggered.

indexUnique ($indexName)

Given the name of a index (see indexNames), return the

UNIQUENESS setting for the index. This is typically the

string "UNIQUE" or "NONUNIQUE". If the index does not

exist, an SQL error is triggered.

constraintCheckText ($tableName, $columnName)

Somewhat post-processed text of the constraint on the

column. The constraint IS NOT NULL is not returned (use

the nullable() method for this information). This is

primarily used for the FIELD IN ('A', 'B') type of

constraints.

*** TABLE ATTRIBUTE METHODS

The following methods return the given attribute of a

given named table. The table name is case insensitive,

unless it is enclosed in double quotes. If the table

does not exist, an SQL error is triggered.

avg_row_len ($tableName)

avg_space ($tableName)

backed_up ($tableName)

blocks ($tableName)

cache ($tableName)

chain_cnt ($tableName)

cluster_name ($tableName)

19/Mar/01 Last change: perl 5.004, patch 01 11

NCCI Perl Documentation OraConnection(3)

degree ($tableName)

empty_blocks ($tableName)

freelists ($tableName)

freelist_groups ($tableName)

ini_trans ($tableName)

initial_extent ($tableName)

instances ($tableName)

max_extents ($tableName)

max_trans ($tableName)

min_extents ($tableName)

next_extent ($tableName)

num_rows ($tableName)

pct_free ($tableName)

pct_used ($tableName)

pct_increase ($tableName)

table_lock ($tableName)

tablespace_name ($tableName)

*** COLUMN ATTRIBUTE METHODS

The following methods return the given attribute of a

given named column in given named table. The table name

and column name are case insensitive. If the table or

column do not exist, an SQL error is triggered.

column_id ($tableName, $columnName)

data_default ($tableName, $columnName)

data_length ($tableName, $columnName)

data_precision ($tableName, $columnName)

data_scale ($tableName, $columnName)

data_type ($tableName, $columnName)

For databases accessed through a gateway (such as RDB),

19/Mar/01 Last change: perl 5.004, patch 01 12

NCCI Perl Documentation OraConnection(3)

this method returns the Oracle type corresponding to the

underlying data type of the foreign database. See

data_type_native() to obtain the underlying native data

type.

default_length ($tableName, $columnName)

density ($tableName, $columnName)

high_value ($tableName, $columnName)

last_analyzed ($tableName, $columnName)

low_value ($tableName, $columnName)

num_buckets ($tableName, $columnName)

num_distinct ($tableName, $columnName)

num_nulls ($tableName, $columnName)

sample_size ($tableName, $columnName)

data_type_native ($tableName, $columnName)

For databases accessed through a gateway (such as RDB),

this method returns the native data type of the column

in the underlying foreign database. See data_type() to

obtain the corresponding Oracle data type.

*** MULTI-EXECUTION QUERIES

execMulti ($query)

Take an SQL query, prepare it, execute it, and return

its statement handle.

The returned statement handle can be re-executed without

another prepare() call, by calling reExec().

You MUST (eventually) call finish() on the returned

statement handle.

If either the prepare or execute fails, perform fatal

processing.

reExec ($sth)

Re-execute a statement prepared earlier using the

statement handle returned by an early exec() invocation.

Returns the statement handle for uniformity with exec().

If the execute fails, perform fatal processing.

19/Mar/01 Last change: perl 5.004, patch 01 13

NCCI Perl Documentation OraConnection(3)

finish ($sth)

Finish the statement and free the associated resources.

If the finish fails, perform fatal processing.

*** SINGLE-EXECUTION QUERIES

exec ($statement)

Once-only execution of a statement. Useful for SQL

INSERT, UPDATE, etc type of queries. On failure,

perform fatal processing.

insertLong ($statement, $marker, $bigValue)

insertLongRaw ($statement, $marker, $bigValue)

Insert a row with a single LONG or LONG RAW field value.

A field of type LONG is available in Oracle 7 and later

for handling up to 2 gigabytes of character data. LONG

RAW is used for up to 2 gigabytes of binary data.

The statement must be of the form:

INSERT INTO <table> (field1, field2, ..., longField)

values ('val1', 'val2', ..., :MARKER)

where the field of type LONG or LONG RAW is named last

and :MARKER is a token which MUST begin with a colon

character.

The call to insertLong() or insertLongRaw() inserts a

single record. The value for the LONG or LONG RAW field

is supplied as a single perl string.

For example:

$stmt = "INSERT INTO mytable (key, longField) " .

"values ('a', :MyMarker)";

$dbc->insertLong ($stmt, ":MyMarker",

$reallyReallyLongValue);

These insertLong() and insertLongRaw() methods are

currently the only way to set a LONG or LONG RAW field.

Large objects in an RDB database cannot be set in this

manner.

NOTE: Use of these methods requires at least version

1.06 of the DBI module and 0.61 of DBD::Oracle. NO CHECK

IS MADE that these version are being used and these

methods may well HANG on earlier versions of the DBI and

DBD::Oracle.

19/Mar/01 Last change: perl 5.004, patch 01 14

NCCI Perl Documentation OraConnection(3)

execStoredProcedure ($procedureInvocation)

Convenience routine to execute a stored procedure call

once. Tacks on the obligatory BEGIN and END around the

procedure call itself. For example, you pass in "Foo

(123, '20-Dec-98')". In this case, the actual executed

SQL would be "BEGIN; Foo (123, '20-Dec-98'); END;". On

failure, perform fatal processing.

fetchAll ($query)

Once-only execution of a query.

Returns the entire result set of a query as an array

reference. The array contains zero or more array

references, each of which represents one row of the

result. Each result array/row holds the field values.

For example, if the resulting rows of "SELECT name,

consort FROM KINGS" is:

henry, joan

henry, marie-gallant

the resulting anonymous hash would be:

[ [ "henry", "joan" ], [ "henry", "marie-gallant" ] ]

As a convenience, this routine returns the count of

records/rows returned.

For example, the following will fetch a ref to a list

(and count) of the tables in the database.

my ($rows, $rowCount) = $connection->fetchAll("SELECT *

FROM user_tables");

This method is potentially a huge memory hog. "Hello

Sun? Yea, we need summore of those reeeely big servers"

fetchRange ($query, $firstRow, $lastRow)

Similar to fetchAll(), but returns only those rows

within the range specified.

It is a way to guard against the potentially unlimited

data returned by fetchAll(). It is particularly well

suited to the Web style of fetching a "page" of data,

then having "NEXT" and "PREV" page links.

The $firstRow and $lastRow are the 0-based indices of

the first and last result row to return. If $firstRow

is undef or less than 0, it assumed to be 0. If

$lastRow is undef, all rows from $firstRow to the end

19/Mar/01 Last change: perl 5.004, patch 01 15

NCCI Perl Documentation OraConnection(3)

are returned.

This routine returns the same array reference as the

first returned element of fetchAll(), except that the

returned arrayref has the first $firstRow elements

missing, and never has more than $lastRow-$firstRow+1

elements.

This routine does not return the total $rowCount (as

does fetchAll()). Use the rowCountOfQuery() method to

get the total row count, if needed.

Note that while this routine avoids wasting space on the

client side, the speed of execution of the query on the

database server is (apparantly) not improved by using

fetchRange() rather than fetchAll(). To improve the

speed of query execution, you can add a WHERE clause of

the form

"rownum <= " . $dbcontext->quote ($lastRow+1)

(Note that the SQL rownum is 1-based and the

fetchRange() method takes 0-based arguments.)

This technique is valid (according to Chandra Kala) even

in the presence of ORDER BY clauses, since Oracle

"extract the rownum <= WHERE clause and applies it after

any ORDER BY clauses". Of course, adding an ORDER BY

clause may, in itself, slow down the query.

fetchSingle ($query [, $noComplainMissing [, $noComplainMulti]])

Once-only execution of a query which should yield

exactly one result row.

Returns a reference to an anonymous array which holds

the fields values of the single result row.

If you don't get exactly one row, returns undef.

If there are no matching rows, a warning is issued,

unless noComplainMissing is defined.

If there are multiple matching rows, a warning is

issued, unless noComplainMulti is defined.

rowCount ($tableName)

Return the number of rows in a given table.

matchingRowCount ($tableName, $fld1, $val1, $fld2, $val2, ...)

Return the number of rows in a given table where the

given field(s) $fld1, $fld2, ... match the given

value(s) $val1, $val2, ....

19/Mar/01 Last change: perl 5.004, patch 01 16

NCCI Perl Documentation OraConnection(3)

Returns the count of matching rows.

rowCountOfQuery ($query)

Returns the number of which will match a given SELECT

query. The $query MUST begin with the form:

"SELECT field1 {, fieldN} FROM ..."

Note that the "SELECT" and "FROM" MUST BE ALL CAPITALS.

Also note that, if the second "FROM" appears later in

the query (eg. in a subquery subordinated in a WHERE

clause), then this routine will NOT work properly - it

will mangle the query and cause an SQL parse error.

This method basically issues a query of the form:

"SELECT count(*) FROM ..."

Returns the count of matching rows.

currval ($sequenceName)

Returns the current value of a given named sequence

number in the database.

nextval ($sequenceName)

Return the next value of a given named sequence number

in the database, AND INCREMENT THE SEQUENCE.

minValue ($sequenceName)

Return the defined minimum value for the sequence.

maxValue ($sequenceName)

Return the defined maximum value for the sequence.

incrementBy ($sequenceName)

Return the increment_by setting for the sequence.

table_owner_of_synonym ($synonymName)

Return the table_owner of a given named synonym number

in the database.

table_name_of_synonym ($synonymName)

Return the table_name of a given named synonym number in

the database.

get ($tableName, $resultField, $fld1, $val1, $fld2, $val2, ...)

Fetch the value of the column named $resultField from a

record whose field(s) $fld1, $fld2, ... match the given

value(s) $val1, $val2, ....

No check is made as to how many matching result rows

exist. If there is more than one result row, you get an

19/Mar/01 Last change: perl 5.004, patch 01 17

NCCI Perl Documentation OraConnection(3)

result field value from an arbitrary one of the matching

rows. Use of ROWID will avoid this problem. For

example:

$result = $dbc->get ("MY_TABLE", "RESULT", "ROWID", $theRowID);

The arguments after $resultField are fieldName/value

pairs which must be matched by the column values. A

value of undef or "IS_NULL" for a value will generate an

"is NULL" check in the corresponding column.

The resultField argument can name a field of data type

LONG or LONG RAW. in this case, the entire field value

is returned in a single perl string object. This get()

method is currently the only way to read LONG and LONG

RAW fields from an Oracle database. Large objects cannot

be accessed from an RDB database in this manner.

$maxRowCount, $fld1, $fld2, ...)

dumpTable ($filename, $tableName, $optionalWhereClause,

Siphon a given table from a given (other) OraConnect

object into a given table in this database, mapping the

column names. NOTE: all tab characters are removed from

field values!

*** ERROR HANDLING

warn ($arg1, $arg2)

Issue a database warning. Several call formats are

supported:

warn "Can't do what I want"; # Issue complaint

with DBI:: data

warn $dbh, "Can't do what I want"; # Issue complaint

with $dbh-> data

fatal ($arg1, $arg2)

Fatal Error (see the ERRORS section). Same arguments as

warn().

sqlError ($arg1, $arg2)

SQL Error (see the ERRORS section). Same arguments as

warn().

systemError ($arg1, $arg2)

System Error (see the ERRORS section). Same arguments

as warn().

WARNING

The fetchAll() method can potentially return a huge amount

of data.

19/Mar/01 Last change: perl 5.004, patch 01 18

NCCI Perl Documentation OraConnection(3)

NOTES

This module has not yet been set up for "installation" in

your local Perl. We currently use a set of rather klugy

conventions for access to this module, until the install

procedures are developed:

- The source code lives on gsun181 in the directory

/Volumes/app/cs/csbase/lib. This disk is automounted on

other hosts, so you should have NFS access to the source.

- By convention, a project maintains perl scripts in

subdirectories of some top-level directory, which we will

call $TOP.

- By convention, projects establish a $TOP/sys directory

which contains a symbolic link from csbase to

/Volumes/app/cs/csbase.

- To get access to the modules, you typically include a

BEGIN of the form:

BEGIN {

...

push (@INC, "../sys/csbase/lib");

}

BUGS

The rowCountOfQuery() method does not properly handle

complex SQL SELECT statements. If more than one "FROM" token

appears in the query, (eg. in a subquery subordinated in a

WHERE clause), then this routine will NOT work properly - it

will mangle the query and cause an SQL parse error.

EXAMPLES

Here's a sample script which prints a count of the rows in

each of the tables in a database.

#!/somewhere/perl

19/Mar/01 Last change: perl 5.004, patch 01 19

NCCI Perl Documentation OraConnection(3)

BEGIN {

...

$ENV{'ORACLE_HOME'} = "../sys/oracle_home";

}

use OraConnection;

# Connect to the database

my ($myDb) = new OraConnection;

$myDb->connect ("myDb", "mylogin", "mypassword");

# Fetch a ref to a list of the table names

my ($myTableNames) = $myDb->tableNames ();

# Count the tables

printf "%30s %s\n", "Table", "Count";

printf "%30s %s\n", "-----", "-----";

my ($myTableName);

foreach $myTableName (@$myTableNames) {

my ($colNames) = $myDb->columnNames ($myTableName);

printf "%30s %5d\n", $myTableName,

$myDb->rowCount ($myTableName);

}

exit;

SEE ALSO

DBI and DBD::Oracle man pages.

AUTHOR

Clint Goss <clint@goss.com>, August 1997 - April 1999