Perl for Oracle

1
Perl for Oracle

• Tools and Technologies
• Tim Bunce

Jan 2002
2
Topical Topics

• DBDOracle
• Hints and tips
• Oracles OCI
• The Oracle API
• OracleOCI
• The new big thing
• Perl Inside Oracle
• The new way

3
DBDOracle

• Hints and tips
• (the under-documented stuff)

4
Making the connection

• Typical connection
• dbh DBI-gtconnect(dbiOracletnsname, )
• dbh DBI-gtconnect(dbiOracle, )
• Connect in OPER or DBA mode
• dbh DBI-gtconnect(dbiOracle, ,
  ora_session_mode gt mode )
• Where mode is 2 for SYSDBA, or 4 for SYSOPER
• Without using a TNS name
• dbh DBI-gtconnect(dbiOracle(DESCRIPTION(ADD
  RESS_LIST(ADDRESS(foo.com)(PORT1526))) (CONNEC
  T_DATA(SIDORCL))), )
• dbh DBI-gtconnect(dbiOraclehostfoo.comsidO
  RCL, )
• Associate a name with the session
• dbh DBI-gtconnect(dbiOracle, ,
  ora_module_name gt 0 )

5
Specifying bind types

• By default DBDOracle 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 DBDOracle qw(ora_types)
• sth dbh-gtprepare(UPDATE tablename SET foo?
  WHERE bar?)
• sth-gtbind_param(1, dummy, ora_type gt
  ORA_CLOB )
• sth-gtbind_param(2, dummy, ora_type gt
  ORA_CHAR )
• sth-gtexecute(_at__) foreach (_at_updates)
• Note that the bound types are sticky so
  execute(_at_values) can be used
• Using TYPEgtSQL_CHAR would also work (and
  SQL_CLOB will soon)

6
Returning cursors as values

• You can return cursor objects
• sth dbh-gtprepare(BEGIN csr
  func_returning_cursor(arg) END)
• sth-gtbind_param(arg, arg)
• sth-gtbind_param_inout("csr", \my sth2, 0,
  ora_typegtORA_RSET )
• sth-gtexecute
• my data sth2-gtfetchall_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-gtprepare("BEGIN CLOSE cursor
  END")
• sth3-gtbind_param_inout("cursor", \sth2, 0,
  ora_type gt ORA_RSET )
• sth3-gtexecute
• Which neatly demonstrates that you can send
  cursors back to the server as well

7
Bind RETURNING values

• Oracles new RETURNING clause can also be used
• sth dbh-gtprepare(q
• UPDATE anothertable SET X? WHERE Y? RETURNING
  Z
• )
• sth-gtbind_param(1, x)
• sth-gtbind_param(2, y)
• sth-gtbind_param_inout(3, \z, 100)
• sth-gtexecute
• But...
• Currently only works for a single value (i.e.,
  update only updated one row)
• but Ill be fixing that soon

8
Tuning the row cache

• Oracle OCI supports a transparent client-side row
  cache
• By default its just two rows
• halves network round-trips, especially good when
  selecting a single row
• DBDOracle 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
  SQLNet protocol overheads
• Good, but not ideal if
• You are selecting lots of data and are happy to
  have a larger cache
• If youre query returns many rows but you only
  want the first few
• The estimated average row width isnt accurate
  (enable trace to see it)
• Can be tuned manually
• dbh-gtRowCache n
• Where n gt 0 specifies the number of rows, and n
  lt 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, theres more...

• High speed bulk loading via Arrays or Direct Path
  Loading
• Describe schema metadata in complete detail
• Use Oracles 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
OracleOCI

• Making OCI practical

13
So what is OracleOCI?

• Simply
• A Perl module that makes the full OCI API
  available in Perl
• But thats 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 DBDOracle
• An example...

14
Pure OracleOCI - attach to server

• Load the module and initialise the OCI and its
  Environment handle
• use OracleOCI 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 errhp0,
  OCI_HTYPE_ERROR, 0, 0)
