External LOBs

External LOBs are stored as files outside the database and do not participate in the transactional model. External LOBs are stored as BFILE datatype, which is actually a pointer to a file.

External Procedures

Oracle8 supports the calling of DLL functions and procedures from PL/SQL code. As a result, you can have data cartridges whose methods are external C routines. By using external C routines, developers can take advantage of the efficiency of a 3GL, and can use the flexibility of calling Win32 APIs and manipulating COM objects.

There are two steps before a developer can call an external procedure from PL/SQL:

1.  Register the DLL’s location with Oracle8’s data dictionary:

   Create or replace library external_lib as

   ‘e:/datacartridge/debug/cartridge.dll’;

2.  Declare the prototype of the C routine in the Oracle8 data dictionary.

The following example shows how to implement an object type that uses an external procedure as a method:

Create or replace package data_package as

    function ext_func (data CLOB) return binary_integer;

end;

create or replace package body data_package as

    function ext_func (data CLOB) return binary_integer is external

name “c_func”

        library external_lib

        language C

        with context

        parameters (

            context,

            data OCILOBLOCATOR

        );

end;

create or replace type ext_objtype as object (

    data         CLOB,

    member function    ext_objtype_func return binary_integer

);

Create or replace type body ext_objtype is

    member function ext_objtype_func return binary_integer is

    begin

        return data_package.ext_func(data);

    end;

end;

The following C code shows the function prototype of the external function:

#Include <oci.h>

#define DLLEXPORT __declspec(dllexport) __cdecl

int DLLEXPORT c_func (OCIExtProcContext *ctx, OCILobLocator *lobl);

int c_func (OCIExtProcContext *ctx, OCILobLocator *lobl)

{

    /*Place function code here */

    return 0;

}

The following code shows the testing of the external callout using PL/SQL:

Declare

    i binary_integer;

    x ext_objtype;

begin

    x := ext_objtype(EMPTY_CLOB());

    I := x.ext_objtype_func();

    DBMS_OUTPUT.PUT_LINE(‘ext_objtype_func() returned ‘ || I);

end;

OCI Callbacks

In Release 8.0.3, Oracle introduced four new OCI APIs, which can be used for external procedure callbacks. External routines can call back the Oracle8 Server using the OCI interface:

•  OCIExtProcAllocCallMemory() Allocates n bytes of memory for the duration of the external procedure.

•  OCIExtProcRaiseExcp() Raises an Oracle error number.

•  OCIProcRaiseExcpWithMsg() Raises a user-defined exception.

•  OCIExtProcGetEnv() Retrieves a set of OCI handles that should only be used for callbacks.

An external procedure can perform the following functions using these OCI APIs:

•  Manipulate LOB data

•  Fetch and update data

•  Execute SQL and PL/SQL

Debugging External Procedures

Traditionally, a developer could use Visual C++’s symbolic debugger to set break points in a DLL loaded by a process without symbolic debugging information. External procedures cannot be debugged in this manner because debugging must be spawned on demand by the listener process. Embedding the DebugBreak() Win32 API call at the beginning of an external procedure will allow developers to debug these procedures.

Example: Step by Step Data Cartridge Development

The following details the development of a data cartridge “Data Store.” This cartridge can be used to store a set of data in an Oracle8 character LOB (CLOB). The stored data can be processed for statistical analysis such as minimum and maximum values, averages, regression, and so on.

1.  Create an object type containing attributes and methods that would define the “Data Store” and its functionality.
The methods are declared as external because the kind of processing that would be supported by this cartridge would be most efficiently performed in PL/SQL, and therefore the methods should be coded in a 3GL like C or C++ and dispatched from the server. C is more efficient for external procedures.

Create or replace type DataStore as object (

          pid integer,

          name varchar2(20),

          date_created date,

          value    clob,

          member function DataMinimum return integer,

          member function DataMaximum return integer,

          pragma  restrict_references(DataMinimum, WNDS, WNPS),

          pragma  restrict_references(DataMaximum, WNDS, WNPS));

2.  Declare a package to hold all the external procedures.

Create or replace package DataStore_package as

          function datastore_findmin (data clob) return integer;

          function datastore_findmax (data clob) return integer;

          pragma restrict_references (datastore_findmin, WNDS, WNPS);

          pragma restrict_references (datastore_findmin, WNDS, WNPS);

        end;

3.  Implement the body of object type DataStore.

Create or replace type body DataStore is

        member function DataMinimum return integer is

            x integer := DataStore_package.datastore_findmin(data);

            begin return x ; end;

        member function DataMaximum return integer is

            y integer := DataStore_package.datastore_findmax(data);

            begin return y ; end;

end;

4.  Create a PL/SQL name for the library in which the implementation of the external procedure will be found.

Create or replace library datastore_lib as ‘<directory_of_library> /

libdatastore.so’

5.  Declare the package body and tie the package functions to 3GL functions of the library.

Create or replace package body DataStore_package as

            function datastore_findmin(data clob) return integer is

external

            name “c_minimum” library datastore_lib language c with

context;

            function datastore_findmax(data clob) return integer is

external

            name “c_maximum” library datastore_lib language c with

context;

        end;

6.  Implement the necessary 3GL routines.
The CLOB passed as argument to the external procedure is used by the routine as a pointer to the LOB locator. It calls the database and reads the CLOB incrementally.

#Include <oci.h>

int c_minimum (OCIExtProcContext *ctx,  OCILobLocator  *lobl)   {

        ub1 bufp[MAXBUFLEN];

        sword  retval;

        init_handles (ctx);

        retval = OCILobRead(...., lobl, bufp, ....);

        return (process_min(bufp));

}

#Include <oci.h>

int c_maximum (OCIExtProcContext *ctx,  OCILobLocator  *lobl)   {

        ub1 bufp[MAXBUFLEN];

        sword  retval;

        init_handles (ctx);

        retval = OCILobRead(...., lobl, bufp, ....);

        return (process_max(bufp));

}