mmoci



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.

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.

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 transfered 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
 1  2  5
 4  3  6

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

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 transfered 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, 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

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

Control parameters

The following parameters are defined by mmoci:

OCIautocommit
Enable/disable "commit on success" in OCI.
OCIbufsize
Data buffer size.
OCIcolsize
Maximum string length.
OCIconnection
Identification number of the active OCI connection.
OCIdebug
Enable/disable debug mode.
OCIndxcol
Indicate whether to use first columns as indices.
OCIrowcnt
Number of lines affected by the last SQL command.
OCIrowxfr
Number of lines transfered during the last SQL command.
OCIsuccess
Indicate whether the last SQL command succeeded.
OCIverbose
Enable/disable message printing by OCI.

All parameters can be accessed with the Mosel function getparam, and those that are not marked read-only in the list below may be set using the procedure setparam.

Example:

setparam("OCIverbose", true)    ! Enable message printing by OCI
csize:=getparam("OCIcolsize")   ! Get the maximum string length
setparam("OCIconnection", 3)    ! Select the connection number 3

Procedures and functions

This section lists in alphabetical order the functions and procedures that are provided by the mmoci module.

OCIcommit
Commit the current transaction.
OCIexecute
Execute an SQL command.
OCIlogoff
Terminate the active database connection.
OCIlogon
Connect to a database.
OCIreadinteger
Read an integer value from a database.
OCIreadreal
Read a real value from a database.
OCIreadstring
Read a string from a database.
OCIrollback
Roll back the current transaction.

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),
noindex
to indicate that only data (no indices) are transfered between the data source and Mosel. By default, the first columns of each table is interpreted as index values for the array to be transfered. 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 colmun `Weeks' 
                                ! of table `PARAMS'
 BPROF as "noindex;BPROFILE"    ! Initialize `BPROF' with table `BPROFILE' 
                                ! all columns being data (no indices)
end-initializations 


If you have any comments or suggestions about these pages, please send mail to support@fico.com.

© Copyright 2001-2013 Fair Isaac Corporation. All rights reserved.