Perl for Oracle

1. Perl for Oracle Tools and Technologies Tim Bunce Jan 2002

2. Topical Topics • DBD::Oracle • Hints and tips • Oracle’s OCI • The Oracle API • Oracle::OCI • The new big thing • Perl Inside Oracle • The new way

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

4. Making the connection • Typical connection $dbh = DBI->connect(”dbi:Oracle:tnsname”, …) $dbh = DBI->connect(”dbi:Oracle:”, …) • 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 • 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”, …) • Associate a name with the session $dbh = DBI->connect(”dbi:Oracle:”, …, { ora_module_name => $0 })

5. Specifying bind types • By default DBD::Oracle binds everything as strings • including numbers • Explicitly specifying Oracle-specific bind types can be useful for • 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)

6. Returning cursors as values • 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 $sth2, 0, { ora_type=>ORA_RSET } ); $sth->execute; my $data = $sth2->fetchall_arrayref; • But... • Not all ways to generate cursors are supported yet (probably simple to add) • The returned cursor currently needs to be explicitly closed like this: $sth3 = $dbh->prepare("BEGIN CLOSE :cursor END"); $sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET } ); $sth3->execute; • Which neatly demonstrates that you can send cursors back to the server as well

7. Bind RETURNING values • 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; • But... • Currently only works for a single value (i.e., update only updated one row) • but I’ll be fixing that soon

8. Tuning the row cache • Oracle OCI supports a transparent client-side row cache • By default it’s just two rows • halves network round-trips, especially good when selecting a single row • DBD::Oracle goes a little further… • 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 • Good, but not ideal if • 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 • $dbh->{RowCache} = $n; • Where $n > 0 specifies the number of rows, and $n < 0 specifies the memory to use

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

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

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

12. Oracle::OCI Making OCI practical

13. So what is Oracle::OCI? • Simply… A Perl module that makes the full OCI API available in Perl • 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 • An example...

14. Pure Oracle::OCI - attach to server • 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, 0, 0); my $envhp = new_ptr('OCIEnvPtr'); # OCIEnvInit($envhp, OCI_DEFAULT, 0, 0); • Allocate Error and Server handles: OCIHandleAlloc($$envhp, my $errhp=0, OCI_HTYPE_ERROR, 0, 0); $errhp = new_ptr('OCIErrorPtr', $errhp); # OCIHandleAlloc($$envhp, my $svrhp=0, OCI_HTYPE_SERVER, 0, 0); $svrhp = new_ptr('OCIServerPtr', $svrhp); # • Attach to the server: OCIServerAttach($svrhp, $errhp, oci_buf_len(”tnsname”), OCI_DEFAULT);

15. Pure Oracle::OCI - login to server • Allocate Service Context handle and associate it with the Server handle: OCIHandleAlloc($$envhp, my $svchp=0, OCI_HTYPE_SVCCTX, 0, 0); $svchp = new_ptr('OCISvcCtxPtr', $svchp); # OCIAttrSet($$svchp, OCI_HTYPE_SVCCTX, $$svrhp, 0, OCI_ATTR_SERVER, $errhp); • Allocate Session handle, set username/password, and login to the server: OCIHandleAlloc($$envhp, my $authp=0, OCI_HTYPE_SESSION, 0, 0); $authp = new_ptr('OCISessionPtr', $authp); # OCIAttrSet($$authp, OCI_HTYPE_SESSION, oci_buf_len($user), OCI_ATTR_USERNAME, $errhp); OCIAttrSet($$authp, OCI_HTYPE_SESSION, oci_buf_len($pass), OCI_ATTR_PASSWORD, $errhp); OCISessionBegin($svchp, $errhp, $authp, OCI_CRED_RDBMS, OCI_DEFAULT); OCIAttrSet($$svchp, OCI_HTYPE_SVCCTX, $$authp, 0, OCI_ATTR_SESSION, $errhp); • Get an OCI attribute from an OCI handle: OCIAttrGet($$handle, OCI_HTYPE_handle, my $attrib_value, 0, OCI_ATTR_name, $errhp);

