mmoci

Topics covered in this chapter:

The Mosel OCI (Oracle Call Interface) interface provides a set of procedures and functions that may be used to access Oracle databases. To use the OCI interface, the following line must be included in the header of a Mosel model file:

 uses 'mmoci'

This manual describes the Mosel OCI interface and shows how to use some standard PL/SQL commands, but it is not meant to serve as a manual for PL/SQL. The reader is referred to the documentation of Oracle for more detailed information on these topics.

Prerequisite

The Oracle interface defined by the module mmoci accesses Oracle databases via the Oracle Call Interface (OCI). Oracle's Instant Client package must be installed on the machine that runs the Mosel model.

Example

Assume that the Oracle database contains a table ``pricelist'' of the following form:

articlenum	color	price
1001	blue	10.49
1002	red	10.49
1003	black	5.99
1004	blue	3.99
...

The following small example shows how to logon to a database from an Mosel model file, read in data, and logoff from the database.

model 'OCIexample'
 uses 'mmoci'

 declarations
  prices: array (range) of real
 end-declarations

 setparam("OCIverbose", true)   ! Enable OCI message printing in case of error
 OCIlogon("scott","tiger","")   ! connect to Oracle as the user 'scott/tiger'

 writeln("Connection number: ", getparam("OCIconnection"))

 OCIexecute("select articlenum,price from pricelist", prices)
                                ! Get the entries of field `price' (indexed by
                                ! field `articlenum') in table `pricelist'

 OCIlogoff                      ! Disconnect from the database
end-model

Here the OCIverbose control parameter is set to true to enable OCI message printing in case of error. Following the connection, the procedure OCIexecute is called to retrieve entries from the field price (indexed by field articlenum) in the table pricelist. Finally, the connection is closed.

For further examples of working with databases and spreadsheets, the reader is referred to the Xpress whitepaper Using ODBC and other database interfaces with Mosel.

Data transfer between Mosel and Oracle

Data transfer beetween Mosel and Oracle is achieved by calls to the procedure OCIexecute. The value of the control parameter OCIndxcol and the type and structure of the second argument of the procedure decide how the data are transferred between the two systems.

From Oracle to Mosel

Information is moved from Oracle to Mosel when performing a SELECT command for instance. Assuming mt has been declared as follows:

mt: array(1..10,1..3) of integer

the execution of the call:

OCIexecute("SELECT c1,c2,c3 from T", mt)

behaves differently depending on the value of OCIndxcol. If this control parameter is true, the columns c1 and c2 are used as indices and c3 is the value to be assigned. For each row (i,j,k) of the result set, the following assignment is performed by mmoci:

mt(i,j):=k

With a table T containing:

c1 c2 c3 c4
 1  2  5  7
 4  3  6  8

We obtain the initialization:

m2(1,2)=5, m(4,3)=6

If the control parameter OCIndxcol is false, all columns are treated as data. In this case, for each row (i,j,k) the following assignments are performed:

mt(r,1):=i; mt(r,2):=j; mt(r,3):=k

where r is the row number in the result set.

Here, the resulting initialization is:

mt(1,1)=1, mt(1,2)=2, mt(1,3)=5
mt(2,1)=4, mt(2,2)=3, mt(2,3)=6

If the SQL statement selects 4 columns (instead of 3) as in:

OCIexecute("SELECT c1,c2,c3,c4 from T", mt)

and the control parameter OCIndxcol is false, the first column is used as the first array index while the remaining columns are treated as data. As a consequence, for each row (i,j,k,l) the following assignments are performed:

mt(i,1):=j; mt(i,2):=k; mt(i,3):=l

The resulting initialization is therefore:

mt(1,1)=2, mt(1,2)=5, mt(1,3)=7
mt(4,1)=3, mt(4,2)=6, mt(4,3)=8

The second argument of OCIexecute may also be a list of arrays. When using this version, the value of OCIndxcol is ignored and the first column(s) of the result set are always considered as indices and the following ones as values for the corresponding arrays. For instance, assuming we have the following declarations:

m1, m2: array(1..10) of integer

With the statement:

OCIexecute("SELECT c1,c2,c3 from T", [m1,m2])

for each row (i,j,k) of the result set, the following assignments are performed:

m1(i):=j; m2(i):=k

So, if we use the table T of our previous example, we get the initialization:

m1(1)=2, m1(4)=5
m2(1)=3, m2(4)=6

From Mosel to Oracle

Information is transferred from Mosel to Oracle when performing an INSERT command for instance. In this case, the way to use the Mosel arrays has to be specified by using parameters in the SQL command. These parameters are identified by their name in the expression. For instance in the following expression 3 parameters (:1, :2 and :3) are used:

INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)

