extproc_perl Embedding Perl in Oracle

1
extproc_perlEmbedding Perl in Oracle

• OReilly Open Source Convention
• July 10, 2003
• Jeff Horwitz, TargetRx, Inc. jeff_at_smashing.org

2
Who am I?

• Manager of Production Systems for TargetRx, Inc.
• E-mail jeff_at_smashing.org
• Author of extproc_perl, AuthenKrb4 and
  AuthenKrb5 Perl modules
• Author of UNIX System Management Primer Plus
  (SAMS 2003)

3
Agenda

• Overview
• Architecture
• Using Perl modules
• Accessing the database
• Security Debugging
• Limitations
• Future enhancements

4
Stored Procedure Languages

• PL/SQL
• Limited native capabilities
• Limited extensibility
• Can be slow
• Java
• Slow, especially at startup
• Large memory footprint
• Tends to have a longer development cycle

5
Enter extproc_perl

• Write stored procedures in Perl!
• Embedded Perl interpreter
• Supports almost all features of Perl
• Interpreter and data persistence
• Fast execution once code is interpreted
• Code stored in file system or database
• Database callbacks using DBI

6
What Can I Do With It?

• Input validation normalization
• Index by regular expression
• Manipulate binary data (images, MP3)
• Encryption checksumming
• Dynamic data retrieval

7
"Hello, world" Example

• sub hello my name shift return
  "Hello, name"
• SQLgt select perl('hello','OSCON') as 2
  response from dual
• RESPONSE------------------------------------Hel
  lo, OSCON!

8
extproc_perl Architecture
9
extproc_perl Processes
10
Code Storage

• Bootstrap File
• Code stored in a file
• Read at interpreter creation time
• Path is hard coded at compile time
• Up to YOU to share code between servers

• Code Table
• Code stored in the database table
• Parsed when an unknown subroutine is encountered
• Can change table name from Oracle
• Shared between servers in one instance

11
The "ep" command

• Imports/exports code from database
• bash ep import db1 code mycode.plImporting
  mycode.pl to code in db1Username
  jeffPasswordCode successfully imported.
• Can roll your own importer if you want

12
extproc_perl APIs

• PL/SQL API
• perl(sub, arg1, )
• functional interface
• wraps ora_perl_func
• returns VARCHAR2
• perl_p(sub, arg1, )
• procedural interface
• wraps ora_perl_proc
• no return value
• can write your own
• custom arguments
• custom return values

• C API
• char ora_perl_func( OCIExtProcContext
  ctx, OCIInd ret_ind, char sub, ...)
• Void ora_perl_proc( OCIExtProcContext
  ctx, char sub, ...)

13
Special Subroutines

• _flush() destroys current interpreter data
• _version() returns extproc_perl version
• _modules() returns list of statically linked
  modules
• _codetable(table_name) sets or returns code
  table
• _preload() load code from database immediately

• _error() returns most recent eval error (_at_,
  a.k.a. ERRSV)
• _errno() returns most recent system error (!)
• _eval(code) evaluates Perl code directly
• _enable_debug() enable debugging to log file
• _disable_debug() disable debugging to log file

14
Using Perl Modules

• DynaLoader not supported by Oracle
• One solution statically link modules
• Would need to rebuild EVERY MODULE!
• But DON'T PANIC!

15
A Better Solution

• Prelink every compiled Perl module shared object
  to extproc_perl.so
• Need to know which modules you want to use at
  compile time
• Select modules in initial configuration
• Can reconfigure to add new modules
• Pure Perl modules do not need this

16
MD5 Checksum Code
use DigestMD5 qw(md5_hex)sub md5
return md5_hex(_0)
17
MD5 Checksum SQL
SQLgt select perl('md5','foobar') 2gt from
dualPERL('MD5','FOOBAR')----------------------
----------3858f62230ac3c915f300c664312c63f
18
Stock Quote Code
use FinanceQuotesub quote my sym
shift my q FinanceQuote-gtnew() my
h q-gtyahoo(sym) return
hsym,'price'
19
Stock Quote SQL
SQLgt select perl('quote','ORCL') 2gt from
dualPERL('QUOTE','ORCL')--------------------1
2.56
20
The ExtProc Module

• Facilitates callbacks from Perl
• Uses Oracle's OCI functions
• Some convenience functions
• USER()
• DATABASE_NAME()
• SESSIONID()
• Oracle exceptions

