To copy the SQL below, click anywhere in the box, then Ctrl+A followed by Ctrl+C.

---- WIDGET DEV ----
DROP USER WidgetDev CASCADE;
CREATE USER WidgetDev IDENTIFIED BY password;
GRANT UNLIMITED TABLESPACE TO WidgetDev;
GRANT CONNECT TO WidgetDev;
GRANT CREATE SESSION TO WidgetDev;
GRANT ALTER ANY TABLE TO WidgetDev;
GRANT CREATE ANY INDEX TO WidgetDev;
GRANT CREATE ANY PROCEDURE TO WidgetDev;
GRANT CREATE ANY VIEW TO WidgetDev;
/
CREATE TABLE WidgetDev.WidgetPrices (
    RecordID INTEGER NOT NULL ,
    WidgetID INTEGER NULL ,
    Price INTEGER  NULL ,
    DateValidFrom DATE NULL ,
    DateValidTo DATE NULL ,
    Active CHAR(1) NULL
);
 
ALTER TABLE WidgetDev.WidgetPrices MODIFY DateValidFrom DATE DEFAULT sysdate ;
ALTER TABLE WidgetDev.WidgetPrices MODIFY Active CHAR DEFAULT ('N');
ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID);
 
 
CREATE INDEX WidgetDev.IX_WidgetPrices_1 ON WidgetDev.WidgetPrices(WidgetID);
CREATE INDEX WidgetDev.IX_WidgetPrices_2 ON WidgetDev.WidgetPrices(DateValidFrom);
CREATE INDEX WidgetDev.IX_WidgetPrices_3 ON WidgetDev.WidgetPrices(DateValidTo);
 
GRANT SELECT  ON WidgetDev.WidgetPrices TO public;
 
CREATE TABLE WidgetDev.Widgets (
    RecordID INTEGER NOT NULL ,
    Description VARCHAR2 (50) NULL ,
    SKU VARCHAR2 (20) NULL
);
 
CREATE TABLE WidgetDev.WidgetReferences (
    WidgetID INTEGER NOT NULL,
    Reference VARCHAR2(25) NULL
);
 
ALTER TABLE WidgetDev.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID);
ALTER TABLE WidgetDev.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID);
 
GRANT SELECT ON WidgetDev.Widgets TO PUBLIC;
 
ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT FK_WidgetPrices_Widgets FOREIGN KEY (WidgetID) REFERENCES WidgetDev.Widgets(RecordID);
 
CREATE OR REPLACE VIEW WidgetDev.CurrentPrices AS
SELECT
WidgetID, 
Price, 
Description
FROM WidgetDev.Widgets INNER JOIN
    WidgetDev.WidgetPrices ON Widgets.RecordID = WidgetPrices.WidgetID 
    WHERE WidgetPrices.Active = 'Y';
 
GRANT SELECT ON WidgetDev.CurrentPrices TO PUBLIC;
 
CREATE OR REPLACE PROCEDURE WidgetDev.prcActivatePrices 
IS
BEGIN
  UPDATE WidgetDev.WidgetPrices SET Active='N' WHERE sysdate<DateValidTo OR sysdate>DateValidFrom; 
  UPDATE WidgetDev.WidgetPrices SET Active='Y' WHERE sysdate>=DateValidFrom OR sysdate<=DateValidFrom;
END;
/
CREATE OR REPLACE PACKAGE WidgetDev.WidgetPackage AS
PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2);
PROCEDURE remWidget(widgetID INTEGER);
END WidgetPackage;
/
CREATE OR REPLACE PACKAGE BODY WidgetDev.WidgetPackage AS
PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2) IS
  BEGIN
     INSERT INTO WidgetDev.Widgets(RecordID, Description, SKU) VALUES(widgetID, widgetDesc, widgetSKU);
END addWidget;
PROCEDURE remWidget(widgetID INTEGER) IS
BEGIN
  DELETE FROM WidgetDev.Widgets
   WHERE WidgetDev.Widgets.RECORDID = remWidget.widgetID;
END remWidget;
END WidgetPackage;
/
 
---- WIDGET TEST ----
DROP USER WidgetTest CASCADE;
CREATE USER WidgetTest IDENTIFIED BY password;
GRANT UNLIMITED TABLESPACE TO WidgetTest;
GRANT CONNECT TO WidgetTest;
GRANT CREATE SESSION TO WidgetTest;
GRANT ALTER ANY TABLE TO WidgetTest;
GRANT CREATE ANY INDEX TO WidgetTest;
GRANT CREATE ANY PROCEDURE TO WidgetTest;
GRANT CREATE ANY VIEW TO WidgetTest;
 
CREATE TABLE WidgetTest.WidgetPrices (
    RecordID INTEGER NOT NULL ,
    WidgetID INTEGER NULL ,
    Price INTEGER NULL
);
 
ALTER TABLE WidgetTest.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID);
 
CREATE TABLE WidgetTest.Widgets (
    RecordID INTEGER NOT NULL ,
    Description VARCHAR2(50) NULL
);
 
CREATE TABLE WidgetTest.WidgetReferences (
    WidgetID int NOT NULL ,
    Reference VARCHAR2(50) NULL
);
 
ALTER TABLE WidgetTest.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID);
   
ALTER TABLE WidgetTest.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID);
 
CREATE OR REPLACE VIEW WidgetTest.CurrentPrices AS
SELECT
WidgetID, 
Price, 
Description
FROM WidgetTest.Widgets INNER JOIN
    WidgetTest.WidgetPrices ON WidgetTest.Widgets.RecordID = WidgetTest.WidgetPrices.WidgetID
;
 
CREATE OR REPLACE PACKAGE WidgetTest.WidgetPackage AS
PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2);
PROCEDURE remWidget(widgetID INTEGER);
END WidgetPackage;
/
CREATE OR REPLACE PACKAGE BODY WidgetTest.WidgetPackage AS
PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2) IS
  BEGIN
     INSERT INTO WidgetTest.Widgets(RecordID, Description) VALUES(widgetID, widgetDesc);
END addWidget;
PROCEDURE remWidget(widgetID INTEGER) IS
BEGIN
  DELETE FROM WidgetTest.Widgets
   WHERE WidgetTest.Widgets.RECORDID = remWidget.widgetID;
END remWidget;
END WidgetPackage;
/