(!******************************************************* Mosel Example Problems ====================== file ociinv.mos ``````````````` Accessing an Oracle database with selection statements using columns in inverted order. (c) 2008 Fair Isaac Corporation author: S. Heipcke, 2007, rev. Jul. 2010 *******************************************************!) model "OCI selection of columns" uses "mmoci" parameters DB="myname/mypassword@dbname" ! Login to Oracle database (not provided) end-parameters declarations PRODUCTS: set of string MACH: range COST,COST2: dynamic array(MACH,PRODUCTS) of real DUR,DUR2,TEMP: dynamic array(PRODUCTS,MACH) of integer end-declarations setparam("OCIdebug",true) OCIlogon(DB) ! Read data from the table 'ProdData' OCIexecute("select Mach,Products,Cost from ProdData", COST) OCIexecute("select Products,Mach,Duration from ProdData", DUR) ! Print out what we have read writeln(COST); writeln(DUR) ! Write out data to another table (after deleting and re-creating the table) OCIexecute("drop table CombData") OCIexecute("create table CombData (Products varchar(10), Mach integer, Cost float, Duration integer)") OCIexecute("insert into CombData (Mach,Products,Cost) values (:1,:2,:3)", COST) ! Altenatively: ! OCIexecute("insert into CombData (Products,Mach,Cost) values (:2,:1,:3)", COST) ! Fill the 'Duration' field of the output table: ! 1. update the existing entries, 2. add new entries OCIexecute("update CombData set Duration=:3 where Products=:1 and Mach=:2", DUR) forall(p in PRODUCTS, m in MACH | exists(DUR(p,m)) and not exists(COST(m,p))) TEMP(p,m) := DUR(p,m) OCIexecute("insert into CombData (Products,Mach,Duration) values (:1,:2,:3)", TEMP) OCIlogoff initializations from "mmoci.oci:debug;"+DB COST2 as "ProdData(Mach,Products,Cost)" DUR2 as "ProdData(Products,Mach,Duration)" end-initializations writeln(COST2); writeln(DUR2) ! Delete and re-create the output table OCIlogon(DB) OCIexecute("drop table CombData2") OCIexecute("create table CombData2 (Products varchar(10), Mach integer, Cost float, Duration integer)") OCIlogoff initializations to "mmoci.oci:debug;"+DB COST2 as "CombData2(Mach,Products,Cost)" DUR2 as "CombData2(Products,Mach,Duration)" end-initializations end-model ************************************************** ! Creation of the data table in an Oracle database: declarations tsucc: array ({false,true}) of string end-declarations tsucc(false):="failed"; tsucc(true):="succeeded" OCIexecute("create table ProdData (Mach integer, Products varchar(10), Cost float, Duration integer)") writeln(" - Create ProdData (",tsucc(getparam("OCIsuccess")),")") declarations PRODUCTS: set of string MACH: range COST: dynamic array(PRODUCTS,MACH) of real DUR: dynamic array(PRODUCTS,MACH) of integer end-declarations initializations from "ocidata.dat" COST DUR end-initializations OCIexecute("insert into ProdData (Mach, Products, Cost, Duration) values (:2, :1, :3, :4)", [COST,DUR]) writeln(" - Insert values in ProdData (",tsucc(getparam("OCIsuccess")),",", getparam("OCIrowcnt"),'/',getparam("OCIrowxfr")," rows)")