Perl for Oracle Tools and Technologies Tim Bunce

Perl for Oracle Tools and Technologies Tim Bunce Jan 2002

Perl for Oracle © Tim Bunce Jan 2002 Topical Topics l DBD: : Oracle – Hints and tips l Oracle’s OCI – The Oracle API l Oracle: : OCI – The new big thing l Perl Inside Oracle – The new way 2

DBD: : Oracle Hints and tips… (the under-documented stuff)

Perl for Oracle © Tim Bunce Jan 2002 Making the connection l Typical connection $dbh = DBI->connect(”dbi: Oracle: tnsname”, …) $dbh = DBI->connect(”dbi: Oracle: ”, …) l Connect in OPER or DBA mode $dbh = DBI->connect(”dbi: Oracle: ”, …, { ora_session_mode => $mode }) Where $mode is 2 for SYSDBA, or 4 for SYSOPER l Without using a TNS name $dbh = DBI->connect(”dbi: Oracle: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(foo. com)(PORT=1526))) (CONNECT_DATA=(SID=ORCL)))”, …) $dbh = DBI->connect(”dbi: Oracle: host=foo. com; sid=ORCL”, …) l Associate a name with the session $dbh = DBI->connect(”dbi: Oracle: ”, …, { ora_module_name => $0 }) 4

Perl for Oracle © Tim Bunce Jan 2002 Specifying bind types l By default DBD: : Oracle binds everything as strings n l including numbers Explicitly specifying Oracle-specific bind types can be useful for n n n Identifying LONG/LOB value types Identifying CURSOR value types CHAR values that need “fixed width comparison semantics” use DBD: : Oracle qw(: ora_types); $sth = $dbh->prepare(”UPDATE tablename SET foo=? WHERE bar=? ”); $sth->bind_param(1, ”dummy”, { ora_type => ORA_CLOB }); $sth->bind_param(2, ”dummy”, { ora_type => ORA_CHAR }); $sth->execute(@$_) foreach (@updates); – Note that the bound types are ‘sticky’ so execute(@values) can be used – Using { TYPE=>SQL_CHAR } would also work (and SQL_CLOB will soon) 5

Perl for Oracle © Tim Bunce Jan 2002 Returning cursors as values l You can return cursor objects $sth = $dbh->prepare(”BEGIN : csr : = func_returning_cursor(: arg); END; ”); $sth->bind_param(”: arg”, $arg); $sth->bind_param_inout(": csr", my $sth 2, 0, { ora_type=>ORA_RSET } ); $sth->execute; my $data = $sth 2 ->fetchall_arrayref; l But. . . n n Not all ways to generate cursors are supported yet (probably simple to add) The returned cursor currently needs to be explicitly closed like this: $sth 3 = $dbh->prepare("BEGIN CLOSE : cursor END"); $sth 3 ->bind_param_inout(": cursor", $sth 2, 0, { ora_type => ORA_RSET } ); $sth 3 ->execute; n Which neatly demonstrates that you can send cursors back to the server as well 6

Perl for Oracle © Tim Bunce Jan 2002 Bind RETURNING values l Oracle’s new RETURNING clause can also be used $sth = $dbh->prepare(q{ UPDATE anothertable SET X=? WHERE Y=? RETURNING Z }); $sth->bind_param(1, $x); $sth->bind_param(2, $y); $sth->bind_param_inout(3, $z, 100); $sth->execute; l But. . . n Currently only works for a single value (i. e. , update only updated one row) – but I’ll be fixing that soon 7

Perl for Oracle © Tim Bunce Jan 2002 Tuning the row cache l Oracle OCI supports a transparent client-side row cache n By default it’s just two rows – halves network round-trips, especially good when selecting a single row l DBD: : Oracle goes a little further… n n l Good, but not ideal if n n n l Automatically scales row cache to as many rows as will fit in 10 ethernet packets Based on an estimated average row width and SQL*Net protocol overheads You are selecting lots of data and are happy to have a larger cache If you’re query returns many rows but you only want the first few The estimated average row width isn’t accurate (enable trace to see it) Can be tuned manually n n $dbh->{Row. Cache} = $n; Where $n > 0 specifies the number of rows, and $n < 0 specifies the memory to use 8

