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.
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 commandOCIexecute("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
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.
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.
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:
- to execute the block in debug mode (to display what SQL queries are produced),
- 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.
- to set the size of a text column (default 64 characters),
- 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 email@example.com.
© Copyright 2001-2013 Fair Isaac Corporation. All rights reserved.