Setting up Oracle

These instructions assume that you are using an Oracle database and guide you through the following tasks:

If your install has not created the default database then create a database, following your DBA instructions, before following these instructions.

You can also perform these steps by running pre-defined SQL scripts: Return to sample home and choose one of the import options. After importing you can choose to set up the database from SQL scripts.

Note. When you create and access the database tables, be aware of the following issues:

  1. Open a new document in a text editor, copy and paste the following script, which creates the DAPTER_ADDRESS, ADAPTER_CUSTOMER, ADAPTER_INVOICE and IBM_EVENTS tables, into the document, name the file adatper_create_tables_oracle.sql and save the file.
    -- First create the tables needed for Application Data
    DROP TABLE "ADAPTER_CUSTOMER" ;
    CREATE TABLE "ADAPTER_CUSTOMER" (
    		"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
    		"FIRSTNAME" VARCHAR(20),
    		"LASTNAME" VARCHAR(20),
    		"ADDRESS" VARCHAR(10),
    		"CCODE" VARCHAR(10)
    	);
    
    ALTER TABLE "ADAPTER_CUSTOMER" ADD CONSTRAINT "CUST_ADDR_FK" FOREIGN KEY
    	("ADDRESS")
    	REFERENCES "ADAPTER_ADDRESS"
    	("PKEY")
    	ON DELETE CASCADE;
    
    DROP TABLE "ADAPTER_ADDRESS" ;
    CREATE TABLE "ADAPTER_ADDRESS" (
    	"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
    	"FIRSTLINE" VARCHAR(20),
    	"SECONDLINE" VARCHAR(20),
    	"CODE" VARCHAR(10),
    	"CITY" VARCHAR(20),
    	"COUNTRY" VARCHAR(10));
    
    DROP TABLE "ADAPTER_INVOICE" ;
    CREATE TABLE "ADAPTER_INVOICE" (
    	"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
    	"ITEM" VARCHAR(10) NOT NULL,
    	"QUANTITY" INTEGER NOT NULL,
    	"CUSTOMER" VARCHAR(10) NOT NULL);
    
    ALTER TABLE "ADAPTER_INVOICE" ADD CONSTRAINT "INV_CUST_FK" FOREIGN KEY
    	("CUSTOMER")
    	REFERENCES "ADAPTER_CUSTOMER"
    	("PKEY")
    	ON DELETE CASCADE;
    
    DROP TABLE "IBM_EVENTS" ;
    CREATE TABLE "IBM_EVENTS" (
    		"EVENT_ID" INTEGER PRIMARY KEY,
    		"CONNECTOR_ID" VARCHAR(40),
    		"OBJECT_KEY" VARCHAR(10) NOT NULL,
    		"OBJECT_NAME" VARCHAR(40) NOT NULL,
    		"OBJECT_VERB" VARCHAR(40) NOT NULL,
    		"EVENT_PRIORITY" INTEGER NOT NULL,
    		"EVENT_TIME" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
    		"EVENT_STATUS" INTEGER NOT NULL,
    		"EVENT_COMMENT" VARCHAR(100)
    	);
    
    
    CREATE SEQUENCE "IBM_SEQUENCE" (start with 1 increment by 1 nomaxvalue);
    CREATE TRIGGER "IBM_SEQ_TRIG" 
      BEFORE INSERT ON "IBM_EVENTS" 
      FOR EACH ROW 
      BEGIN 
        SELECT DBINPUT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual; 
      END;
    
    DROP TRIGGER "ADAP_CUST_EVENT";
    CREATE TRIGGER "ADAP_CUST_EVENT" 
    	AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_CUSTOMER"
    	REFERENCING  NEW AS N OLD AS O
    	FOR EACH ROW
    	BEGIN
    		IF inserting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Create', 1, 0);
    		END IF;
    		IF updating THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Update', 1, 0);
    		END IF;
    		IF deleting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Delete', 1, 0);
    		END IF;
    	END;
    	
    DROP TRIGGER "ADAP_ADD_EVENT";
    CREATE TRIGGER "ADAP_ADD_EVENT" 
    	AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_ADDRESS"
    	REFERENCING  NEW AS N OLD AS O
    	FOR EACH ROW
    	BEGIN
    		IF inserting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Create', 1, 0);
    		END IF;
    		IF updating THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Update', 1, 0);
    		END IF;
    		IF deleting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Delete', 1, 0);
    		END IF;
    	END;
    	
    DROP TRIGGER "ADAP_INV_EVENT";
    CREATE TRIGGER "ADAP_INV_EVENT" 
    	AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_INVOICE"
    	REFERENCING  NEW AS N OLD AS O
    	FOR EACH ROW
    	BEGIN
    		IF inserting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Create', 1, 0);
    		END IF;
    		IF updating THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Update', 1, 0);
    		END IF;
    		IF deleting THEN
    			INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS)
    				VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Delete', 1, 0);
    		END IF;
    	END;
    	
    	
            
  2. In a command window that you have opened to access your database, move to the folder that contains adapter_create_tables_oracle.sql and enter the following command:
    sqlplus <uid>/<password> @adapter_create_tables_oracle.sql

    Wait for the script to finish running. If you are running the script for the first time, the following message is displayed:

    ORA-00942: table or view does not exist
    Ignore this message. The message is displayed because the script attempts to remove a existing tables called DBINPUT_CUSTOMER and DBINPUT_EVENTS before it creates new tables, but if you have not run the script before, the script cannot find the existing table.

Back to Setting up the database

Back to sample home