21
Phone Normalization Code
sub phoneval my phone shift my
(a,p,n) if (phone /\((\d3)\)\s\-\.
(\d3)\s\.\-\s(\d4)/x)
(a,p,n) (1,2,3) return "(a)
p-n" ExtProcexception('invalid
phone')
22
Phone Normalization Trigger
create or replace trigger phoneval before
insert or update of phone on contact for
each row BEGIN new.phone
perl('phoneval',new.phone) END
23
Phone Normalization SQL
SQLgt insert into contact (name,phone)
values('Jeff','610-555-1234')1 row
created.SQLgt select from contactNAME
PHONE------------- --------------Jeff
[phone]
24
Phone Normalization SQL (cont.)
SQLgt insert into contact (name,phone)
values('Joe','KL5-2345')ERROR at line
1ORA-20100 PERL EXTPROC ERROR invalid
phoneORA-06512 at "JEFF.PERL", line
0ORA-06512 at "JEFF.PHONEVAL", line
2ORA-04088 error during execution of trigger
'JEFF.PHONEVAL'
25
DBI Callbacks

• Can use DBI to query/update database
• Uses native C OCI callbacks
• No new SQLNet connection
• FAST FAST FAST!
• Need patch for DBDOracle
• should be 100 integrated soon
• DBDOracle 1.15?

26
Restrictions on Callbacks

• Must adhere to Oracle's rules
• Can query calling database from any function or
  procedure
• DML statements from procedures only
• DDL statements will not work at all

27
"Connecting" To The Database

• ExtProc-gtdbi_connect()
• Behind the scenes call to DBI-gtconnect
• Passes current OCI context to DBI
• Patched DBDOracle handles callback
• Returns DBI handle
• Not a true "connection" like SQLNet
• Call dbi_connect once per transaction

28
Image Table

• SQLgt describe imagesName Null?
  Type------------- -------- ----------------ID
  VARCHAR2(255)DATA
  BLOB

29
Image Size Code
use DBIuse ExtProcuse GD sub width my
id shift my dbh ExtProc-gtdbi_connect()
unless (dbh) ExtProcexception(DBI-gterrst
r) return undef dbh-gtLongReadLen
65536 my sth dbh-gtprepare('select data
from images where id ?') sth-gtexecute(id)
my (data) sth-gtfetchrow_array
sth-gtfinish my image GDImage-gtnew(data)
my (width) image-gtgetBounds return
width
30
Image Size Query
SQLgt select perl('width','apache_pb.png') 2gt
as width 3gt from dualWIDTH-----------------
------------259
31
Views and extproc_perl

• Use views to simulate real tables
• Encapsulates calls to extproc_perl
• Good for "dynamic" tables

32
Portfolio View
SQLgt create view portfolio as ( 2gt select
symbol, 3gt perl('quote',symbol) as price 4gt
from stocks )View created.
33
Portfolio Query
SQLgt select from portfolio 2gt where price
gt 10 3gt order by priceSYMBOL
PRICE---------- ----------ORCL 12.10CSCO
15.69MSFT 24.22
34
Security

• External procedures run as Oracle user
• Analogous to CGI scripts
• Can change user in listener, or ...
• Taint checking in version 1.0
• All query arguments are tainted
• Enabled at compile time

35
Taint Checking Code
sub ttest my name shift NEXT LINE
IS NAUGHTY!!! open(FILE,"gtgt/tmp/name")
print FILE "foobar\n" close(FILE)
36
Taint Checking Output
SQLgt select perl('ttest','x') from dualselect
perl('ttest','x') from dual ERROR at
line 1ORA-20100 PERL EXTPROC ERROR
Insecuredependency in open while running with-T
switch at (eval 24) line 92.ORA-06512 at
"JEFF.PERL", line 0ORA-06512 at line 1
37
Debugging

• Internal trace log
• STDERR redirection
• Perl API for debug logging
• ExtProcep_debug(msg)
• Must compile in debugging support
• Enable/Disable dynamically at runtime

38
Important Limitations

• Single-threaded execution per session
• Slow index creation
• No native cross-session persistence
• Use shared memory or serialized data
• Limited access to Oracle internals
• Closed source

39
Guidelines

• When should I use extproc_perl?
• You need functionality only available in Perl
• The functionality is faster in Perl
• When shouldn't I use extproc_perl?
• For the heck of it
• Initial overhead of spawning the extproc
• When you need parallel execution
• e.g. bulk-load index creation

40
select future from extproc_perl

• Windows port -- help is welcome!
• Proper data type mapping
• Make it work with threaded Perl
• Isolate sessions in their own namespace
• Run code in "Safe" compartment
• Aggregate function support
• extproc_parrot?

41
Additional Resources

• Web Sitehttp//www.smashing.org/extproc_perl
• Mailing ListE-mail extproc-perl-join_at_smashing.or
  g
• BooksChapter 8 of Perl for Oracle DBAsby Andy
  Duncan and Jared Still,O'Reilly 2003.
• Covers version 0.93

42
Questions?