• errhp new_ptr('OCIErrorPtr', errhp)
• OCIHandleAlloc(envhp, my svrhp0,
  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 OracleOCI - login to server

• Allocate Service Context handle and associate it
  with the Server handle
• OCIHandleAlloc(envhp, my svchp0,
  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 authp0,
  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 OracleOCI - 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
• Well 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 lets see how we can make it easier...

17
OracleOCIDBI/DBDOracle

• 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-gtconnect(dbiOracle, user,
  password)
• Get an OCI attribute from a DBI handle
• use DBI
• use OracleOCI qw(all)
• dbh DBI-gtconnect(dbiOracle, user,
  password)
• OCIAttrGet(dbh, OCI_HTYPE_handle, my
  attrib_value, 0, OCI_ATTR_name, dbh)
• Thats it!
• The dbh DBI handle holds the DBDOracle handle
  that, in turn, holds the OCI environment, error,
  service context and session handles
• The OracleOCI module asks DBDOracle to return
  whichever handle is needed

19
Handling large objects

• Fetch a LOB locator (not the contents) using
  the DBI
• my lob_locator dbh-gtselectrow_array(select
  my_lob from table_name where id1 for update,
  ora_auto_lob gt 0 return LOB locator not
  contents
• )
• then play with it using OracleOCI
• OCILobGetLength(dbh, dbh, lob_locator, my
  lob_len0)
• 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
DBI
OracleOCI
DBDOracle
Oracle Server
21
Why should I use OracleOCI?

• 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
• OracleOCI only needed for the more specialized
  code
• DBDOracle, via the DBI, takes care of the rest
• All of which leads to rapid application
  development
• And its fun!

22
OracleOCI - the guts

• Making it happen

23
The basics

• Primary goals
• Change the API as little as possible
• Oracle OCI documentation should also be
  OracleOCI documentation!
• (OCI reference manual1000 pages, associated
  guides800600800 pages!)
• Any changes made should consistently conform to a
  small set of rules
• So developers can translate the OCI API to the
  OracleOCI 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!
• OracleOCI 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
  CScan module
• The XS file then translated into C code and
  compiled
• its 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
  OracleOCIOCICollSize(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-gtdebug)
• warn(" s returned s", "OCICollSize",
  oci_status_name(RETVAL))

29
Getting started

• The prerequisites
• Oracle 8
• Perl 5.6
• The DBI and DBDOracle modules
• The CScan and DataFlow modules
• The (un)complicated build process
• run a single command
• does everything except install

30

Whats 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 DBDOracle
• 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 youd like to use it for
• so I can prioritise development

32
Beyond OracleOCI

• Firstly, new modules layered on top of
  OracleOCI
• providing simpler abstract API, more perl-like
• each focused on a specific area of functionality
• OracleLOB
• OracleDirectPath
• OracleCollection
• OracleDescribe
• OracleTransaction
• Secondly, ...

33
OraclePLSQL?

• 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-gtconnect(dbiOracle, user, pass,
  ora_autolob gt 0 )
• bfile dbh-gtselectcol_array(select bfile from
  mylobs where id? for update, undef, 1)
• use OraclePLSQL
• dbms_lob new OraclePLSQL DBMS_LOB gt
  \dbh Magic
• dbms_lob-gtfileexists(bfile) or die File
  missing More magic via AUTOLOAD
• length dbms_lob-gtfilelength(bfile)
• dbms_lob-gtfilegetname(bfile, diename,
  filename)
• dbms_lob-gtfileopen(bfile, dbms_lob-gtfile_reado
  nly)
• dbms_lob-gtread(bfile, 40, 1, buffer)
• dbms_lob-gtfileclose(bfile)
• IN, OUT, and IN OUT params of all types work as
  expected, including polymorphism

34
OraclePLSQL?

• Brings the server closer to the client -
  Bridges the gap
• Whats 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 clientlt-gtserver 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!

• Its not really inside the Oracle server, its 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
• Cant dynamically load perl extensions
• but can statically link them in advance
• and can load any pure-perl modules

40
But, its 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 havent 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',symb
  ol) into price FROM dual RETURN priceEND
• use FinanceQuotesub stock_quote my sym
  shift my q FinanceQuote-gtnew() my h
  q-gtyahoo(sym) return hsym,'price'
• SQLgt SELECT stock_quote('ORCL') as price FROM
  dualPRICE------------------------14.38

42
What next...

• Efficiency enhancements
• to reduce latency as far as possible
• Tighter integration with DBDOracle and
  OracleOCI
• to get fast access to current database handle
• Store perl code inside Oracle tables
• using CODE ref in _at_INC
• And
• probably many more things we havent thought of
  yet...

43
Reference Materials

• This presentation
• http//www.perl.com/CPAN/authors/id/TIMB/OraclePer
  lTalk_2002.tar.gz
• OracleOCI
• 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
• mailtooracle-oci-help_at_perl.org
• Perl DBI
• http//dbi.perl.org/
• the DBI Home Page
• http//www.perl.com/CPAN/authors/id/TIMB/DBI_Intro
  Talk_2002.tar.gz
• http//www.perl.com/CPAN/authors/id/TIMB/DBI_Advan
  cedTalk_2002.tar.gz
• http//www.oreilly.com/catalog/perldbi/
• or http//www.amazon.com/exec/obidos/ASIN/15659269
  94/dbi
• Programming the Perl DBI - The DBI book!
• Extproc_perl

44
The end

• Till next time