Oracle::OCI

1. Oracle::OCI Database Discovery - GeekCruise June 2001 Tim Bunce

2. Topical Topics • Introduction to the Oracle Call Interface • Advantages / Functionality • Introduction to Perl • Duck Tape and Glue • The Oracle::OCI Perl module • Pure Oracle::OCI • Integrating the DBI • Inside Oracle::OCI • Mapping the OCI API • Building the beast • The future • My plans • Your input

3. Who is this guy? • Technical Director of the Ingram Group, in the UK, for many years • Custom software system development for British Telecom and others • Active contributor to Perl development for many years • DynaLoader, MakeMaker, Exporter, ‘The Module List’ etc • Responsible for Perl releases 5.4.1 thru 5.4.4 • Designed and developed the Perl DBI and DBD::Oracle modules • Co-author of Programming the Perl DBI (available in 6 languages) • Now an independent consultant Tim.Bunce@data-plan.com or Tim.Bunce@pobox.com

4. The Oracle Call Interface O … C … I

5. So what is the Oracle OCI? • The Oracle Call Interface (OCI) is an application programming interface (API) that allows you to create applications that use procedural function calls to access an Oracle database server and control all phases of SQL statement execution. • OCI provides: • Improved performance and scalability through efficient use of system memory and network connectivity • Consistent interfaces for dynamic session and transaction management in a two-tier client-server or multi-tier environment • N-tiered authentication • Comprehensive support for application development using Oracle objects • Ability to develop applications that service an increasing number of users and requests without additional hardware investments

6. Advantages of OCI (versus Pro*C etc) • More fine-grained control over all aspects of the application design and execution • Use of familiar 3GL programming techniques and application development tools such as browsers and debuggers • Dynamic • Full support for dynamic SQL • Dynamic bind and define using callbacks • Describe functionality to expose layers of server metadata • Asynchronous event notification for registered client applications • Enhanced array data manipulation language (DML) capability for array INSERTs, UPDATEs, and DELETEs • Thread safety so you do not have to use mutual exclusive locks (mutex) on OCI handles

7. OCI functionality - in general • Relational functions • for managing database access and processing SQL statements • Datatype mapping and manipulation functions • for manipulating data attributes of Oracle types • Navigational functions • for manipulating objects retrieved from an Oracle database server • External procedure functions • for writing server-side C callbacks from PL/SQL

8. OCI functionality - some specifics • Read and write LOBs in chunks • Including streaming LOBs to or from the database via callbacks. • Create and manipulate collections, iterators, user defined types • variable-length arrays, nested tables, etc • Have multiple processes share the same transaction • Have multiple users share the same database connection • Non-blocking mode for OCI function calls • High speed bulk loading via Arrays or Direct Path

9. OCI functionality - more specifics • Pin objects in the cache • Use Oracle’s data manipulation and formatting facilities • dates, numbers, character set conversions, etc • Advanced Queuing • Including Publish / Subscribe event notifications • Managed automatic fail-over with Parallel Server

10. Best of both worlds • The Oracle Call Interface (OCI) allows you to develop applications that combine the non-procedural data access power of SQL with the procedural capabilities of programming languages such as C • The OCI supports all the facilities that are available through an Oracle database server • The applications you develop with OCI can be more powerful and flexible than applications written in SQL or PL/SQL alone

11. Perl The practical language of choice

12. Why Perl? • A practical language • emphasis on practical over academic • the discipline of freedom vs. discipline and bondage • Fewer lines of code • a rich language with powerful idiomatic constructs • faster development / fewer errors / easier maintenance • “A picture is worth a thousand words” vs. “Can’t see the wood for the trees” • Freedom of expression • “There’s more than one way to do it” - elegance of expression • My poetry may not be Perl, but I hope my Perl is poetry • The tool builders tool factory • Hundreds of ready to use modules available

13. Why Perl for databases? • Ideally suited to data processing • parsing / cleansing / filtering / merging / generating / formatting / etc … • Simple powerful and portable database interface • The DBI module and database driver mdules, including DBD::Oracle • Applications • Data acquisition and reporting • Data Migration • Web Page creation • Building sharper tools faster • The choice of many experienced developers • Extensive module library - growing at an ever faster rate • Many powerful add-on modules for databases and the DBI

14. Oracle::OCI Making OCI practical

