genxdata.sql

Oracle PL/SQL procedure generator (second generator type)

REM If the sequence already exists, do not recreate it.
REM DROP SEQUENCE plggen_seq;
CREATE SEQUENCE plggen_seq;

CREATE TABLE plg_doir
   (objid INTEGER,
    driver VARCHAR2(30),  
    owner VARCHAR2(60),
    objname VARCHAR2(60),
    objtype VARCHAR2(50),
    attrname VARCHAR2(250) DEFAULT '*NOTUSED*',
    attrtype VARCHAR2(50) DEFAULT '*NOTUSED*',
    infotype VARCHAR2(150),
    infoseq INTEGER,
    info VARCHAR2(2000),
    doc VARCHAR2(2000),
    created_on DATE,
    created_by VARCHAR2(100),
    changed_on DATE,
    changed_by VARCHAR2(100)
    )
	PCTFREE 20
	PCTUSED 40
   STORAGE ( 
      INITIAL 250K 
      NEXT 100K 
      PCTINCREASE 0)
    ;

REM CREATE UNIQUE INDEX plg_sourplg_doir_pkyce_pky ON
REM    plg_doir (objid)
REM   STORAGE (INITIAL 100k NEXT 50K PCTINCREASE 0);

ALTER TABLE plg_doir ADD CONSTRAINT plg_doir_pky 
   PRIMARY KEY (objid);
   
REM 7/23 Remove unique qualifier. Let PLGdoir.ins procedure do the check

CREATE INDEX i_plg_doir1 ON plg_doir
   (driver, owner, objname, objtype, attrname, attrtype, infotype, infoseq)
   STORAGE (INITIAL 100k NEXT 50K PCTINCREASE 0);

CREATE INDEX i_plg_doir2 ON plg_doir
   (driver, objtype, attrname, attrtype, infotype, infoseq)
   STORAGE (INITIAL 100k NEXT 50K PCTINCREASE 0);

REM Remove any rows in plg_doir which are negative; leave all positive rows in place
REM since these were created by the user.

DELETE FROM plg_doir WHERE objid < 0;

CREATE SEQUENCE plg_doir_seq;

REM New for PLG 1.1

CREATE TABLE plg_source
   (driver VARCHAR2(100),
    name VARCHAR2(200),
    line INTEGER,
    text VARCHAR2(2000),
    created_at DATE
    )
	PCTFREE 0
	PCTUSED 40
   STORAGE ( 
      INITIAL 1000K 
      NEXT 250K 
      PCTINCREASE 0)
   ;

CREATE UNIQUE INDEX plg_source_pky ON
   plg_source (driver,name,line)
   STORAGE (INITIAL 500k NEXT 100K PCTINCREASE 0);
      
ALTER TABLE plg_source ADD CONSTRAINT plg_source_pky
   PRIMARY KEY (driver, name, line);

TRUNCATE TABLE plg_source;
   
REM 2000.2 Add schema and objname to this as well.

CREATE TABLE plg_driver_source
   (driver VARCHAR2(30),
    schema VARCHAR2(100),
    objname VARCHAR2(200),
    name VARCHAR2(200),
    seq INTEGER, 
    line INTEGER,
    text VARCHAR2(2000)
    )
	PCTFREE 0
	PCTUSED 40
   STORAGE ( 
      INITIAL 500K 
      NEXT 250K 
      PCTINCREASE 0)
	;

-- 2000.2 For upgrades and not installs

ALTER TABLE plg_driver_source ADD schema VARCHAR2(100);
ALTER TABLE plg_driver_source ADD objname VARCHAR2(200);

-- 2000.2 Remove uniqueness and primary key for new usages 

-- First changes necessary for upgrades

DROP INDEX plg_driver_source_u1;
DROP INDEX plg_driver_source_pky;

ALTER TABLE plg_driver_source ADD CONSTRAINT plg_driver_source_pky
   PRIMARY KEY (driver,name,line);

-- Now the new indexes

CREATE INDEX plg_driver_source_u1 ON 
   plg_driver_source (driver, seq, line)
   STORAGE (INITIAL 250k NEXT 100K PCTINCREASE 0);

CREATE INDEX plg_driver_source_u2 ON
   plg_driver_source (driver,schema,objname,name,line)
   STORAGE (INITIAL 250k NEXT 100K PCTINCREASE 0);
      
/* 2000.2 Name conversion */

CREATE TABLE plgnamex_word (
   word varchar2(100) PRIMARY KEY, 
   fword varchar2(100), 
   wordlen INTEGER);