The Oracle Call Interface O…C…I the Oracle A…P…I

Perl for Oracle © Tim Bunce Jan 2002 What can it do for you? l Read and write LOBs in chunks – Including streaming LOBs to or from the database via callbacks l Create and manipulate collections, iterators, user defined types – cursors, variable-length arrays, nested tables, etc l Have multiple users share the same database connection – very handy for web servers l Have multiple processes share the same transaction – very handy for high volume data loading l Non-blocking mode for OCI function calls – very handy for GUIs etc. 10

Perl for Oracle © Tim Bunce Jan 2002 Hold on, there’s more. . . l High speed bulk loading via Arrays or Direct Path Loading l Describe schema metadata in complete detail l Use Oracle’s data manipulation and formatting facilities – dates, numbers, character set conversions, etc l Advanced Queuing – Including Publish / Subscribe and asynchronous event notifications l Fetch a tree of related objects with a single call l Manage automatic fail-over with Parallel Server l Thread safe and thread hot 11

Oracle: : OCI Making OCI practical

Perl for Oracle © Tim Bunce Jan 2002 So what is Oracle: : OCI? l Simply… A Perl module that makes the full OCI API available in Perl l But that’s not all… – A very thin layer over the OCI API – Designed and built for speed – Automatic error checking built-in – Valuable detailed call tracing/debugging built-in – Integrates very well with DBI and DBD: : Oracle l An example. . . 13

Perl for Oracle © Tim Bunce Jan 2002 Pure Oracle: : OCI - attach to server l Load the module and initialise the OCI and its Environment handle: use Oracle: : OCI qw(: all); OCIInitialize(OCI_OBJECT | OCI_THREADED | OCI_EVENTS | OCI_SHARED, 0, 0); my $envhp = new_ptr('OCIEnv. Ptr'); # OCIEnv. Init($envhp, OCI_DEFAULT, 0, 0); l Allocate Error and Server handles: OCIHandle. Alloc($$envhp, my $errhp=0, OCI_HTYPE_ERROR, 0, 0); $errhp = new_ptr('OCIError. Ptr', $errhp); # OCIHandle. Alloc($$envhp, my $svrhp=0, OCI_HTYPE_SERVER, 0, 0); $svrhp = new_ptr('OCIServer. Ptr', $svrhp); # l Attach to the server: OCIServer. Attach($svrhp, $errhp, oci_buf_len(”tnsname”) , OCI_DEFAULT); 14

Perl for Oracle © Tim Bunce Jan 2002 Pure Oracle: : OCI - login to server l Allocate Service Context handle and associate it with the Server handle: OCIHandle. Alloc($$envhp, my $svchp=0, OCI_HTYPE_SVCCTX, 0, 0); $svchp = new_ptr('OCISvc. Ctx. Ptr', $svchp); # OCIAttr. Set($$svchp, OCI_HTYPE_SVCCTX, $$svrhp, 0, OCI_ATTR_SERVER, $errhp); l Allocate Session handle, set username/password, and login to the server: OCIHandle. Alloc($$envhp, my $authp=0, OCI_HTYPE_SESSION, 0, 0); $authp = new_ptr('OCISession. Ptr', $authp); # OCIAttr. Set($$authp, OCI_HTYPE_SESSION, oci_buf_len($user), OCI_ATTR_USERNAME, $errhp); OCIAttr. Set($$authp, OCI_HTYPE_SESSION, oci_buf_len($pass), OCI_ATTR_PASSWORD, $errhp); OCISession. Begin($svchp, $errhp, $authp, OCI_CRED_RDBMS, OCI_DEFAULT); OCIAttr. Set($$svchp, OCI_HTYPE_SVCCTX, $$authp, 0, OCI_ATTR_SESSION, $errhp); l Get an OCI attribute from an OCI handle: OCIAttr. Get($$handle, OCI_HTYPE_handle, my $attrib_value, 0, OCI_ATTR_name, $errhp); 15

