Oracle

The module mmoci defines the following subroutines for accessing Oracle databases via SQL commands.

OCIlogon, OCIlogoff:

Connect to a database / terminate the active connection.

OCIexecute:

Execute a PL/SQL command (select, insert, update, delete, create table, etc.).

OCIreadinteger, OCIreadreal, OCIreadstring:

Read an integer or real value, or a string from the database.

OCIcommit, OCIrollback:

Commit / roll back the current transaction (depending on setting of parameter OCIautocommit).

Please notice that there are some differences (other than the replacement of the prefix SQL by OCI) from the set of subroutines defined by module mmodbc: there is no separate 'update' procedure (data table updates can be formulated with OCIexecute), and an extra feature of this interface is the possibility to roll back transactions.

Data input with SQL statements

The following Mosel model corresponds to the model we have seen in Section Data input using odbc with the difference that we are now using SQL statements to read the data instead of an initializations block.

model "Duo input OCI (2)"
 uses "mmoci"

 declarations
  A5: dynamic array(range,range) of real
 end-declarations

! Use SQL statements to read the data
 OCIlogon("myname/mypassword@dbname")
 OCIexecute("select Index_i,Index_j,Value from MyDataTable", A5)
 OCIlogoff

! Print out the data we have read
 writeln('A5 is: ', A5)

end-model 

An alternative, equivalent formulation of the database logon statement uses three separate strings for the user name, password, and database name:

 OCIlogon("myname", "mypassword", "dbname") 

The SQL statement "select Index_i,Index_j,Value from MyDataTable" says `select columns Index_i, Index_j, and Value from the table called MyDataTable. If this table contains only these three columns and in the given order we might equally use the query "select * from MyDataTable" which says `select everything from the table MyDataTable'. In any case, we can work with exactly the same statement as in the ODBC version of this model since these are standard SQL queries. By using SQL statements directly in the Mosel model it is possible to have much more complex selection statements than the ones we have used here (see for instance the SQL queries formulated in Section SQL selection statements).

Data output with SQL statements

The example from Section Data output using odbc that outputs an array to an Oracle database via initializations to may be rewritten as follows with SQL statements:

model "Duo output OCI (2)"
 uses "mmoci"

 declarations
  A: array(-1..1,5..7) of real
 end-declarations

 A :: [ 2,  4,  6,
       12, 14, 16,
       22, 24, 26]

! Use SQL statements for writing data
 OCIlogon('mmoci.oci:myname/mypassword@dbname')
 OCIexecute("delete from MyOutTable2")
 OCIexecute("insert into MyOutTable2 (Index1,Index2,AValue) values (:1,:2,:3)", A)
 OCIlogoff

end-model 

The insertion command says `write the contents of the array A in the form of value-triples to the fields Index1, Index2, and AValue of the table MyOutTable2'. The markers :1, :2 etc. are placeholders for the index tuple, followed by the value of the array entry (:1 = first index value, :2 = second index value, :3 = array entry). Their number must correspond to the number of output table columns that are named. By these markers it is possible to select which indices/values to output and in which order (see Section Selection of columns/fields).

As explained for the I/O driver version of this example (Section Data output using oci) we need to make sure that the output table does not contain any data. Instead of removing data from the database table ``by hand'', this SQL implementation clears the contents of the output table by executing a `delete' command in the Mosel model before inserting the new data. Alternatively, we might have chosen to update the existing data in the output table by writing out array A with the following command:

 OCIexecute("update MyOutTable2 set AValue=:3 where Index1=:1 and Index2=:2", A)