15. 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...

16. 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, 0, 0, OCI_DEFAULT);

17. 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, add username/password and login to the server: OCIHandleAlloc($$envhp, my $authp=0, OCI_HTYPE_SESSION, 0, 0); $authp = new_ptr('OCISessionPtr', $authp); # my ($user, $pass) = split /\//, $dbuser; OCIAttrSet($$authp, OCI_HTYPE_SESSION, dvoid_p_str($user), OCI_ATTR_USERNAME, $errhp); OCIAttrSet($$authp, OCI_HTYPE_SESSION, dvoid_p_str($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);

18. Pure Oracle::OCI - example • Notes • Two pages of OCI code required to login to Oracle • Very little ‘excess’ code is required beyond the raw OCI calls • The gray colored lines in the previous examples (with a # at the end) are temporary ‘scaffolding’ that will not be required in future releases (mostly) • The same (possibly) applies to the double dollars seen in some places

19. Oracle::OCI+DBI/DBD::Oracle Making OCI easy

20. Season with a little DBI… • Load the modules, then do all the previous work the easy way: use DBI; $dbh = DBI->connect(”dbi:Oracle:”, $user, $password); • Get an OCI attribute from an DBI handle: use Oracle::OCI qw(:all); 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 handles • The Oracle::OCI module works with DBD::Oracle to extract whichever handle is needed

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

22. 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!

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

24. Mapping the OCI API to Perl #1 • Primary goals • Change the API as little as possible • Oracle OCI documentation should also be Oracle::OCI documentation! • 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 • 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 • A work in progress • some things may change

25. Mapping the OCI API to Perl #2 • Minor inconveniences • OCI functions that take string + string length pairs • would need OCIFoo(…, $string, length($string), …); • maybe do OCIFoo(…, ocistrlen($string), …); which would return both values for you as a convenience • More tricky issues • OCI functions that want to write to a pre-allocated buffer and update a separate parameter with the output length • I think I have a neat solution to this • OCI functions with void* output parameters such as OCIAttrGet() • need to know what class to bless them into - use a lookup table

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! • Installer can choose which OCI functions to include

27. Generating the code • Example OCI function definition sword OCICollSize( OCIEnv *env, OCIError *err, CONST OCIColl *coll, sb4 *size ); • Corresponding generated Perl XS sword OCICollSize(env, err, coll, size) OCIEnv * env OCIError * err OCIColl * coll sb4 &size OUTPUT: size

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 RETVAL; dXSTARG; RETVAL = OCICollSize(env, err, coll, &size); sv_setiv(ST(3), (IV)size); SvSETMAGIC(ST(3)); XSprePUSH; PUSHi((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

30. And finally... What’s new or planned?

31. A work in progress • Rapid ongoing development • Removal of the need for most scaffolding code • Tighter integration with the DBI and DBD::Oracle • Next release should be in under two weeks • then probably one more release before the Perl Conference in July 2001 • 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::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! • IN, OUT, and IN OUT params of all types work as expected, inc polymorphism • PL/SQL exceptions map to Perl exceptions 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); • 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? • PL/SQL that needs to interact with the client • or that needs access to functionality in Perl • regular expressions, CPAN modules, • transparent UTL_FILE client<->server file handles • DBA may have many uses in the management of • Advanced Queuing • Replication • Standby databases • Gathering and processing performance stats • DBMS_DEBUG - build custom tools • Currently a figment of my fevered imagination • but maybe not for much longer

35. Reference Materials • 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 • this Oracle::OCI tutorial • mailto:oracle-oci-help@perl.org • for details of the mailing list • Perl DBI • http://dbi.perl.org/ • the DBI Home Page • http://www.perl.com/CPAN/authors/id/TIMB/DBI_Intro_2001.tar.gz • http://www.perl.com/CPAN/authors/id/TIMB/DBI_Talk5_2001.tar.gz • DBI tutorials for Database Discovery (intro) and The Perl Conference 5.0 (advanced) • http://www.oreilly.com/catalog/perldbi/ • or http://www.amazon.com/exec/obidos/ASIN/1565926994/dbi • Programming the Perl DBI - The DBI book!

36. The end Till next time… p.s. I’m also giving seminars at the Perl Conference, July 2001, and on the Perl Whirl cruise, January 2002