4. Using Program Objects

This section describes the configuration and use of program objects from within Oracle.

Program objects are stand-alone executable programs which communicate via standard input and standard output using the "Pipetalk" protocol (See the Daylight Theory Manual for more details). The cartridge supports the ability to execute external program objects on the same machine as the Oracle server, and to communicate with the program objects in a robust, general way.

The table c$dcischem.progob is the dictionary of program objects available to the Oracle server. In order to use a program object, it must be defined in this dictionary table.

  SQL>  desc progob
   Name                                      Null?    Type
   --------------------------------------- -------- -------------------------
   NAME                                               VARCHAR2(60)
   PATH                                               VARCHAR2(4000)
   ARGS                                               VARCHAR2(4000)

For example, consider the clogp program object. The code for the clogp example is included in the CONTRIB subdirectory. It can be defined to the system with:

  SQL> insert into progob values
     ('clogp', '/oracle/progob/clogptalk.sh', NULL);

This defines a program object with the symbolic name 'clogp'. It corresponds to the absolute path: '/oracle/progob/clogptalk.sh', which must be an executable program object. In this case, the arguments to the program object (whitespace delimited) are NULL.

The file '/oracle/progob/clogptalk.sh' looks like the following:

  #!/bin/sh

  DY_ROOT=/usr/local/daylight/v481
  export DY_ROOT

  DY_LICENSEDATA=/usr/local/daylight/dy_license.dat
  export DY_LICENSEDATA

  LD_LIBRARY_PATH=/usr/lib:$DY_ROOT/lib
  export LD_LIBRARY_PATH

  $DY_ROOT/bin/clogptalk

When Oracle executes the program object, the environment is empty. Hence, all required environment variables must be defined in a shell wrapper before calling the actual program object.

Since program objects are executed under the Oracle userid, a strict security policy must be defined and obeyed to prevent access to unauthorized Oracle privileges. The policy, checked and enforced by the cartridge code before executing any external program object, is the following:

1. If the program object is executable and SUID, then it is OK. SUID requires privileges of the destination UID, so this means that whoever turned on the SUID bit has either root or owner privilege on the file.
2. If the program object is executable by and owned by Oracle, and the directory is not group or world writeable, then OK.
3. Otherwise, fail.

Program objects are persistant on the server and are executed on a per-user-session basis. The first time a program object is accessed within a user session it is started, and the program object will continue to run until the user session is terminated. If the program object crashes during the user session, the cartridge will restart it and retry the transaction. Because of the ability to restart program objects, program objects ideally should be stateless and communications to the program object from Daycart should assume that the program is stateless.

Program object communication through the ddpackage.fprogob() function is via VARCHAR2 or CLOB datatypes. The string types can contain one or more delimited lines of data. Because of this line-oriented data communication, most program objects will require a PL/SQL wrapper function to convert from Oracle datatypes to line-oriented data and to parse returned results. Continuing the clogp example, consider a simple function which will calculate clogp:

  create function fclogp (sosdata in varchar2) return number
   as
    v1 varchar2(4000);
    rc number;
    off1 number;
    off2 number;
   begin
    v1 := c$dcischem.ddpackage.fprogob('clogp', sosdata);
    off1 := instr(v1, ' ', 1, 1);
    off2 := instr(v1, ' ', 1, 2);
    rc := to_number(substr(v1, off1, off2 - off1));
    return rc;
   end;

Executing the fprogob() function directly using the clogptalk program object results in a full line of data being returned from the program object for each SMILES:

  SQL> select ddpackage.fprogob('clogp', 'c1ccccc1') from dual;

  DDPACKAGE.FPROGOB('CLOGP','C1CCCCC1')
  -----------------------------------------------------------------------------
  c1ccccc1 2.142 0 LogPstar: 2.13

The wrapper function fclogp() simply parses out the second field, converts it to a number, and returns it. The wrapper function hides the details from the user and allows the function to return the desired numeric value.

  SQL> select fclogp('c1ccccc1') from dual;

  FCLOGP('C1CCCCC1')
  ------------------
               2.142

More complex examples (eg. MOLFILE -> SMILES conversion) are included in the CONTRIB directory.