extproc_perl 2.0 Preview Writing Oracle Stored Procedures in Perl

1
extproc_perl 2.0 PreviewWriting Oracle Stored
Procedures in Perl

• Philadelphia Perl Mongers
• December 8, 2003
• Jeff Horwitz (jeff_at_smashing.org)

2
Before We Start

• Who has experience with Oracle?
• Who has experience with SQL?
• Has anybody ever used
• extproc_perl 1.0.x?
• PL/Perl? (Postgres)
• MyPerl? (MySQL)

3
Stored Procedure Overview

• Stored procedures are handy for
• Repetitive tasks
• Functions
• Reporting
• Triggers
• Languages supported by Oracle as of 9i
• PL/SQL
• Java
• C

4
Problems with PL/SQL Java

• PL/SQL
• cumbersome syntax
• limited functionality (getting better 10g)
• limited extensibility
• Java
• relatively slow
• resource-intensive
• forced OO-style

5
External Procedures

• Oracle supports C through external procedures
• Create an Oracle library
• Create a procedure referencing the library
• Oracle spawns a process (extproc)
• extproc loads your shared library
• extproc calls your C function

6
Perl Stored Procedures

• Run in an external procedure
• Thus the name extproc_perl
• Embedded Perl interpreter
• Persistent interpreter
• One-time compilation
• Database callbacks
• Automatic data type conversion

7
Requirements

• A Solaris or Linux release supported by Oracle
• Perl 5.6.0 or higher
• shared libperl
• no threads
• Oracle 8i (8.1.7) or higher

8
Perl Stored Procedures

• Can be functions or procedures
• Can accept arguments
• Can return data
• Can access the calling database
• Can use Perl modules
• Are Portable

9
What Can I Do With Them?

• Input validation normalization
• Index by regular expression
• Manipulate binary data (images, MP3)
• Encryption checksumming
• Dynamic data retrieval
• Or just code in a familiar language

10
extproc_perl Process Flow
11
Hello World!
sub hello my name shift
return "Hello, name!"
12
Hello World!
SQLgt select hello('Philly.pm') from
dualHELLO('PHILLY.PM')------------------Hello
, Philly.pm!
13
Joys of Interpreter Persistence
SQLgt set timing onSQLgt select hello('Philly.pm')
from dualHELLO('PHILLY.PM')------------------
Hello, Philly.pm!Elapsed 000000.32
SQLgt select hello('Philly.pm') from
dualHELLO('PHILLY.PM')------------------Hello
, Philly.pm!Elapsed 000000.00
14
Functions vs. Procedures

• Functions
• return values that are used in the function's
  place
• can be used in queries
• Procedures
• perform a task
• may return values in arguments

15
Calling Perl Stored Procedures

• Generic wrappers (1.0.x 2.0)
• Can call any Perl subroutine as a function or
  procedure
• Only support VARCHAR2
• Surprisingly flexible
• Oracle will pass integers as VARCHAR2
• Perl's autoconversion of scalars
• Only support IN parameters

16
Calling Perl Stored Procedures

• Perl.proc
• exec Perl.proc('give_raise', 'Jones', 1000)
• Perl.func
• update accounts set password Perl.func('encrypt'
  , 'foobar')

17
Calling Perl Stored Procedures

• Direct execution (2.0)
• Real external procedures
• no Perl.func or Perl.proc
• Less overhead
• Not limited to VARCHAR2 data types
• Can use IN OUT and OUT parameters

18
Calling Perl Stored Procedures

• exec give_raise('Jones', 1000)
• update accounts set password encrypt('foobar')
• exec double(number)

19
Code Storage

• Bootstrap file
• regular file on a local filesystem
• run once at interpreter initialization
• good for system specific and global data
• Code table
• special table in the database itself
• one row per subroutine
• isolates subroutine parsing
• travels with the database (migrations, backups)

20
Importing Subroutines