16. Pure Oracle::OCI - example • Very little ‘excess’ code beyond the raw OCI calls • 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 • We’ll look at the gory details of how Perl maps to the OCI calls later • Two pages of OCI code required just to login to Oracle! • 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...

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

18. Season with a little DBI… • Do all the previous work the easy way - with just two lines of code: use DBI; $dbh = DBI->connect(”dbi:Oracle:”, $user, $password); • Get an OCI attribute from a DBI handle: use DBI; use Oracle::OCI qw(:all); $dbh = DBI->connect(”dbi:Oracle:”, $user, $password); OCIAttrGet($dbh, OCI_HTYPE_handle, my $attrib_value, 0, OCI_ATTR_name, $dbh); • That’s it! • 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

19. Handling large objects • 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 ); • then play with it using Oracle::OCI OCILobGetLength($dbh, $dbh, $lob_locator, my $lob_len=0); OCILobTrim($dbh, $dbh, $lob_locator, $lob_len - 2); • and fetch, edit, and update some bytes in the middle my ($offset, $amount, $buffer) = ($lob_len/2, 44, ’’); OCILobRead($dbh, $dbh, $lob_locator,$amount, $offset, oci_buf_len($buffer, 200, \$amount), 0,0, 0,0 ); $buffer =~ s/ATGC/ACTG/g; OCILobWrite($dbh, $dbh, $lob_locator, $amount, $offset,oci_buf_len($buffer), OCI_ONE_PIECE, 0,0, 0, 1 );

20. A picture is worth? Perl Application Oracle::OCI DBI DBD::Oracle Oracle Server

21. Why should I use Oracle::OCI? • It brings together the best tools … • The power of the OCI API • The power of Perl language and extensive module library • Plus … • 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 • All of which leads to rapid application development • And… it’s fun!

22. Oracle::OCI - the guts Making it happen

23. The basics • Primary goals • 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!) • 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 • Output parameters in OCI are output parameters in Perl • parameter values are updated ‘in place’ (without refs) • A handle is represented as a reference to an integer holding a pointer • Gives efficient access • The integer is blessed into a class for type safety and extra functionality • Using $$foo bypasses the type check

24. Handling buffers • 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 • What about returning buffers/strings from OCI? • Consider: OCIAttrGet( ... , 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?

25. Quantum entanglement? • Make oci_buf_len() magical... • oci_buf_len($string) • returns two element list containing $string and length of $string • nothing magical there, but... • oci_buf_len($string, $max_len) • first ‘grows’ underlying buffer of $string to $max_len, if needed • then returns $string and a magical copy of $max_len • the magical $max_len is ‘entangled’ with $string • 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

26. Building the beast • OCI 8.1 API has approximately… • 170 typedefs • 530 functions • 1000 macros • and more are added with each Oracle release! • Oracle::OCI does not try to hardcode/handcode all those! • 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 h2xs with the C::Scan module • 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) • Installer can choose which OCI functions to include • Use of code generation should make porting to Perl6 relatively simple

27. Generating the code • Example OCI function definition sword OCICollSize( OCIEnv *env, OCIError *err, CONST OCIColl *coll, sb4 *size ); • Corresponding generated Perl XS sword_status OCICollSize(env, err, coll, size) OCIEnv * env OCIError * err OCIColl * coll sb4 &size OUTPUT: size • Note: • 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

28. Generating the code • Corresponding generated C code XS(XS_Oracle__OCI_OCICollSize) { dXSARGS; if (items != 4) Perl_croak(aTHX_ "Usage: Oracle::OCI::OCICollSize(env, err, coll, size)"); { OCIEnv * env = ora_getptr_OCIEnvPtr(ST(0), "env", "OCIEnvPtr", "OCICollSize"); OCIError * err = ora_getptr_OCIErrorPtr(ST(1), "err", "OCIErrorPtr", "OCICollSize"); OCIColl * coll = ora_getptr_OCICollPtr(ST(2), "coll", "OCICollPtr", "OCICollSize"); sb4 size = (sb4)SvIV(ST(3)); sword_status RETVAL; RETVAL = OCICollSize(env, err, coll, &size); sv_setiv(ST(3), (IV)size); SvSETMAGIC(ST(3)); ST(0) = sv_newmortal(); if (RETVAL != OCI_SUCCESS || DBIS->debug) { warn(" %s returned %s", "OCICollSize", oci_status_name(RETVAL)); } sv_setiv(ST(0), (IV)RETVAL); } XSRETURN(1); }

