Executing SQL with the Oracle Perl DBI

Oracle Tips by Mladen Gogala

Executing SQL with DBI

Now that we have a database handle properly created and configured, we can use it to execute SQL commands. Oracle SQL executes in several phases:

Parse phase: SQL statement is checked for syntactical validity and all objects are checked to see whether they exist or not and whether the user has appropriate access privileges or not. It is during this phase that the optimizer is invoked, statistics examined and the optimal access path determined. This parsing operation can be excruciatingly expensive and is best avoided, if at all possible.

Bind phase: placeholders in SQL statements are “connected” to the program variables. During this process the address of program variables is “made known” to oracle, so that it can read or write values from it. The same SQL command can be executed over and over again, without the need for re-parsing for various values of the program variable.

Execute phase: In this phase, the SQL statement in question is executed and the appropriate program areas within the program are updated to reflect that. Oracle can postpone parsing until the statement is actually executed, therefore decreasing the number of calls to oracle and the number of round trips over the network, necessary to reach the database.

Define phase: define phase exists only for queries. During this phase we define variables to receive output. This phase is not really necessary with Perl DBI as DBI calls also create variables to receive data.

Fetch phase: during the fetch phase, data is retrieved from an Oracle cursor and stored into program variables.  Fetch is not bi-directional, it can be read sequentially and closed when no longer needed.

These phases of execution correspond to DBI calls. In particular, “parse” phase corresponds to the prepare DBI call. Now is the right time to demonstrate the fabled “deferred parse:

Example 1.

#!/usr/bin/perl -w

use strict;

use DBI;

my $db = DBI->connect( "dbi:Oracle:Local", "scott", "tiger" )

    || die( $DBI::errstr . "\n" );

$db->{AutoCommit}    = 0;

$db->{RaiseError}    = 1;

$db->{ora_check_sql} = 0;

$db->{RowCacheSize}  = 16;

my $SEL = "invalid SQL statement";

my $sth = $db->prepare($SEL);

print "If you see this, parse phase succeeded without a problem.\n";

$sth->execute();

print "If you see this, execute phase succeeded without a problem.\n";

END {

    $db->disconnect if defined($db);

}

The result of this script is the following:

bash-3.00$ /tmp/ttt

If you see this, parse phase succeeded without a problem.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

As you can see, parse succeeded without a problem, although the SQL statement in the variable $SEL was clearly invalid. There is one more thing in this script that needs to be explained, namely the END block. The END block gets executed when the script exits, regardless of the way it exits. So , let’s comment it out and see what happens:

bash-3.00$ /tmp/ttt

If you see this, parse phase succeeded without a problem.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "invalid SQL statement"] at /tmp/ttt line 13.

Issuing rollback() for database handle being DESTROY'd without explicit disconnect().

bash-3.00$

The last message was issued by the DBI, because of exiting without disconnect. To avoid that, it is a good practice to include an END block like in the example 1. End block can also be used to issue rollback after a failed transaction. To summarize the example 1, a successful “connect” call established a database handle $db. Database handle has a prepare method, used to create a statement handle, $sth. Statement handle has many methods. Execute, bind and fetch are all methods of a statement handle.

Now, let’s replace the invalid SQL with a valid one, namely “select * from emp”; Our script will need more then cosmetic changes. The script will now look like this:

Example 2.

The output looks exactly as expected:

So, now we not only have execute, we also have fetch, implemented by fetchrow_array method of the statement handle. The array @row is created on the fly, for each row, by the fetchrow_array method. Therefore, we do not have to do “define” as we would have had, had we been programming an OCI program instead of a Perl script.

There several more methods for fetching data from an Oracle cursor:

l       fetchrow_arrayref

l       fetchrow_hashref

l       fetchall_arrayref,

l       fetchall_hashref

These methods differ by what do they return (hash or array reference) and how many rows do they return  (one or all). Methods returning hash reference are convenient when we want to pick column value by column name, not its sequence number. For  methods that return reference to hash, retrieval works like this:

$row=sth->fetchrow_hashref();

$ename=$row->{ENAME};

Methods that fetch all rows are convenient when the underlying query returns relatively few rows that are frequently referenced within a script.

All those methods fetch  relational NULL as “undef” value, which is a problem if we have “use strict” in effect. That is the reason for having the line which reads:

                 $_ = "\t" if !defined($_);

in the script. If that line is commented out, we get an ugly output, laced with warning messages, which looks like this:

bash-3.00$ ./ttt

Use of uninitialized value in concatenation (.) or string at line 17

7369    SMITH   CLERK   7902    17-DEC-80       800

7499    ALLEN   SALESMAN        7698    20-FEB-81       1600

7521    WARD    SALESMAN        7698    22-FEB-81       1250

Use of uninitialized value in concatenation (.) or string at line 17

7566    JONES   MANAGER 7839    02-APR-81       2975

7654    MARTIN  SALESMAN        7698    28-SEP-81       1250