• Use Perl.import_perl to import a subroutine from
  a fileSQLgt exec Perl.import_perl('double','doub
  le.pl')
• Use Perl.drop_perl to drop subroutinesSQLgt
  exec Perl.drop_perl('double')

21
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
22
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
23
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
24
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
25
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
26
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
27
Configuring Direct Execution
CREATE OR REPLACE PROCEDURE perl_subroutine_name(
arg1 IN oracle_type1, arg2 IN
oracle_type2, ...) AS EXTERNAL NAME
"EP_perl_subroutine_name" LIBRARY PERL_LIB WITH
CONTEXT PARAMETERS ( CONTEXT, arg1
external_type1, arg2 external_type2, ...)
28
Configuring Direct Execution

• Now create a C wrapperexec Perl.create_wrapper(
  'name(IN type1, OUT type2) RETURN type')
• Can also do this with Perl.import_perl
• Add to list in perlxsi.mk
• Link with make f perlxsi.mk

29
Using Perl Modules

• Oracle does not allow dynamic loading of shared
  objects
• XS modules must be prelinked into extproc_perl
  library (pure Perl is okay)
• Configuration does this for you
• Special Makefile perlxsi.mk will relink
  extproc_perl when module list changes

30
Accessing the Database

• Use DBI (DBDOracle) to access the database from
  Perl
• Uses native OCI callbacks
• Need patch for DBDOracle
• Integrated in 1.15
• No SQLNet overhead FAST!

31
Accessing the Database

• Use the ExtProc module to get a DBI handle and
  proceed normally!

use ExtProc sub foo my dbh
ExtProc-gtdbi_connect ...
32
Accessing the Database

• Limitations
• Can query from any function or procedure
• DML only from procedures (e.g. INSERT)
• no DDL at all (e.g. CREATE, DROP)
• These are Oracle limitations that apply to all
  stored procedures

33
Things We Haven't Covered

• Installation configuration
• Debugging facilities
• Security
• Bundled Perl utilities
• Oracle Perl package utilities
• Data persistence
• Diagnostic views

34
And now
EXAMPLES!
35
MD5 Checksum Function
use DigestMD5 qw(md5_hex) sub md5 return
md5_hex(_0)
36
MD5 Checksum Function
SQLgt select md5('foobar') from dual MD5('FOOBAR'
) -------------------------------- 3858f62230ac3c9
15f300c664312c63f
37
Stock Quote Function
sub quote my sym shift my
q FinanceQuote-gtnew() my h
q-gtyahoo(sym) return
hsym,'price'
38
Stock Quote Function
SQLgt select quote('ORCL') as quote 2gt from
dual QUOTE ----- 12.74
39
Portfolio View
SQLgt create table stocks ( 2gt symbol
VARCHAR2(8) )Table created.SQLgt create view
portfolio as ( 2gt select symbol, 3gt
quote(symbol) as price 4gt from stocks
)View created.
40
Portfolio View
SQLgt create table stocks ( 2gt symbol
VARCHAR2(8) )Table created.SQLgt create view
portfolio as ( 2gt select symbol, 3gt
quote(symbol) as price 4gt from stocks
)View created.
41
Portfolio View
SQLgt select from portfolio 2gt where price
gt 10 3gt order by priceSYMBOL
PRICE---------- ----------ORCL 12.74CSCO
23.75MSFT 25.98
42
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders (name,
symbol, shares, price) values(?,?,?,?)')
sth-gtexecute(name, symbol, shares,
price) put_line("Purchased shares
shares of symbol for name at \price")
dbh-gtdisconnect
43
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders (name,
symbol, shares, price) values(?,?,?,?)')
sth-gtexecute(name, symbol, shares,
price) put_line("Purchased shares
shares of symbol for name at \price")
dbh-gtdisconnect
44
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders (name,
symbol, shares, price) values(?,?,?,?)')
sth-gtexecute(name, symbol, shares,
price) put_line("Purchased shares
shares of symbol for name at \price")
dbh-gtdisconnect
45
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders (name,
symbol, shares, price) values(?,?,?,?)')
sth-gtexecute(name, symbol, shares,
price) put_line("Purchased shares
shares of symbol for name at \price")
dbh-gtdisconnect
46
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders
name, symbol, shares, price)
values(?,?,?,?)') sth-gtexecute(name,
symbol, shares, price)
put_line("Purchased shares shares of
symbol for name at \price")
dbh-gtdisconnect
47
Stock Purchase Procedure
use ExtProc use ExtProcUtil qw(put_line) sub
buy_stock my (name, symbol, shares)
_at__ my price quote(symbol)
my dbh ExtProc-gtdbi_connect my sth
dbh-gtprepare('insert into orders (name,
symbol, shares, price) values(?,?,?,?)')
sth-gtexecute(name, symbol, shares,
price) put_line("Purchased shares
shares of symbol for name at \price")
dbh-gtdisconnect
48
Stock Purchase Procedure
SQLgt set serveroutput on SQLgt exec
buy_stock('jeff', 'ORCL', 100) Purchased 100
shares of ORCL for jeff at 12.71 PL/SQL
procedure successfully completed. SQLgt select
from orders NAME SYMBOL SHARES
PRICE -------------------------------------- Jeff
ORCL 100 12.71
49
Stock Purchase Procedure
SQLgt set serveroutput on SQLgt exec
buy_stock('jeff', 'ORCL', 100) Purchased 100
shares of ORCL for jeff at 12.71 PL/SQL
procedure successfully completed. SQLgt select
from orders NAME SYMBOL SHARES
PRICE -------------------------------------- Jeff
ORCL 100 12.71
50
Stock Purchase Procedure
SQLgt set serveroutput on SQLgt exec
buy_stock('jeff', 'ORCL', 100) Purchased 100
shares of ORCL for jeff at 12.71 PL/SQL
procedure successfully completed. SQLgt select
from orders NAME SYMBOL SHARES
PRICE -------------------------------------- Jeff
ORCL 100 12.71
51
Stock Purchase Procedure
SQLgt set serveroutput on SQLgt exec
buy_stock('jeff', 'ORCL', 100) Purchased 100
shares of ORCL for jeff at 12.71 PL/SQL
procedure successfully completed. SQLgt select
from orders NAME SYMBOL SHARES
PRICE -------------------------------------- Jeff
ORCL 100 12.71
52
Stock Purchase Procedure
SQLgt set serveroutput on SQLgt exec
buy_stock('jeff', 'ORCL', 100) Purchased 100
shares of ORCL for jeff at 12.71 PL/SQL
procedure successfully completed. SQLgt select
from orders NAME SYMBOL SHARES
PRICE -------------------------------------- jeff
ORCL 100 12.71
53
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
54
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
55
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
56
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
57
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
58
Phone Number Normalization
sub normalize_phone my phone shift my
(a,p,n) if (phone / \((\d3)\)\s
\-\. (\d3)\s\.\-\s (\d4)/x)
(a,p,n) (1,2,3) phone "(a)
p-n" else ora_exception('invalid
number')
59
Phone Number Normalization
SQLgt create or replace trigger phoneval 2gt
before insert or update of phone 3gt on
contact 4gt for each row 5gt BEGIN 6gt
normalize_phone(new.phone) 7gt ENDTrigger
created.
60
Phone Number Normalization
SQLgt insert into contact (name,phone) values
('Joe User','610-555-1212') 1 row
created. SQLgt select from contact NAME
PHONE --------------------------- Joe User
[phone]
61
Phone Number Normalization
SQLgt insert into contact (name,phone) values
('Jane User','1212') ERROR at line
1 ORA-20100 PERL EXTPROC ERROR
invalid number ORA-06512 at "JEFF.NORMALIZE_PHONE
", line 0 ORA-06512 at
"JEFF.PHONEVAL", line 2 ORA-04088 error during
execution of trigger 'JEFF.PHONEVAL'
62
Phone Number Normalization
SQLgt insert into contact (name,phone) values
('Jane User','1212') ERROR at line
1 ORA-20100 PERL EXTPROC ERROR
invalid number ORA-06512 at "JEFF.NORMALIZE_PHONE
", line 0 ORA-06512 at
"JEFF.PHONEVAL", line 2 ORA-04088 error during
execution of trigger 'JEFF.PHONEVAL'
63
select status from extproc_perl

• Currently in alpha testing (closed)
• Volunteers are still welcome to test
• Everything in this talk currently WORKS in the
  2.0 alpha

64
select future from extproc_perl

• End of year 2.0 beta release
• 2.0 released by March 2004
• Oracle type support
• Aggregates (e.g. count, min, max)
• More built-in Perl-specific utilities
• Complete DBI integration in DBDOracle 1.15

65
Resources

• http//www.smashing.org/extproc_perl
• source code documentation
• Mailing list extproc-perl_at_smashing.org
• public forum for help and announcements
• Me jeff_at_smashing.org