ora8oledbscript.sql

LOB objects for oracle database,inserting them to database

--  NAME
--  Ora8OLEDBScript.sql
--
-- DESCRIPTION
--   This script creates the tables and inserts data used for the 
--   OLEDB samples in the current schema.
--   This script can be run with Oracle 8.x version and above


SET SCAN ON
SET DEFINE ON

REM *************************************************************
PROMPT Dropping Table LANGUAGE if it already exists
REM *************************************************************
DROP TABLE LANGUAGE CASCADE CONSTRAINTS ; 

REM *****************************************************
PROMPT Creating Table LANGUAGE
REM *****************************************************
CREATE TABLE LANGUAGE ( 
  LANGUAGE_ID    VARCHAR2 (20)  NOT NULL, 
  LANGUAGE_NAME  VARCHAR2 (20), 
  CONSTRAINT LANGUAGE_PK
  PRIMARY KEY ( LANGUAGE_ID ) ) ; 



REM *************************************************************
PROMPT Dropping Table SUPPLIER if it already exists
REM *************************************************************
DROP TABLE SUPPLIER CASCADE CONSTRAINTS ; 

REM *****************************************************
PROMPT Creating Table SUPPLIER
REM *****************************************************
CREATE TABLE SUPPLIER ( 
  SUPPLIER_ID    NUMBER (6)    NOT NULL, 
  SUPPLIER_NAME  VARCHAR2 (30), 
  CONSTRAINT SUPPLIER_PK
  PRIMARY KEY ( SUPPLIER_ID ) ) ; 



REM *************************************************************
PROMPT Dropping Table ORDERS  if it already exists
REM *************************************************************
DROP TABLE ORDERS CASCADE CONSTRAINTS ; 

REM *****************************************************
PROMPT Creating Table ORDERS
REM *****************************************************
CREATE TABLE ORDERS ( 
  ORDER_ID     NUMBER (12)   NOT NULL, 
  ORDER_DATE   DATE          NOT NULL, 
  ORDER_TOTAL  NUMBER (8,2), 
  SUPPLIER_ID  NUMBER (6), 
  CONSTRAINT ORDERS_PK
  PRIMARY KEY ( ORDER_ID ) ) ; 



REM *************************************************************
PROMPT Dropping Table PRODUCT_INFORMATION  if it already exists
REM *************************************************************
DROP TABLE PRODUCT_INFORMATION CASCADE CONSTRAINTS ; 

REM *****************************************************
PROMPT Creating Table PRODUCT_INFORMATION  
REM *****************************************************
CREATE TABLE PRODUCT_INFORMATION ( 
  PRODUCT_ID           NUMBER (6), 
  PRODUCT_NAME         VARCHAR2 (50),
  PRODUCT_IMAGE        BLOB, 
  PRODUCT_DESCRIPTION  VARCHAR2 (2000), 
  CATEGORY             NUMBER (2), 
  SUPPLIER_ID          NUMBER (6), 
  MIN_PRICE            NUMBER (8,2), 
  PRODUCT_STATUS       VARCHAR2 (20),
  CONSTRAINT PRODUCT_INFORMATION_PK
  PRIMARY KEY ( PRODUCT_ID ) ) ; 



REM *************************************************************
PROMPT Dropping Table PRODUCT_DESCRIPTIONS  if it already exists
REM *************************************************************
DROP TABLE PRODUCT_DESCRIPTIONS CASCADE CONSTRAINTS ; 

REM *****************************************************
PROMPT Creating Table PRODUCT_DESCRIPTIONS  
REM *****************************************************
CREATE TABLE PRODUCT_DESCRIPTIONS ( 
  PRODUCT_ID              NUMBER (6)    NOT NULL, 
  LANGUAGE_ID             VARCHAR2 (20)  NOT NULL, 
  TRANSLATED_NAME         NVARCHAR2 (50), 
  TRANSLATED_DESCRIPTION  NVARCHAR2 (2000), 
  CONSTRAINT PRODUCT_DESCRIPTIONS_PK
  PRIMARY KEY ( PRODUCT_ID, LANGUAGE_ID ) ) ; 



REM ***********************************************************
PROMPT Creating foreign key between ORDERS and SUPPLIER tables 
REM ***********************************************************
ALTER TABLE ORDERS ADD  CONSTRAINT ORDERS_SUPPLIER_FK
 FOREIGN KEY (SUPPLIER_ID) 
  REFERENCES SUPPLIER (SUPPLIER_ID) ;



REM ***********************************************************
PROMPT Creating foreign key between PRODUCT_DESCRIPTIONS and 
PROMPT PRODUCT_INFORMATION tables 
REM ***********************************************************
ALTER TABLE PRODUCT_DESCRIPTIONS ADD  CONSTRAINT PRODUCT_INFORMATION_FK
 FOREIGN KEY (PRODUCT_ID) 
  REFERENCES PRODUCT_INFORMATION (PRODUCT_ID) ;



REM *********************************************************************
PROMPT Inserting Sample Data into LANGUAGE table and Commit 
REM *********************************************************************
INSERT INTO LANGUAGE ( LANGUAGE_ID, LANGUAGE_NAME ) VALUES ( 
'2', 'French'); 
INSERT INTO LANGUAGE ( LANGUAGE_ID, LANGUAGE_NAME ) VALUES ( 
'3', 'Irish'); 
INSERT INTO LANGUAGE ( LANGUAGE_ID, LANGUAGE_NAME ) VALUES ( 
'4', 'English'); 
INSERT INTO LANGUAGE ( LANGUAGE_ID, LANGUAGE_NAME ) VALUES (