mmoci expects that parameters are always named :n where n is the parameter number starting at 1 but does not impose any order (i.e. :3,:1,:2 is also valid) and a given parameter may be used several times in an expression. The command is then executed repeatedly as many times as the provided data allows to build new tuples of parameters. The initialization of parameters is similar to what is done for a SELECT statement.

Assuming mt has been declared as follows:

mt: array(1..2,1..3) of integer

and initialized with this assignment:

mt::[1,2,3,
     4,5,6]

the execution of the call:

OCIexecute("INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)",mt)

behaves differently depending on the value of OCIndxcol. If this control parameter is true, for each execution of the command, the following assignments are performed by mmoci:

':1':= i, ':2':= j, ':3':= mt(i,j)  

The execution is repeated for all possible values of i and j (in our example 6 times). The resulting table T is therefore:

c1 c2 c3
 1  1  1
 1  2  2
 1  3  3
 2  1  4
 2  2  5
 2  3  6

Note that mmoci uses the names of the parameters to perform an initialization and not their relative position. This property is particularly useful for UPDATE statements where the order of parameters needs to be changed. For instance, if we want to update the table T instead of inserting new rows, we can write:

OCIexecute("UPDATE T c3=:3 WHERE c1=:1, c2=:2",mt)

This command is executed exactly in the same way as the INSERT example above (i.e. we do not have ':3':=i, ':1':=j, ':2':=mt(i,j) as the order of appearance in the command suggests but ':1':=i, ':2':=j, ':3':=mt(i,j)).

The same functionality may also be used to reorder or repeat columns. With the same definition of the array mt as before and a 4-column table S in the database the execution of the command

OCIexecute("INSERT INTO S (c1,c2,c3,c4) VALUES (:1,:2,:3,:2)",mt)

results in the following contents of table S:

c1 c2 c3 c4
 1  1  1  1
 1  2  2  2
 1  3  3  3
 2  1  4  1
 2  2  5  2
 2  3  6  3

If the control parameter OCIndxcol is false, only the values of the Mosel array are used to initialize the parameters. So, for each execution of the command of our initial example (with 3 parameters), we have:

':1':=mt(i,1), ':2':=mt(i,2), ':3':=mt(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3
 1  2  3
 4  5  6

However if the SQL query defines 4 parameters (instead of 3) as in:

OCIexecute("INSERT INTO T (c1,c2,c3,c4) VALUES (:1,:2,:3,:4)",mt)

and the control parameter OCIndxcol is false, the first parameter is used as the first array index while the remaining parameters are populated with data. As a consequence, for each execution of the command, the following assignments are performed by mmoci:

':1':= i, ':2':= mt(i,1), ':3':= mt(i,2), ':4':=mf(i,3)

The execution is repeated for all possible values of i (in our example 2 times). The resulting table T is therefore:

c1 c2 c3 c4
 1  1  2  3
 2  4  5  6

When OCIexecute is used with a list of arrays, the behavior is again similar to what has been described earlier for the SELECT command: the first parameter(s) are assigned index values and the final ones the actual array values. For instance, assuming we have the following declarations:

m1,m2: array(1..3) of integer

And the arrays have been initialized as follows:

m1::[1,2,3]
m2::[4,5,6]

Then the following call:

OCIexecute("INSERT INTO T (c1,c2,c3) VALUES (:1,:2,:3)",[m1,m2])

executes 3 times the INSERT command. For each execution, the following parameter assignments are performed:

':1':=i, ':2':=m1(i), ':3':=m2(i)

The resulting table T is therefore:

c1 c2 c3
 1  1  4
 2  2  5
 3  3  6

I/O drivers

This module provides a driver designed to be used in initializations blocks for both reading and writing data. The oci IO driver simplifies access to Oracle databases.

Driver oci

oci:[debug;][noindex;][colsize=#;][bufsize=#;]logstring

The driver can only be used in `initializations' blocks. The database to use has to be given in the opening part of the block as user/password@dbname. Before this identifier, the following options may be stated:

debug

to execute the block in debug mode (to display what SQL queries are produced). This option is ignored if the model is not compiled with debug information.

noindex

to indicate that only data (no indices) are transferred between the data source and Mosel. By default, the first columns of each table are interpreted as index values for the array to be transferred. This behaviour is changed by this option.

colsize=c

to set the size of a text column (default 64 characters).

bufsize=c

to set the size of the data buffer in kilobytes (default 4).

In the block, each label entry is understood as a table name optionally followed by a list of column names in brackets (e.g. "my_table(col1,col2)"). All columns are used if no list of names is specified. Note that, before the table name, one can add option noindex to indicate that for this particular entry indices are not used.

Example:

initializations from "mmoci.oci:scott/tiger@orcl"
 NWeeks as "PARAMS(Weeks)"      ! Initialize `NWeeks' with column `Weeks'
                                ! of table `PARAMS'
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with table `BPROFILE'
                                ! all columns being data (no indices)
end-initializations