29. Getting started • The prerequisites • Oracle 8 • Perl 5.6 • The DBI and DBD::Oracle modules • The C::Scan and Data::Flow modules • The (un)complicated build process • run a single command • does everything except install

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

31. A “work in progress” • Ongoing development • 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 OCIDescribeAny() and related metadata stuff • Volunteers most welcome! • 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

32. Beyond Oracle::OCI • Firstly, new modules layered on top of Oracle::OCI • providing simpler abstract API, more ‘perl-like’ • each focused on a specific area of functionality • Oracle::LOB • Oracle::DirectPath • Oracle::Collection • Oracle::Describe • Oracle::Transaction • … • Secondly, ...

33. Oracle::PLSQL? • 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!

34. Oracle::PLSQL? • Brings the server ‘closer’ to the client - “Bridges the gap” • What’s the niche? • 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! • 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 • Currently a figment of my fevered imagination • but maybe not for much longer

35. Perl Inside Oracle Well, almost...

36. Extending Oracle Dynamically • Oracle now supports loading and calling shared libraries (DLLs)on the server • 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 DOUBLEAS LANGUAGE C LIBRARY MY_LIB NAME ”my_example_func"; SELECT my_example_func(foo, bar) FROM table; • And now Jeff Horwitz has applied this to perl with his ‘extproc_perl’ module

37. Linking to perl • 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 VARCHAR2, arg1 in VARCHAR2 default NULL, arg2 in VARCHAR2 default NULL, arg3 in VARCHAR2 default NULL, dummy in VARCHAR2 default NULL) RETURN STRING AS EXTERNAL NAME "ora_perl_sub” LIBRARY "PERL_LIB” WITH CONTEXT PARAMETERS ( CONTEXT, RETURN INDICATOR BY REFERENCE, sub string, arg1 string, arg1 INDICATOR short, arg2 string, arg2 INDICATOR short, arg3 string, arg3 INDICATOR short, dummy string, dummy INDICATOR short);

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

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

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

41. A small example • CREATE OR REPLACE FUNCTION stock_quote (symbol in VARCHAR2)RETURN VARCHAR2 IS price VARCHAR2(8);BEGIN SELECT perl(’stock_quote',symbol) into price FROM dual; RETURN price;END; • use Finance::Quote;sub stock_quote { my $sym = shift; my $q = Finance::Quote->new(); my %h = $q->yahoo($sym); return $h{$sym,'price'};} • SQL> SELECT stock_quote('ORCL') as price FROM dual;PRICE------------------------14.38

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

43. Reference Materials • This presentation • http://www.perl.com/CPAN/authors/id/TIMB/OraclePerlTalk_2002.tar.gz • Oracle::OCI • Oracle Call Interface Programmer's Guide - A67846-01 • Oracle8i Application Developer's Guide - Fundamentals - A68003-01 • http://www.perl.com/CPAN/authors/id/TIMB/OCI_Talk1_2001.tar.gz • mailto:oracle-oci-help@perl.org • Perl DBI • http://dbi.perl.org/ • the DBI Home Page • http://www.perl.com/CPAN/authors/id/TIMB/DBI_IntroTalk_2002.tar.gz • http://www.perl.com/CPAN/authors/id/TIMB/DBI_AdvancedTalk_2002.tar.gz • http://www.oreilly.com/catalog/perldbi/ • orhttp://www.amazon.com/exec/obidos/ASIN/1565926994/dbi • Programming the Perl DBI - The DBI book! • Extproc_perl • http://search.cpan.org/search?mode=dist&query=extproc_perl

44. The end Till next time…