Perl for Oracle © Tim Bunce Jan 2002 Pure Oracle: : OCI - example l Very little ‘excess’ code beyond the raw OCI calls n The few gray colored lines in the previous examples (with a # at the end) are temporary ‘scaffolding’ that will not be required in future releases l We’ll look at the gory details of how Perl maps to the OCI calls later l Two pages of OCI code required just to login to Oracle! n n And then another page of code to logout and clean up properly. The many handles do give you great flexibility, but let’s see how we can make it easier. . . 16

Oracle: : OCI + DBI/DBD: : Oracle Having made OCI practical, now lets’ make it easy. . .

Perl for Oracle © Tim Bunce Jan 2002 Season with a little DBI… l Do all the previous work the easy way - with just two lines of code: use DBI; $dbh = DBI->connect(”dbi: Oracle: ”, $user, $password); l Get an OCI attribute from a DBI handle: use DBI; use Oracle: : OCI qw(: all); $dbh = DBI->connect(”dbi: Oracle: ”, $user, $password); OCIAttr. Get($dbh, OCI_HTYPE_handle, my $attrib_value, 0, OCI_ATTR_name, $dbh); l That’s it! n n The $dbh DBI handle holds the DBD: : Oracle handle that, in turn, holds the OCI environment, error, service context and session handles The Oracle: : OCI module asks DBD: : Oracle to return whichever handle is needed 18

Perl for Oracle © Tim Bunce Jan 2002 Handling large objects l Fetch a LOB ‘locator’ (not the contents) using the DBI my $lob_locator = $dbh->selectrow_array( ”select my_lob from table_name where id=1 for update”, { ora_auto_lob => 0 } # return LOB locator not contents ); l then play with it using Oracle: : OCILob. Get. Length($dbh, $lob_locator, my $lob_len=0); OCILob. Trim($dbh, $lob_locator, $lob_len - 2); l and fetch, edit, and update some bytes in the middle my ($offset, $amount, $buffer) = ($lob_len/2, 44, ’’); OCILob. Read($dbh, $lob_locator, $amount, $offset, oci_buf_len($buffer, 200, $amount), 0, 0 ); $buffer =~ s/ATGC/ACTG/g; OCILob. Write($dbh, $lob_locator, $amount, $offset, oci_buf_len($buffer), OCI_ONE_PIECE, 0, 0, 0, 1 ); 19

Perl for Oracle © Tim Bunce Jan 2002 A picture is worth? Perl Application DBI Oracle: : OCI DBD: : Oracle Server 20

Perl for Oracle © Tim Bunce Jan 2002 Why should I use Oracle: : OCI? l It brings together the best tools … n n l The power of the OCI API The power of Perl language and extensive module library Plus … n n n The DBI takes much of the grunt work out of OCI development Oracle: : OCI only needed for the more specialized code DBD: : Oracle, via the DBI, takes care of the rest l All of which leads to rapid application development l And… it’s fun! 21

Oracle: : OCI - the guts Making it happen

Perl for Oracle © Tim Bunce Jan 2002 The basics l Primary goals n Change the API as little as possible – Oracle OCI documentation should also be Oracle: : OCI documentation! – (OCI reference manual=1000 pages, associated guides=800+600+800 pages!) n Any changes made should consistently conform to a small set of rules – So developers can translate the OCI API to the Oracle: : OCI API in their heads l Output parameters in OCI are output parameters in Perl n l parameter values are updated ‘in place’ (without refs) A handle is represented as a reference to an integer holding a pointer n n n Gives efficient access The integer is blessed into a class for type safety and extra functionality Using $$foo bypasses the type check 23

Perl for Oracle © Tim Bunce Jan 2002 Handling buffers l Many OCI functions take buffer + buffer length pairs: – would need OCIFoo(…, $string, length($string), …); – you can do OCIFoo(…, oci_buf_len($string), …); oci_buf_len() returns both values for you as a convenience l What about returning buffers/strings from OCI? n n n l Consider: OCIAttr. Get(. . . , void *buf, long *len, . . . ) on input len is pointer to long holding max buffer size on return len has been updated to hold the length of data actually written How to support this with typical perl simplicity? 24

Perl for Oracle © Tim Bunce Jan 2002 Quantum entanglement? l Make oci_buf_len() magical. . . l oci_buf_len($string) n returns two element list containing $string and length of $string – nothing magical there, but. . . l oci_buf_len($string, $max_len) n first ‘grows’ underlying buffer of $string to $max_len, if needed n then returns $string and a magical copy of $max_len n the magical $max_len is ‘entangled’ with $string l When $max_len is read, it returns the current buffer size of $string When $max_len is set, it sets the length of the contents of $string l 25

Perl for Oracle © Tim Bunce Jan 2002 Building the beast l OCI 8. 1 API has approximately… n n l 170 typedefs 530 functions 1000 macros and more added with each Oracle release! Oracle: : OCI does not try to hardcode/handcode all those! n n The build process parses the Oracle header files in your own installation Then generates the Perl XS interface definition file to match – using a customized version of h 2 xs with the C: : Scan module n The XS file then translated into C code and compiled – it’s big! (~17, 000 lines of XS expanding to ~24, 000 lines of C) n n Installer can choose which OCI functions to include Use of code generation should make porting to Perl 6 relatively simple 26

Perl for Oracle © Tim Bunce Jan 2002 Generating the code l Example OCI function definition sword OCIColl. Size( OCIEnv *env, OCIError *err, CONST OCIColl *coll, sb 4 *size ); l Corresponding generated Perl XS sword_status OCIColl. Size(env, err, coll, size) OCIEnv * env OCIError * err OCIColl * coll sb 4 &size OUTPUT: size l Note: n n n integer size pointer automatically changed to ‘address of’ interface style using ‘&’ and automatically added to OUTPUT section return type changed to sword_status to enable typemap to generate check/trace code 27

Perl for Oracle © Tim Bunce Jan 2002 Generating the code l Corresponding generated C code XS(XS_Oracle__OCIColl. Size) { d. XSARGS; if (items != 4) Perl_croak(a. THX_ "Usage: Oracle: : OCIColl. Size(env, err, coll, size)"); { OCIEnv * env = ora_getptr_OCIEnv. Ptr(ST(0), "env", "OCIEnv. Ptr", "OCIColl. Size"); OCIError * err = ora_getptr_OCIError. Ptr(ST(1), "err", "OCIError. Ptr", "OCIColl. Size"); OCIColl * coll = ora_getptr_OCIColl. Ptr(ST(2), "coll", "OCIColl. Ptr", "OCIColl. Size"); sb 4 size = (sb 4)Sv. IV(ST(3)); sword_status RETVAL; RETVAL = OCIColl. Size(env, err, coll, &size); sv_setiv(ST(3), (IV)size); Sv. SETMAGIC(ST(3)); ST(0) = sv_newmortal(); if (RETVAL != OCI_SUCCESS || DBIS->debug) { warn(" %s returned %s", "OCIColl. Size", oci_status_name(RETVAL)); } sv_setiv(ST(0), (IV)RETVAL); } XSRETURN(1); } 28

Perl for Oracle © Tim Bunce Jan 2002 Getting started l The prerequisites n n l Oracle 8 Perl 5. 6 The DBI and DBD: : Oracle modules The C: : Scan and Data: : Flow modules The (un)complicated build process n n run a single command does everything except install 29

What’s new or planned? or just imagined on dark and stormy nights. . .

Perl for Oracle © Tim Bunce Jan 2002 A “work in progress” l Ongoing development n n n Removal of the need for most scaffolding code Tighter integration with the DBI and DBD: : Oracle Explore and validate more of the OCI API via expanded test scripts – currently working on OCIDescribe. Any() and related metadata stuff l Volunteers most welcome! n n n Get involved… join the mailing list (details at end) Test the build system with your Oracle version on your platform Tell me what you’d like to use it for – so I can prioritise development 31

Perl for Oracle © Tim Bunce Jan 2002 Beyond Oracle: : OCI l Firstly, new modules layered on top of Oracle: : OCI n providing simpler abstract API, more ‘perl-like’ n each focused on a specific area of functionality – Oracle: : LOB – Oracle: : Direct. Path – Oracle: : Collection – Oracle: : Describe – Oracle: : Transaction – … l Secondly, . . . 32

Perl for Oracle © Tim Bunce Jan 2002 Oracle: : PLSQL? l Auto-generate Perl proxy interfaces for PL/SQL packages and functions – Invoke a PL/SQL function simply by calling a perl sub of the same name! use DBI; $dbh = DBI->connect(’dbi: Oracle: ’, $user, $pass, { ora_autolob => 0 }); $bfile = $dbh->selectcol_array(”select bfile from mylobs where id=? for update”, undef, 1); use Oracle: : PLSQL; $dbms_lob = new Oracle: : PLSQL DBMS_LOB => $dbh; # Magic $dbms_lob->fileexists($bfile) or die “File missing”; # More magic via AUTOLOAD $length = $dbms_lob->filelength($bfile); $dbms_lob->filegetname($bfile, $diename, $filename); $dbms_lob->fileopen($bfile, $dbms_lob->{file_readonly}); $dbms_lob->read($bfile, 40, 1, $buffer); $dbms_lob->fileclose($bfile); – IN, OUT, and IN OUT params of all types work as expected, including polymorphism – PL/SQL exceptions map to Perl exceptions – Would work for any PL/SQL package - including your own! 33

Perl for Oracle © Tim Bunce Jan 2002 Oracle: : PLSQL? l l Brings the server ‘closer’ to the client - “Bridges the gap” What’s the niche? n n n Perl code that needs closer interaction with PL? SQL on the server PL/SQL that needs closer interaction with the client or that needs access to functionality in Perl – regular expressions, CPAN modules, – transparent UTL_FILE client<->server file handles! n may have many uses in the management of – Replication and Standby databases – Server monitoring, and gathering and processing performance statistics – DBMS_DEBUG - build custom debug/tracing/logging tools – Advanced Queuing l Currently a figment of my fevered imagination – but maybe not for much longer 34

Perl Inside Oracle Well, almost. . .

Perl for Oracle © Tim Bunce Jan 2002 Extending Oracle Dynamically l Oracle now supports loading and calling shared libraries (DLLs) on the server l For example: CREATE OR REPLACE LIBRARY MY_LIB IS '/path/to/library. so'; CREATE OR REPLACE FUNCTION my_example_func ( x LONG, y UNSIGNED SHORT ) RETURN DOUBLE AS LANGUAGE C LIBRARY MY_LIB NAME ”my_example_func"; SELECT my_example_func(foo, bar) FROM table; l And now Jeff Horwitz has applied this to perl with his ‘extproc_perl’ module 36

Perl for Oracle © Tim Bunce Jan 2002 Linking to perl l After building and installing ‘extproc_perl’ you can do: CREATE OR REPLACE LIBRARY PERL_LIB IS '/path/to/extproc_perl. so'; CREATE OR REPLACE FUNCTION perl ( sub IN VARCHAR 2, arg 1 in VARCHAR 2 default NULL, arg 2 in VARCHAR 2 default NULL, arg 3 in VARCHAR 2 default NULL, dummy in VARCHAR 2 default NULL) RETURN STRING AS EXTERNAL NAME "ora_perl_sub” LIBRARY "PERL_LIB” WITH CONTEXT PARAMETERS ( CONTEXT, RETURN INDICATOR BY REFERENCE, sub string, arg 1 INDICATOR short, arg 2 string, arg 2 INDICATOR short, arg 3 string, arg 3 INDICATOR short, dummy string, dummy INDICATOR short); 37

Perl for Oracle © Tim Bunce Jan 2002 Calling perl from Oracle l The perl() function is now an entry point into perl from Oracle l The first parameter is the name of the perl sub to call select perl('mysub') from dual; l Up to three additional parameters are passed to the sub (Easily increased up to 128 if needed) l The return value from the sub is returned as a string to Oracle l A boot script is executed when the perl interpreter is started to pre-load handy modules etc. 38

Perl for Oracle © Tim Bunce Jan 2002 But! l It’s not really inside the Oracle server, it’s an external process – so higher latency – but still much lower than network latency l Library can be unloaded by Oracle at any time – so perl interpreter is not guaranteed persistent for session – but that can be worked around l Can’t dynamically load perl extensions – but can statically link them in advance – and can load any pure-perl modules 39

Perl for Oracle © Tim Bunce Jan 2002 But, it’s still very useful. . . l Especially for n Perl based data processing (formatting, filtering etc. ) – regexes, regexps, regexen – pack / unpack etc. – Crypt: : * modules – Internet access n “Function-based indices” (such as a custom hash function) – but inserts and updates can get significantly slower n And… – Probably many more things we haven’t thought of yet. . . 40

Perl for Oracle © Tim Bunce Jan 2002 A small example l CREATE OR REPLACE FUNCTION stock_quote (symbol in VARCHAR 2) RETURN VARCHAR 2 IS price VARCHAR 2(8); BEGIN SELECT perl(’stock_quote', symbol) into price FROM dual; RETURN price; END; l use Finance: : Quote; sub stock_quote { my $sym = shift; my $q = Finance: : Quote->new(); my %h = $q->yahoo($sym); return $h{$sym, 'price'}; } l SQL> SELECT stock_quote('ORCL') as price FROM dual; PRICE ------------14. 38 41

Perl for Oracle © Tim Bunce Jan 2002 What next. . . l Efficiency enhancements – to reduce latency as far as possible l Tighter integration with DBD: : Oracle and Oracle: : OCI – to get fast access to “current” database handle l Store perl code inside Oracle tables – using CODE ref in @INC l And… – probably many more things we haven’t thought of yet. . . 42

Perl for Oracle © Tim Bunce Jan 2002 Reference Materials l This presentation n l Oracle: : OCI n n l http: //www. perl. com/CPAN/authors/id/TIMB/Oracle. Perl. Talk_2002. tar. gz Oracle Call Interface Programmer's Guide - A 67846 -01 Oracle 8 i Application Developer's Guide - Fundamentals - A 68003 -01 http: //www. perl. com/CPAN/authors/id/TIMB/OCI_Talk 1_2001. tar. gz mailto: oracle-oci-help@perl. org Perl DBI n http: //dbi. perl. org/ – n n n http: //www. perl. com/CPAN/authors/id/TIMB/DBI_Intro. Talk_2002. tar. gz http: //www. perl. com/CPAN/authors/id/TIMB/DBI_Advanced. Talk_2002. tar. gz http: //www. oreilly. com/catalog/perldbi/ – or http: //www. amazon. com/exec/obidos/ASIN/1565926994/dbi – l the DBI Home Page Programming the Perl DBI - The DBI book! Extproc_perl n http: //search. cpan. org/search? mode=dist&query=extproc_perl 43

The end Till next time…