Setting up Oracle

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

• Create a table, DBINPUT_CUSTOMER, for use by the DatabaseInput sample
• Create an event table, DBINPUT_EVENTS, to hold the information for which the DatabaseInput node will poll
• Create triggers to populate the DBINPUT_EVENTS table

Before you start

You must create a database.

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

• When you run the script that creates the table in the database, the user identifier from which you run the script determines the database schema name. For example, if your user identifier is USERID, the fully qualified name of the DBINPUT_CUSTOMER table is USERID.DBINPUT_CUSTOMER.
• When you set an integration node to access a table, if the node is not using the same user identifier to access the database that you used when you created the database and tables, include the schema name in the node. For example, Database.USERID.DBINPUT_CUSTOMER.

1. Open a new document in a text editor, copy and paste the following script, which creates the DBINPUT_CUSTOMER table, into the document, name the file dbinput_create_tables_oracle.sql and save the file.

   DROP TABLE "DBINPUT_CUSTOMER";
   CREATE TABLE "DBINPUT_CUSTOMER" (
   		"PKEY" VARCHAR(10) NOT NULL PRIMARY KEY,
   		"FIRSTNAME" VARCHAR(20),
   		"LASTNAME" VARCHAR(20),
   		"CCODE" VARCHAR(10)
   	);
   
   DROP TABLE "DBINPUT_EVENTS";
   CREATE TABLE "DBINPUT_EVENTS" (
   		"EVENT_ID" INTEGER PRIMARY KEY
   		"OBJECT_KEY" VARCHAR(80) NOT NULL,
   		"OBJECT_VERB" VARCHAR(40) NOT NULL
   	);
   
   CREATE SEQUENCE "DBINPUT_SEQUENCE" (start with 1 increment by 1 nomaxvalue);
   CREATE TRIGGER "DBIN_SEQ_TRIG" 
     BEFORE INSERT ON "DBINPUT_EVENTS" 
     FOR EACH ROW 
     BEGIN 
       SELECT DBINPUT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual; 
     END;
   
   CREATE TRIGGER "DBIN_CUST_EVENT" 
   	AFTER INSERT OR DELETE OR UPDATE ON "DBINPUT_CUSTOMER"
   	REFERENCING  NEW AS N OLD AS O
   	FOR EACH ROW
   	BEGIN
   		IF inserting THEN
   			INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
   				VALUES(:N.PKEY,'Create');
   		END IF;
   		IF updating THEN
   			INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
   				VALUES(:N.PKEY,'Update');
   		END IF;
   		IF deleting THEN
   			INSERT INTO DBINPUT_EVENTS(OBJECT_KEY, OBJECT_VERB)
   				VALUES(:O.PKEY,'Delete');
   		END IF;
   	END;
           

2. In a command window that you have opened to access your database, move to the folder that contains dbinput_create_tables_oracle.sql and enter the following command:

   sqlplus <uid>/<password> @dbinput_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 existing tables, DBINPUT_CUSTOMER and DBINPUT_EVENTS, before it creates new tables, but if you have not run the script before, the the tables do not exist.

Back to Setting up the database

Back to sample home