Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Executing SQL with the Oracle Perl DBI

Oracle Tips by Mladen Gogala

Mladen Gogala is the author of "Easy Oracle PHP".

See the whole article: Perl DBI and Oracle

Mladen Gogala is available to assist you with your toughest Perl DBI problems.  Just call.

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.

 

#!/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 = "SELECT * FROM EMP";
my $sth = $db->prepare($SEL);
$sth->execute();
 
while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n";
}
 
END {
    $db->disconnect if defined($db);
}

 

The output looks exactly as expected:

 

bash-3.00$ ./ttt
7369    SMITH   CLERK   7902    17-DEC-80       800                     20
7499    ALLEN   SALESMAN        7698    20-FEB-81       1600    300     30
7521    WARD    SALESMAN        7698    22-FEB-81       1250    500     30
7566    JONES   MANAGER 7839    02-APR-81       2975                    20
7654    MARTIN  SALESMAN        7698    28-SEP-81       1250    1400    30
7698    BLAKE   MANAGER 7839    01-MAY-81       2850                    30
7782    CLARK   MANAGER 7839    09-JUN-81       2450                    10
7788    SCOTT   ANALYST 7566    09-DEC-82       3000                    20
7839    KING    PRESIDENT                       17-NOV-81       5000           10
7844    TURNER  SALESMAN        7698    08-SEP-81       1500    0       30
7876    ADAMS   CLERK   7788    12-JAN-83       1100                    20
7900    JAMES   CLERK   7698    03-DEC-81       950                     30
7902    FORD    ANALYST 7566    03-DEC-81       3000                    20
7934    MILLER  CLERK   7782    23-JAN-82       1300                    10
bash-3.00$

 

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.

#!/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 = "SELECT * FROM EMP";
my $sth = $db->prepare($SEL);
$sth->execute();
my $nf = $sth->{NUM_OF_FIELDS};
print "This statement returns $nf fields\n";
 
for ( my $i = 0; $i < $nf; $i++ ) {
    my $name = $sth->{NAME}[$i];
    my $type = $sth->{TYPE}[$i];
    my $prec = $sth->{PRECISION}[$i];
    my $scle = $sth->{SCALE}[$i];
    my $tn=$db->type_info($type)->{TYPE_NAME};
    print
        "Field number $i: name $name of type $tn with precision $prec,$scle\n";
}
while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n";
}
print "\n\nThis query returned " . $sth->rows . " rows.\n";
 
END {
    $db->disconnect if defined($db);
}

 

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

7369            SMITH       CLERK       7902            17-DEC-80 800                                                      20               
7499            ALLEN       SALESMAN                 7698            20-FEB-81  1600            300              30               
7521            WARD       SALESMAN                 7698            22-FEB-81  1250            500              30               
7566            JONES        MANAGER                   7839            02-APR-81 2975                                                    20               
7654            MARTIN   SALESMAN                 7698            28-SEP-81  1250            1400            30               
7698            BLAKE      MANAGER                   7839            01-MAY-81                   2850                                                    30               
7782            CLARK      MANAGER                   7839            09-JUN-81  2450                                                    10               
7788            SCOTT       ANALYST                    7566            09-DEC-82 3000                                                    20               
7839            KING          PRESIDENT                                                          17-NOV-81 5000                                                    10               
7844            TURNER    SALESMAN                 7698            08-SEP-81  1500            0                  30               
7876            ADAMS    CLERK       7788            12-JAN-83 1100                                                    20               
7900            JAMES      CLERK       7698            03-DEC-81 950                                                      30               
7902            FORD         ANALYST                    7566            03-DEC-81 3000                                                    20               
7934            MILLER     CLERK       7782            23-JAN-82 1300                                                    10               

 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational