ora7oledbscript.sql

LOB objects for oracle database,inserting them to database

--  NAME
--  Ora7OLEDBScript.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 7.3 version and above

SET SCAN ON
SET DEFINE ON


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_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 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 Inserting Sample Data into SUPPLIER table and Commit 
REM *********************************************************************
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102050, 'ABC PRODUCTS LTD'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102051, 'PQR Computers'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102053, 'NEXGEN AGENCIES'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102055, 'XYZ PVT LTD'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102057, 'SUPER COMPUTERS'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102060, 'Y AXIS DEALERS'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102061, 'ZOOM TECH LTD'); 
INSERT INTO SUPPLIER ( SUPPLIER_ID, SUPPLIER_NAME ) VALUES ( 
102068, 'TELESOFT COMMUNICATIONS'); 
commit;



REM *********************************************************************
PROMPT Inserting Sample Data into ORDERS table and Commit 
REM *********************************************************************
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2436,  TO_Date( '09/02/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 6394.8, 102053); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2446,  TO_Date( '07/27/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 103679.3, 102055); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2447,  TO_Date( '07/27/2000 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 33893.6, 102055); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2432,  TO_Date( '09/14/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 10523, 102053); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2433,  TO_Date( '09/13/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 78, 102053); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2359,  TO_Date( '01/08/1998 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 5543.1, 102068); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2390,  TO_Date( '11/18/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 7616.8, 102061); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2407,  TO_Date( '06/29/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2519, 102060); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2410,  TO_Date( '05/24/2000 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 45175, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2411,  TO_Date( '05/24/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 15760.5, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2412,  TO_Date( '03/29/1998 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 66816, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2413,  TO_Date( '03/29/2000 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 48552, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2414,  TO_Date( '03/29/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 10794.6, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2415,  TO_Date( '03/29/1997 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 310, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2416,  TO_Date( '03/29/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 384, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2417,  TO_Date( '03/20/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 1926.6, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2418,  TO_Date( '03/20/1996 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 5546.6, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2419,  TO_Date( '03/20/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 31574, 102050); 
INSERT INTO ORDERS ( ORDER_ID, ORDER_DATE, ORDER_TOTAL, SUPPLIER_ID ) VALUES ( 
2420,  TO_Date( '03/13/1999 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 29750, 102051);