Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » MobiLink - Getting Started » MobiLink Tutorials » Tutorial: Using MobiLink with an Oracle Database 10g

 

Lesson 2: Prepare the consolidated database

This tutorial assumes that you have installed the Order Entry (OE) sample database. Information about installing the sample schema can be found in the Oracle documentation or at [external link] http://www.oracle.com/technology/obe/obe1013jdev/common/OBEConnection.htm.

The OE database needs to be altered for use with MobiLink. Columns are dropped because they were created as user-defined types. You could translate these user-defined types into types that SQL Anywhere recognizes, but doing so is not relevant to this tutorial. Next, you must grant permission to the OE user to create triggers because MobiLink needs to create a few triggers using OE's credentials.

 To prepare the consolidated database
  1. Connect as the SYS user with SYSDBA privileges using the Oracle SQL Plus application. At a command prompt, run the following command:

    sqlplus SYS/your password for sys as SYSDBA
  2. To drop columns created as user-defined types, run the following commands:

    ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_ADDRESS;
    ALTER TABLE OE.CUSTOMERS DROP COLUMN PHONE_NUMBERS;
    ALTER TABLE OE.CUSTOMERS DROP COLUMN CUST_GEO_LOCATION;
    ALTER TABLE OE.PRODUCT_INFORMATION DROP COLUMN WARRANTY_PERIOD;
  3. Unlock the OE user and set the password to sql. Run the following command:

    ALTER USER OE IDENTIFIED BY sql ACCOUNT UNLOCK;
  4. To allow the OE user to create triggers, run the following command:

    GRANT CREATE ANY TRIGGER TO OE;
  5. To drop the orders_customer foreign key and create a new foreign key that references the customer_id in the customers table, run the following commands:

    ALTER TABLE OE.ORDERS DROP CONSTRAINT ORDERS_CUSTOMER_ID_FK;
    ALTER TABLE OE.ORDERS ADD CONSTRAINT ORDERS_CUSTOMER_ID_FK
      FOREIGN KEY (CUSTOMER_ID) REFERENCES OE.CUSTOMERS (CUSTOMER_ID);
 Adding unique primary keys
 Unique primary keys across all databases
 Further reading