Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




EnterpriseDB: PL/PERL
Oracle Tips by Burleson

Perl is a C-like language developed by Larry Wall.  It is used in utilities and applications in every operating system available.  It is arguably one of the most popular languages even if it is less visible than some others are.  Most flavors of Linux come with Perl pre-installed.  For Windows, you can download ActivePERL from 

In the Chapter on Oracle Compatibility (Chapter 8), I will show you how to use Perl to expand your options for using file input and output in EnterpriseDB.  In this chapter, I will introduce you to the EnterpriseDB implementation of Perl.

Below is a function that provides the same basic functionality as the example that I have been using.

CREATE OR REPLACE FUNCTION get_emp_names_perl(integer)  RETURNS TEXT AS $$
   $query = "SELECT ename FROM emp WHERE empno = $_[0]"; 

    $cursor = spi_exec_query($query, 1);   

    $foo = $cursor->{rows}[0]->{ename};
    return $foo;
$$ LANGUAGE plperlu;

The spi_exec_query call accepts a string parameter that defines a valid query.  The second parameter is a count of the max number of rows to return.  In this case I wanted only a single row.

In PL/Perl, spi_exec_query returns a zero-based array of array row values (that's the $cursor variable).  I retrieved the single ename value from row 1 by accessing the array at offset 0.  I pointed (->) to the array, which pointed to row array ({row}) at offset 0 and then pointed to the ename value. 

When run, this function works exactly like the examples above:

SELECT get_emp_names_perl(7788); 


(1 row)

SELECT get_emp_names_perl(1); 


(1 row)


This chapter covered a lot of territory.  It began with an introduction describing how EnterpriseDB's SPL is compatible with Oracle's PL/SQL.

I covered the block structure of the code, comments, and variables, both simple and complex.  There was detail on scalar variables and composite variables and detailed examples showing how to declare and use those.

The control structures that were covered include the conditionals, IF and CASE and the flow control examples include LOOP, WHILE and FOR.

A critical piece to understanding and using SPL includes the ability to effectively use SQL embedded in your code.  To assist with that, this chapter also included many examples of static and dynamic SQL.  EnterpriseDB SPL provides full cursor support and provides Oracle compatible ref cursors.  Ref cursors can be used to pass a pointer to a query from one procedure to another.

While not as robust as PL/SQL exception handling, SPL does provide Oracle compatibility with the procedure raise_application_error.  SPL also provides a range of named exceptions.

Programming SPL is at its finest when properly utilizing packages.  In this chapter I showed how to use procedures and functions within, and external to, packages.  I recommend that all code be wrapped in a package.

Triggers in EnterpriseDB can be written in SPL and I recommend that you do so.  SPL triggers are very easy to read and maintain.  This chapter showed you how to define triggers using SPL.

I closed the section on SPL by showing the built-in XML support via xpath_string, xpath_number, xpath_nodeset, xpath_table and xslt_process.  I provide examples using all five of these functions.  In EnterpriseDB, XML support is not as complete as it is in Oracle but it has the basics that are needed.

I closed the chapter by showing you a few of the available languages that are available to developers of EnterpriseDB applications (PL/pgSQL, PL/Perl and PL/TCL).  I also showed you where to get these languages and how to install them.  I finished with a consistent example in their usage.

There is no way a single chapter, or a single book for that matter, can cover every possible combination of features and languages in EnterpriseDB.  This chapter was intended to give you a place to start and I hope it did that.  The remainder of the book will use the things you learned in this chapter.

The next chapter, Using Developer Studio, will show you how to apply the things you learned in Chapter 3 and Chapter 4.  You will learn how to write and tune your code and access other databases.


This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter