1 / 44

Perl for Oracle

Perl for Oracle. Tools and Technologies Tim Bunce. Jan 2002. 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. DBD::Oracle. Hints and tips… (the under-documented stuff). Making the connection.

tea
Download Presentation

Perl for Oracle

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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…

More Related