Use of uninitialized value in concatenation (.) or string at line 17

7698    BLAKE   MANAGER 7839    01-MAY-81       2850

Use of uninitialized value in concatenation (.) or string at line 17

7782    CLARK   MANAGER 7839    09-JUN-81       2450

Use of uninitialized value in concatenation (.) or string at line 17

7788    SCOTT   ANALYST 7566    09-DEC-82       3000

Use of uninitialized value in concatenation (.) or string at line 17

Use of uninitialized value in concatenation (.) or string at line 17

7839    KING    PRESIDENT               17-NOV-81       5000

7844    TURNER  SALESMAN        7698    08-SEP-81       1500

Use of uninitialized value in concatenation (.) or string at line 17

7876    ADAMS   CLERK   7788    12-JAN-83       1100

Use of uninitialized value in concatenation (.) or string at line 17

7900    JAMES   CLERK   7698    03-DEC-81       950

Use of uninitialized value in concatenation (.) or string at line 17

7902    FORD    ANALYST 7566    03-DEC-81       3000

Use of uninitialized value in concatenation (.) or string at line 17

7934    MILLER  CLERK   7782    23-JAN-82       1300

bash-3.00$

The strict checking, enforced by the “use strict” pragma, will flag out any NULL values encountered by our script. That must be handled if we don’t want a warning message after each NULL value.

This was a very simple script, executing and retrieving results of  the simplest possible query from the SCOTT.EMP table. The additional question one can immediately ask is the following:

I don’t know much about the EMP table and SCOTT demo schema. How many columns are returned, what are their names, types and sizes? How many rows were returned by the query? Of course, one solution is to scold the unfortunate wannabe programmer and tell him to go and read the fine manual, but RTFM is not always considered a polite and civilized solution, so I developed a DBI solution, presented in an extended version of the example 2:

Example 2a.

Of course, the most interesting thing here is the output of this script, which looks like this:

This statement returns 8 fields

Field number 0: name EMPNO of type DECIMAL with precision 4,0

Field number 1: name ENAME of type VARCHAR2 with precision 10,0

Field number 2: name JOB of type VARCHAR2 with precision 9,0

Field number 3: name MGR of type DECIMAL with precision 4,0

Field number 4: name HIREDATE of type DATE with precision 75,0

Field number 5: name SAL of type DECIMAL with precision 7,2

Field number 6: name COMM of type DECIMAL with precision 7,2

Field number 7: name DEPTNO of type DECIMAL with precision 2,0

This query returned 14 rows.

So, what is the answer to our question? Column name, column type, column precision and scale, as well as the number of columns returned are all attributes of the statement handle. Those attributes are defined when the statement is parsed. In our script, we bundled the parse phase with the execute phase, which means that those attributes are not defined until we execute the handle, which is a colloquial expression for “invoke the handle’s execute method”. If we have a valid statement handle $sth,  that handle automatically has the following elements:

• $sth->{NUMBER_OF_FIELDS} is an integer field telling us how many columns does the query return, if the statement is a query.
   
• $sth->{NAME} is an array, with  $sth->{NUMBER_OF_FIELDS} elements, each element being the column name of the corresponding returned column.
   
• $sth->{TYPE} is also an array which contains column types. Column types are integers, because they're consistent across databases. Supported types depend on the version of the DBI and can be translated into type names by using the type_info() method as shown in the example 2a.
   
• $sth->{PRECISION} and $sth->{SCALE} are both arrays containing the information about the precision and the scale of the returned column. Note that the precision for VARCHAR2 column is its size. Its scale, of course is 0. The value of scale traditionally defines the number of decimal places in the number field.
   
• Last but not least is the rows method. Invoking $sth->rows() will give you the number of rows fetched so far or the number of affected rows. This method is completely analogous to the PL/SQL ROWCOUNT attribute and is used in exactly the same way.
   
• There are, of course, other elements which can be located and studied on the online documentation page.

Generally speaking, statement handles are Oracle cursors, disguised as Perl objects. The whole philosophy of  database handles and statement handles is the same as the philosophy of database connections and cursors: to open a cursor, one has to connect to a database. In the OO world of the modern programming languages, objects have “methods” and “properties” or “members”, so those are the terms that we must use when talking about DB handles  and statement handles.

Now we know how to execute a simple SQL statement and get the basic information about it. For statements like “ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'”, there is also a shortcut:

$db=DBI->connect(“dbi:Oracle:Local”,”scott”,”tiger”);

$db->do(“ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'”);

No prepare is necessary, no statement handle is available, the “do” method is a database handle method which fuses prepare and execute methods in a single step. That is convenient for the alter session commands or DDL commands. The next thing to learn is how to make a program variable “known” to Oracle.

For a complete treatment of PHP and Oracle, see Mladen Gogala's great new book "Easy Oracle PHP".  This unique book focuses on removing the guesswork from creating dynamic web pages with Oracle content, and it's only $19.95.