Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
- description: basic_table
schema: |
CREATE TABLE TEST_TABLE (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(50) NOT NULL
);
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "TEST_TABLE",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)"
}
],
"indexes": [
{
"name": "PK_TEST_TABLE",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: table_with_sequence
schema: |
CREATE TABLE EMPLOYEES (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100) NOT NULL
);
CREATE SEQUENCE EMP_SEQ START WITH 1 INCREMENT BY 1;
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "EMPLOYEES",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
}
],
"indexes": [
{
"name": "PK_EMPLOYEES",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
],
"sequences": [
{
"name": "EMP_SEQ",
"start": "1"
}
]
}
]
}
- description: table_with_view
schema: |
CREATE TABLE PRODUCTS (
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100) NOT NULL,
PRICE NUMBER(10,2)
);
CREATE VIEW EXPENSIVE_PRODUCTS AS
SELECT ID, NAME, PRICE
FROM PRODUCTS
WHERE PRICE > 100;
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "PRODUCTS",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "PRICE",
"position": 3,
"nullable": true,
"type": "NUMBER(10,2)"
}
],
"indexes": [
{
"name": "PK_PRODUCTS",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
],
"views": [
{
"name": "EXPENSIVE_PRODUCTS",
"definition": "SELECT ID, NAME, PRICE\nFROM PRODUCTS\nWHERE PRICE \u003e 100"
}
]
}
]
}
- description: comprehensive_data_types
schema: |
CREATE TABLE DATA_TYPES_COMPREHENSIVE (
ID NUMBER PRIMARY KEY,
-- Numeric types
NUMBER_COL NUMBER,
NUMBER_PRECISION NUMBER(10),
NUMBER_SCALE NUMBER(10,2),
FLOAT_COL FLOAT,
BINARY_FLOAT_COL BINARY_FLOAT,
BINARY_DOUBLE_COL BINARY_DOUBLE,
-- Character types
VARCHAR2_COL VARCHAR2(4000),
NVARCHAR2_COL NVARCHAR2(2000),
CHAR_COL CHAR(100),
NCHAR_COL NCHAR(100),
-- Date and time types
DATE_COL DATE,
TIMESTAMP_COL TIMESTAMP,
TIMESTAMP_TZ_COL TIMESTAMP WITH TIME ZONE,
TIMESTAMP_LTZ_COL TIMESTAMP WITH LOCAL TIME ZONE,
INTERVAL_YM_COL INTERVAL YEAR TO MONTH,
INTERVAL_DS_COL INTERVAL DAY TO SECOND,
-- Large object types
CLOB_COL CLOB,
NCLOB_COL NCLOB,
BLOB_COL BLOB,
-- Binary types
RAW_COL RAW(2000),
LONG_RAW_COL LONG RAW,
-- Row identifier
ROWID_COL ROWID,
UROWID_COL UROWID
);
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "DATA_TYPES_COMPREHENSIVE",
"columns": [
{
"name": "ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "NUMBER_COL",
"position": 2,
"nullable": true,
"type": "NUMBER"
},
{
"name": "NUMBER_PRECISION",
"position": 3,
"nullable": true,
"type": "NUMBER(10)"
},
{
"name": "NUMBER_SCALE",
"position": 4,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "FLOAT_COL",
"position": 5,
"nullable": true,
"type": "FLOAT(126)"
},
{
"name": "BINARY_FLOAT_COL",
"position": 6,
"nullable": true,
"type": "BINARY_FLOAT"
},
{
"name": "BINARY_DOUBLE_COL",
"position": 7,
"nullable": true,
"type": "BINARY_DOUBLE"
},
{
"name": "VARCHAR2_COL",
"position": 8,
"nullable": true,
"type": "VARCHAR2(4000 BYTE)"
},
{
"name": "NVARCHAR2_COL",
"position": 9,
"nullable": true,
"type": "NVARCHAR2(4000)"
},
{
"name": "CHAR_COL",
"position": 10,
"nullable": true,
"type": "CHAR(100 BYTE)"
},
{
"name": "NCHAR_COL",
"position": 11,
"nullable": true,
"type": "NCHAR(200)"
},
{
"name": "DATE_COL",
"position": 12,
"nullable": true,
"type": "DATE"
},
{
"name": "TIMESTAMP_COL",
"position": 13,
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "TIMESTAMP_TZ_COL",
"position": 14,
"nullable": true,
"type": "TIMESTAMP(6) WITH TIME ZONE"
},
{
"name": "TIMESTAMP_LTZ_COL",
"position": 15,
"nullable": true,
"type": "TIMESTAMP(6) WITH LOCAL TIME ZONE"
},
{
"name": "INTERVAL_YM_COL",
"position": 16,
"nullable": true,
"type": "INTERVAL YEAR(2) TO MONTH"
},
{
"name": "INTERVAL_DS_COL",
"position": 17,
"nullable": true,
"type": "INTERVAL DAY(2) TO SECOND(6)"
},
{
"name": "CLOB_COL",
"position": 18,
"nullable": true,
"type": "CLOB"
},
{
"name": "NCLOB_COL",
"position": 19,
"nullable": true,
"type": "NCLOB"
},
{
"name": "BLOB_COL",
"position": 20,
"nullable": true,
"type": "BLOB"
},
{
"name": "RAW_COL",
"position": 21,
"nullable": true,
"type": "RAW(2000)"
},
{
"name": "LONG_RAW_COL",
"position": 22,
"nullable": true,
"type": "LONG RAW"
},
{
"name": "ROWID_COL",
"position": 23,
"nullable": true,
"type": "ROWID"
},
{
"name": "UROWID_COL",
"position": 24,
"nullable": true,
"type": "UROWID(4000)"
}
],
"indexes": [
{
"name": "PK_DATA_TYPES_COMPREHENSIVE",
"expressions": [
"ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: complex_constraints
schema: |
CREATE TABLE ORDERS (
ORDER_ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER NOT NULL,
ORDER_DATE DATE DEFAULT SYSDATE,
STATUS VARCHAR2(20) DEFAULT 'PENDING',
TOTAL_AMOUNT NUMBER(12,2) NOT NULL,
DISCOUNT_PERCENT NUMBER(5,2) DEFAULT 0,
SHIP_DATE DATE,
NOTES VARCHAR2(4000),
CREATED_BY VARCHAR2(100) DEFAULT USER,
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- Check constraints
CONSTRAINT chk_status CHECK (STATUS IN ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
CONSTRAINT chk_total_positive CHECK (TOTAL_AMOUNT > 0),
CONSTRAINT chk_discount_range CHECK (DISCOUNT_PERCENT BETWEEN 0 AND 100),
CONSTRAINT chk_ship_after_order CHECK (SHIP_DATE IS NULL OR SHIP_DATE >= ORDER_DATE),
-- Unique constraints
CONSTRAINT uk_order_customer_date UNIQUE (CUSTOMER_ID, ORDER_DATE)
);
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "ORDERS",
"columns": [
{
"name": "ORDER_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "STATUS",
"position": 4,
"default": "'PENDING'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)"
},
{
"name": "TOTAL_AMOUNT",
"position": 5,
"type": "NUMBER(12,2)"
},
{
"name": "DISCOUNT_PERCENT",
"position": 6,
"default": "0",
"nullable": true,
"type": "NUMBER(5,2)"
},
{
"name": "SHIP_DATE",
"position": 7,
"nullable": true,
"type": "DATE"
},
{
"name": "NOTES",
"position": 8,
"nullable": true,
"type": "VARCHAR2(4000 BYTE)"
},
{
"name": "CREATED_BY",
"position": 9,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "CREATED_AT",
"position": 10,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "UPDATED_AT",
"position": 11,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "PK_ORDERS",
"expressions": [
"ORDER_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_ORDER_CUSTOMER_DATE",
"expressions": [
"CUSTOMER_ID",
"ORDER_DATE"
],
"type": "NORMAL",
"unique": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "CHK_STATUS",
"expression": "STATUSIN('PENDING','PROCESSING','SHIPPED','DELIVERED','CANCELLED')"
},
{
"name": "CHK_TOTAL_POSITIVE",
"expression": "TOTAL_AMOUNT\u003e0"
},
{
"name": "CHK_DISCOUNT_RANGE",
"expression": "DISCOUNT_PERCENTBETWEEN0AND100"
},
{
"name": "CHK_SHIP_AFTER_ORDER",
"expression": "SHIP_DATEISNULLORSHIP_DATE\u003e=ORDER_DATE"
}
]
}
]
}
]
}
- description: foreign_keys_and_references
schema: |
CREATE TABLE DEPARTMENTS (
DEPT_ID NUMBER PRIMARY KEY,
DEPT_NAME VARCHAR2(100) NOT NULL UNIQUE,
MANAGER_ID NUMBER,
LOCATION VARCHAR2(100),
BUDGET NUMBER(15,2)
);
CREATE TABLE EMPLOYEES_FK (
EMP_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL,
EMAIL VARCHAR2(100) UNIQUE,
PHONE VARCHAR2(20),
HIRE_DATE DATE DEFAULT SYSDATE,
SALARY NUMBER(10,2),
COMMISSION_PCT NUMBER(4,2),
DEPT_ID NUMBER,
MANAGER_ID NUMBER,
-- Foreign key constraints
CONSTRAINT fk_emp_dept FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID) ON DELETE SET NULL,
CONSTRAINT fk_emp_manager FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES_FK(EMP_ID) ON DELETE SET NULL,
-- Check constraints
CONSTRAINT chk_salary_positive CHECK (SALARY > 0),
CONSTRAINT chk_commission_range CHECK (COMMISSION_PCT BETWEEN 0 AND 1),
CONSTRAINT chk_email_format CHECK (EMAIL LIKE '%@%.%')
);
-- Self-referencing foreign key for department manager
ALTER TABLE DEPARTMENTS ADD CONSTRAINT fk_dept_manager
FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES_FK(EMP_ID) ON DELETE SET NULL;
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "DEPARTMENTS",
"columns": [
{
"name": "DEPT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "DEPT_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "MANAGER_ID",
"position": 3,
"nullable": true,
"type": "NUMBER"
},
{
"name": "LOCATION",
"position": 4,
"nullable": true,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "BUDGET",
"position": 5,
"nullable": true,
"type": "NUMBER(15,2)"
}
],
"indexes": [
{
"name": "PK_DEPARTMENTS",
"expressions": [
"DEPT_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_DEPARTMENTS_DEPT_NAME",
"expressions": [
"DEPT_NAME"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"foreign_keys": [
{
"name": "FK_DEPT_MANAGER",
"columns": [
"MANAGER_ID"
],
"referenced_table": "EMPLOYEES_FK",
"referenced_columns": [
"EMP_ID"
],
"on_delete": "SET NULL"
}
]
},
{
"name": "EMPLOYEES_FK",
"columns": [
{
"name": "EMP_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "FIRST_NAME",
"position": 2,
"type": "VARCHAR2(50 BYTE)"
},
{
"name": "LAST_NAME",
"position": 3,
"type": "VARCHAR2(50 BYTE)"
},
{
"name": "EMAIL",
"position": 4,
"nullable": true,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "PHONE",
"position": 5,
"nullable": true,
"type": "VARCHAR2(20 BYTE)"
},
{
"name": "HIRE_DATE",
"position": 6,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
},
{
"name": "SALARY",
"position": 7,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "COMMISSION_PCT",
"position": 8,
"nullable": true,
"type": "NUMBER(4,2)"
},
{
"name": "DEPT_ID",
"position": 9,
"nullable": true,
"type": "NUMBER"
},
{
"name": "MANAGER_ID",
"position": 10,
"nullable": true,
"type": "NUMBER"
}
],
"indexes": [
{
"name": "PK_EMPLOYEES_FK",
"expressions": [
"EMP_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_EMPLOYEES_FK_EMAIL",
"expressions": [
"EMAIL"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"foreign_keys": [
{
"name": "FK_EMP_DEPT",
"columns": [
"DEPT_ID"
],
"referenced_table": "DEPARTMENTS",
"referenced_columns": [
"DEPT_ID"
],
"on_delete": "SET NULL"
},
{
"name": "FK_EMP_MANAGER",
"columns": [
"MANAGER_ID"
],
"referenced_table": "EMPLOYEES_FK",
"referenced_columns": [
"EMP_ID"
],
"on_delete": "SET NULL"
}
],
"check_constraints": [
{
"name": "CHK_SALARY_POSITIVE",
"expression": "SALARY\u003e0"
},
{
"name": "CHK_COMMISSION_RANGE",
"expression": "COMMISSION_PCTBETWEEN0AND1"
},
{
"name": "CHK_EMAIL_FORMAT",
"expression": "EMAILLIKE'%@%.%'"
}
]
}
]
}
]
}
- description: indexes_comprehensive
schema: |
CREATE TABLE CUSTOMER_ORDERS (
ORDER_ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER NOT NULL,
ORDER_DATE DATE NOT NULL,
PRODUCT_CODE VARCHAR2(50) NOT NULL,
QUANTITY NUMBER NOT NULL,
UNIT_PRICE NUMBER(10,2) NOT NULL,
TOTAL_AMOUNT NUMBER(12,2) NOT NULL,
STATUS VARCHAR2(20) DEFAULT 'ACTIVE',
DESCRIPTION CLOB,
TAGS VARCHAR2(1000),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Various index types
CREATE INDEX idx_customer_date ON CUSTOMER_ORDERS(CUSTOMER_ID, ORDER_DATE);
CREATE INDEX idx_product_status ON CUSTOMER_ORDERS(PRODUCT_CODE, STATUS);
CREATE INDEX idx_order_date_desc ON CUSTOMER_ORDERS(ORDER_DATE DESC);
CREATE INDEX idx_total_amount ON CUSTOMER_ORDERS(TOTAL_AMOUNT);
CREATE UNIQUE INDEX idx_customer_product_unique ON CUSTOMER_ORDERS(CUSTOMER_ID, PRODUCT_CODE, ORDER_DATE);
CREATE BITMAP INDEX idx_status_bitmap ON CUSTOMER_ORDERS(STATUS);
CREATE INDEX idx_upper_product ON CUSTOMER_ORDERS(UPPER(PRODUCT_CODE));
CREATE INDEX idx_created_year ON CUSTOMER_ORDERS(EXTRACT(YEAR FROM CREATED_AT));
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "CUSTOMER_ORDERS",
"columns": [
{
"name": "ORDER_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "ORDER_DATE",
"position": 3,
"type": "DATE"
},
{
"name": "PRODUCT_CODE",
"position": 4,
"type": "VARCHAR2(50 BYTE)"
},
{
"name": "QUANTITY",
"position": 5,
"type": "NUMBER"
},
{
"name": "UNIT_PRICE",
"position": 6,
"type": "NUMBER(10,2)"
},
{
"name": "TOTAL_AMOUNT",
"position": 7,
"type": "NUMBER(12,2)"
},
{
"name": "STATUS",
"position": 8,
"default": "'ACTIVE'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)"
},
{
"name": "DESCRIPTION",
"position": 9,
"nullable": true,
"type": "CLOB"
},
{
"name": "TAGS",
"position": 10,
"nullable": true,
"type": "VARCHAR2(1000 BYTE)"
},
{
"name": "CREATED_AT",
"position": 11,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "PK_CUSTOMER_ORDERS",
"expressions": [
"ORDER_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "IDX_CUSTOMER_DATE",
"expressions": [
"CUSTOMER_ID",
"ORDER_DATE"
],
"descending": [
false,
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_PRODUCT_STATUS",
"expressions": [
"PRODUCT_CODE",
"STATUS"
],
"descending": [
false,
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_ORDER_DATE_DESC",
"expressions": [
"ORDER_DATE"
],
"descending": [
true
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
},
{
"name": "IDX_TOTAL_AMOUNT",
"expressions": [
"TOTAL_AMOUNT"
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_CUSTOMER_PRODUCT_UNIQUE",
"expressions": [
"CUSTOMER_ID",
"PRODUCT_CODE",
"ORDER_DATE"
],
"descending": [
false,
false,
false
],
"type": "NORMAL",
"unique": true,
"visible": true
},
{
"name": "IDX_STATUS_BITMAP",
"expressions": [
"STATUS"
],
"descending": [
false
],
"type": "BITMAP",
"visible": true
},
{
"name": "IDX_UPPER_PRODUCT",
"expressions": [
"UPPER(PRODUCT_CODE)"
],
"descending": [
false
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
},
{
"name": "IDX_CREATED_YEAR",
"expressions": [
"EXTRACT(YEAR FROM CREATED_AT)"
],
"descending": [
false
],
"type": "FUNCTION-BASED NORMAL",
"visible": true
}
]
}
]
}
]
}
- description: sequences_and_triggers
schema: |
CREATE TABLE AUDIT_LOG (
LOG_ID NUMBER PRIMARY KEY,
TABLE_NAME VARCHAR2(128) NOT NULL,
OPERATION VARCHAR2(10) NOT NULL,
OLD_VALUES CLOB,
NEW_VALUES CLOB,
USER_NAME VARCHAR2(128) DEFAULT USER,
TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
SESSION_ID VARCHAR2(128) DEFAULT SYS_CONTEXT('USERENV', 'SESSIONID')
);
CREATE SEQUENCE audit_log_seq
START WITH 1000
INCREMENT BY 1
MAXVALUE 999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20;
CREATE OR REPLACE TRIGGER audit_log_trigger
BEFORE INSERT ON AUDIT_LOG
FOR EACH ROW
BEGIN
IF :NEW.LOG_ID IS NULL THEN
SELECT audit_log_seq.NEXTVAL INTO :NEW.LOG_ID FROM DUAL;
END IF;
END;
/
CREATE TABLE INVENTORY (
ITEM_ID NUMBER PRIMARY KEY,
ITEM_CODE VARCHAR2(50) UNIQUE NOT NULL,
DESCRIPTION VARCHAR2(200),
QUANTITY_ON_HAND NUMBER DEFAULT 0,
UNIT_COST NUMBER(10,2),
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE SEQUENCE inventory_seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER inventory_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON INVENTORY
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, NEW_VALUES)
VALUES ('INVENTORY', 'INSERT', 'ID=' || :NEW.ITEM_ID || ',CODE=' || :NEW.ITEM_CODE);
ELSIF UPDATING THEN
INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)
VALUES ('INVENTORY', 'UPDATE',
'ID=' || :OLD.ITEM_ID || ',QTY=' || :OLD.QUANTITY_ON_HAND,
'ID=' || :NEW.ITEM_ID || ',QTY=' || :NEW.QUANTITY_ON_HAND);
ELSIF DELETING THEN
INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES)
VALUES ('INVENTORY', 'DELETE', 'ID=' || :OLD.ITEM_ID || ',CODE=' || :OLD.ITEM_CODE);
END IF;
END;
/
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "AUDIT_LOG",
"columns": [
{
"name": "LOG_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "TABLE_NAME",
"position": 2,
"type": "VARCHAR2(128 BYTE)"
},
{
"name": "OPERATION",
"position": 3,
"type": "VARCHAR2(10 BYTE)"
},
{
"name": "OLD_VALUES",
"position": 4,
"nullable": true,
"type": "CLOB"
},
{
"name": "NEW_VALUES",
"position": 5,
"nullable": true,
"type": "CLOB"
},
{
"name": "USER_NAME",
"position": 6,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(128 BYTE)"
},
{
"name": "TIMESTAMP",
"position": 7,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
},
{
"name": "SESSION_ID",
"position": 8,
"default": "SYS_CONTEXT('USERENV', 'SESSIONID')",
"nullable": true,
"type": "VARCHAR2(128 BYTE)"
}
],
"indexes": [
{
"name": "PK_AUDIT_LOG",
"expressions": [
"LOG_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
],
"triggers": [
{
"name": "AUDIT_LOG_TRIGGER",
"body": "CREATE OR REPLACE TRIGGER audit_log_trigger\n BEFORE INSERT ON AUDIT_LOG\n FOR EACH ROW\nBEGIN\n IF :NEW.LOG_ID IS NULL THEN\n SELECT audit_log_seq.NEXTVAL INTO :NEW.LOG_ID FROM DUAL;\n END IF;\nEND;"
}
]
},
{
"name": "INVENTORY",
"columns": [
{
"name": "ITEM_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "ITEM_CODE",
"position": 2,
"type": "VARCHAR2(50 BYTE)"
},
{
"name": "DESCRIPTION",
"position": 3,
"nullable": true,
"type": "VARCHAR2(200 BYTE)"
},
{
"name": "QUANTITY_ON_HAND",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER"
},
{
"name": "UNIT_COST",
"position": 5,
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "LAST_UPDATED",
"position": 6,
"default": "CURRENT_TIMESTAMP",
"nullable": true,
"type": "TIMESTAMP(6)"
}
],
"indexes": [
{
"name": "PK_INVENTORY",
"expressions": [
"ITEM_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_INVENTORY_ITEM_CODE",
"expressions": [
"ITEM_CODE"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"triggers": [
{
"name": "INVENTORY_AUDIT_TRIGGER",
"body": "CREATE OR REPLACE TRIGGER inventory_audit_trigger\n AFTER INSERT OR UPDATE OR DELETE ON INVENTORY\n FOR EACH ROW\nBEGIN\n IF INSERTING THEN\n INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, NEW_VALUES)\n VALUES ('INVENTORY', 'INSERT', 'ID=' || :NEW.ITEM_ID || ',CODE=' || :NEW.ITEM_CODE);\n ELSIF UPDATING THEN\n INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES, NEW_VALUES)\n VALUES ('INVENTORY', 'UPDATE',\n 'ID=' || :OLD.ITEM_ID || ',QTY=' || :OLD.QUANTITY_ON_HAND,\n 'ID=' || :NEW.ITEM_ID || ',QTY=' || :NEW.QUANTITY_ON_HAND);\n ELSIF DELETING THEN\n INSERT INTO AUDIT_LOG (TABLE_NAME, OPERATION, OLD_VALUES)\n VALUES ('INVENTORY', 'DELETE', 'ID=' || :OLD.ITEM_ID || ',CODE=' || :OLD.ITEM_CODE);\n END IF;\nEND;"
}
]
}
],
"sequences": [
{
"name": "AUDIT_LOG_SEQ",
"start": "1000"
},
{
"name": "INVENTORY_SEQ",
"start": "1"
}
]
}
]
}
- description: views_complex
schema: |
CREATE TABLE SALES_TRANSACTIONS (
TRANSACTION_ID NUMBER PRIMARY KEY,
CUSTOMER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
SALE_DATE DATE NOT NULL,
QUANTITY NUMBER NOT NULL,
UNIT_PRICE NUMBER(10,2) NOT NULL,
DISCOUNT_AMOUNT NUMBER(10,2) DEFAULT 0,
TAX_AMOUNT NUMBER(10,2) DEFAULT 0,
TOTAL_AMOUNT NUMBER(12,2) NOT NULL,
SALES_REP_ID NUMBER,
REGION VARCHAR2(50)
);
-- Complex view with aggregations
CREATE VIEW MONTHLY_SALES_SUMMARY AS
SELECT
TO_CHAR(SALE_DATE, 'YYYY-MM') AS SALES_MONTH,
REGION,
COUNT(*) AS TRANSACTION_COUNT,
SUM(QUANTITY) AS TOTAL_QUANTITY,
SUM(TOTAL_AMOUNT) AS TOTAL_REVENUE,
AVG(TOTAL_AMOUNT) AS AVG_TRANSACTION_VALUE,
MIN(SALE_DATE) AS FIRST_SALE_DATE,
MAX(SALE_DATE) AS LAST_SALE_DATE
FROM SALES_TRANSACTIONS
WHERE SALE_DATE >= ADD_MONTHS(SYSDATE, -12)
GROUP BY TO_CHAR(SALE_DATE, 'YYYY-MM'), REGION
HAVING SUM(TOTAL_AMOUNT) > 1000
ORDER BY SALES_MONTH DESC, REGION;
-- View with joins and complex expressions
CREATE VIEW CUSTOMER_SALES_ANALYSIS AS
SELECT
st.CUSTOMER_ID,
COUNT(DISTINCT st.TRANSACTION_ID) AS TOTAL_TRANSACTIONS,
COUNT(DISTINCT TO_CHAR(st.SALE_DATE, 'YYYY-MM')) AS ACTIVE_MONTHS,
SUM(st.TOTAL_AMOUNT) AS LIFETIME_VALUE,
AVG(st.TOTAL_AMOUNT) AS AVG_TRANSACTION_VALUE,
MAX(st.SALE_DATE) AS LAST_PURCHASE_DATE,
CASE
WHEN MAX(st.SALE_DATE) >= SYSDATE - 30 THEN 'ACTIVE'
WHEN MAX(st.SALE_DATE) >= SYSDATE - 90 THEN 'RECENT'
ELSE 'INACTIVE'
END AS CUSTOMER_STATUS,
ROUND(SUM(st.TOTAL_AMOUNT) / NULLIF(COUNT(DISTINCT TO_CHAR(st.SALE_DATE, 'YYYY-MM')), 0), 2) AS MONTHLY_SPEND_RATE
FROM SALES_TRANSACTIONS st
WHERE st.SALE_DATE >= ADD_MONTHS(SYSDATE, -24)
GROUP BY st.CUSTOMER_ID
HAVING COUNT(*) >= 3;
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "SALES_TRANSACTIONS",
"columns": [
{
"name": "TRANSACTION_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "CUSTOMER_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "PRODUCT_ID",
"position": 3,
"type": "NUMBER"
},
{
"name": "SALE_DATE",
"position": 4,
"type": "DATE"
},
{
"name": "QUANTITY",
"position": 5,
"type": "NUMBER"
},
{
"name": "UNIT_PRICE",
"position": 6,
"type": "NUMBER(10,2)"
},
{
"name": "DISCOUNT_AMOUNT",
"position": 7,
"default": "0",
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "TAX_AMOUNT",
"position": 8,
"default": "0",
"nullable": true,
"type": "NUMBER(10,2)"
},
{
"name": "TOTAL_AMOUNT",
"position": 9,
"type": "NUMBER(12,2)"
},
{
"name": "SALES_REP_ID",
"position": 10,
"nullable": true,
"type": "NUMBER"
},
{
"name": "REGION",
"position": 11,
"nullable": true,
"type": "VARCHAR2(50 BYTE)"
}
],
"indexes": [
{
"name": "PK_SALES_TRANSACTIONS",
"expressions": [
"TRANSACTION_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
],
"views": [
{
"name": "CUSTOMER_SALES_ANALYSIS",
"definition": "SELECT\n st.CUSTOMER_ID,\n COUNT(DISTINCT st.TRANSACTION_ID) AS TOTAL_TRANSACTIONS,\n COUNT(DISTINCT TO_CHAR(st.SALE_DATE, 'YYYY-MM')) AS ACTIVE_MONTHS,\n SUM(st.TOTAL_AMOUNT) AS LIFETIME_VALUE,\n AVG(st.TOTAL_AMOUNT) AS AVG_TRANSACTION_VALUE,\n MAX(st.SALE_DATE) AS LAST_PURCHASE_DATE,\n CASE\n WHEN MAX(st.SALE_DATE) \u003e= SYSDATE - 30 THEN 'ACTIVE'\n WHEN MAX(st.SALE_DATE) \u003e= SYSDATE - 90 THEN 'RECENT'\n ELSE 'INACTIVE'\n END AS CUSTOMER_STATUS,\n ROUND(SUM(st.TOTAL_AMOUNT) / NULLIF(COUNT(DISTINCT TO_CHAR(st.SALE_DATE, 'YYYY-MM')), 0), 2) AS MONTHLY_SPEND_RATE\nFROM SALES_TRANSACTIONS st\nWHERE st.SALE_DATE \u003e= ADD_MONTHS(SYSDATE, -24)\nGROUP BY st.CUSTOMER_ID\nHAVING COUNT(*) \u003e= 3"
},
{
"name": "MONTHLY_SALES_SUMMARY",
"definition": "SELECT\n TO_CHAR(SALE_DATE, 'YYYY-MM') AS SALES_MONTH,\n REGION,\n COUNT(*) AS TRANSACTION_COUNT,\n SUM(QUANTITY) AS TOTAL_QUANTITY,\n SUM(TOTAL_AMOUNT) AS TOTAL_REVENUE,\n AVG(TOTAL_AMOUNT) AS AVG_TRANSACTION_VALUE,\n MIN(SALE_DATE) AS FIRST_SALE_DATE,\n MAX(SALE_DATE) AS LAST_SALE_DATE\nFROM SALES_TRANSACTIONS\nWHERE SALE_DATE \u003e= ADD_MONTHS(SYSDATE, -12)\nGROUP BY TO_CHAR(SALE_DATE, 'YYYY-MM'), REGION\nHAVING SUM(TOTAL_AMOUNT) \u003e 1000\nORDER BY SALES_MONTH DESC, REGION"
}
]
}
]
}
- description: materialized_views
schema: |
CREATE TABLE PRODUCT_SALES_DAILY (
SALE_DATE DATE NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
CATEGORY VARCHAR2(50) NOT NULL,
SALES_AMOUNT NUMBER(12,2) NOT NULL,
UNITS_SOLD NUMBER NOT NULL,
COST_OF_GOODS NUMBER(12,2) NOT NULL
);
-- Materialized view for performance (simplified for query rewrite support)
CREATE MATERIALIZED VIEW PRODUCT_PERFORMANCE_MV
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
PRODUCT_ID,
CATEGORY,
COUNT(*) AS SALES_DAYS,
SUM(SALES_AMOUNT) AS TOTAL_REVENUE,
SUM(UNITS_SOLD) AS TOTAL_UNITS,
SUM(COST_OF_GOODS) AS TOTAL_COST,
SUM(SALES_AMOUNT) - SUM(COST_OF_GOODS) AS TOTAL_PROFIT,
AVG(SALES_AMOUNT) AS AVG_DAILY_REVENUE,
MIN(SALE_DATE) AS FIRST_SALE_DATE,
MAX(SALE_DATE) AS LAST_SALE_DATE
FROM PRODUCT_SALES_DAILY
WHERE SALE_DATE >= ADD_MONTHS(SYSDATE, -12)
GROUP BY PRODUCT_ID, CATEGORY;
-- Create index on materialized view
CREATE INDEX idx_mv_category ON PRODUCT_PERFORMANCE_MV(CATEGORY);
CREATE INDEX idx_mv_total_profit ON PRODUCT_PERFORMANCE_MV(TOTAL_PROFIT);
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "PRODUCT_SALES_DAILY",
"columns": [
{
"name": "SALE_DATE",
"position": 1,
"type": "DATE"
},
{
"name": "PRODUCT_ID",
"position": 2,
"type": "NUMBER"
},
{
"name": "CATEGORY",
"position": 3,
"type": "VARCHAR2(50 BYTE)"
},
{
"name": "SALES_AMOUNT",
"position": 4,
"type": "NUMBER(12,2)"
},
{
"name": "UNITS_SOLD",
"position": 5,
"type": "NUMBER"
},
{
"name": "COST_OF_GOODS",
"position": 6,
"type": "NUMBER(12,2)"
}
]
}
],
"materialized_views": [
{
"name": "PRODUCT_PERFORMANCE_MV",
"definition": "SELECT\n PRODUCT_ID,\n CATEGORY,\n COUNT(*) AS SALES_DAYS,\n SUM(SALES_AMOUNT) AS TOTAL_REVENUE,\n SUM(UNITS_SOLD) AS TOTAL_UNITS,\n SUM(COST_OF_GOODS) AS TOTAL_COST,\n SUM(SALES_AMOUNT) - SUM(COST_OF_GOODS) AS TOTAL_PROFIT,\n AVG(SALES_AMOUNT) AS AVG_DAILY_REVENUE,\n MIN(SALE_DATE) AS FIRST_SALE_DATE,\n MAX(SALE_DATE) AS LAST_SALE_DATE\nFROM PRODUCT_SALES_DAILY\nWHERE SALE_DATE \u003e= ADD_MONTHS(SYSDATE, -12)\nGROUP BY PRODUCT_ID, CATEGORY\n",
"indexes": [
{
"name": "IDX_MV_CATEGORY",
"expressions": [
"CATEGORY"
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
},
{
"name": "IDX_MV_TOTAL_PROFIT",
"expressions": [
"TOTAL_PROFIT"
],
"descending": [
false
],
"type": "NORMAL",
"visible": true
}
]
}
]
}
]
}
- description: procedures_and_functions
schema: |
CREATE TABLE CUSTOMER_ACCOUNTS (
ACCOUNT_ID NUMBER PRIMARY KEY,
CUSTOMER_NAME VARCHAR2(100) NOT NULL,
ACCOUNT_TYPE VARCHAR2(20) NOT NULL,
BALANCE NUMBER(15,2) DEFAULT 0,
CREDIT_LIMIT NUMBER(15,2) DEFAULT 0,
STATUS VARCHAR2(20) DEFAULT 'ACTIVE',
CREATED_DATE DATE DEFAULT SYSDATE
);
-- Function to calculate available credit
CREATE OR REPLACE FUNCTION get_available_credit(p_account_id NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
v_balance NUMBER;
v_credit_limit NUMBER;
v_available_credit NUMBER;
BEGIN
SELECT BALANCE, CREDIT_LIMIT
INTO v_balance, v_credit_limit
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id;
v_available_credit := v_credit_limit - v_balance;
RETURN GREATEST(v_available_credit, 0);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END;
/
-- Procedure to update account balance
CREATE OR REPLACE PROCEDURE update_account_balance(
p_account_id IN NUMBER,
p_amount IN NUMBER,
p_transaction_type IN VARCHAR2,
p_result OUT VARCHAR2
)
IS
v_current_balance NUMBER;
v_new_balance NUMBER;
v_credit_limit NUMBER;
BEGIN
-- Get current balance and credit limit
SELECT BALANCE, CREDIT_LIMIT
INTO v_current_balance, v_credit_limit
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id
FOR UPDATE;
-- Calculate new balance
IF p_transaction_type = 'CREDIT' THEN
v_new_balance := v_current_balance + p_amount;
ELSIF p_transaction_type = 'DEBIT' THEN
v_new_balance := v_current_balance - p_amount;
-- Check credit limit
IF v_new_balance < (v_credit_limit * -1) THEN
p_result := 'ERROR: Transaction would exceed credit limit';
ROLLBACK;
RETURN;
END IF;
ELSE
p_result := 'ERROR: Invalid transaction type';
RETURN;
END IF;
-- Update balance
UPDATE CUSTOMER_ACCOUNTS
SET BALANCE = v_new_balance,
STATUS = CASE
WHEN v_new_balance < 0 THEN 'OVERDRAWN'
WHEN v_new_balance >= 0 THEN 'ACTIVE'
END
WHERE ACCOUNT_ID = p_account_id;
COMMIT;
p_result := 'SUCCESS: Balance updated to ' || v_new_balance;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_result := 'ERROR: Account not found';
WHEN OTHERS THEN
p_result := 'ERROR: ' || SQLERRM;
ROLLBACK;
END;
/
-- Function with complex logic
CREATE OR REPLACE FUNCTION calculate_account_risk_score(p_account_id NUMBER)
RETURN NUMBER
IS
v_balance NUMBER;
v_credit_limit NUMBER;
v_account_age NUMBER;
v_transaction_count NUMBER;
v_risk_score NUMBER := 0;
BEGIN
SELECT
BALANCE,
CREDIT_LIMIT,
SYSDATE - CREATED_DATE,
(SELECT COUNT(*) FROM AUDIT_LOG WHERE TABLE_NAME = 'CUSTOMER_ACCOUNTS')
INTO v_balance, v_credit_limit, v_account_age, v_transaction_count
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id;
-- Calculate risk based on multiple factors
-- Balance utilization (higher utilization = higher risk)
IF v_credit_limit > 0 THEN
v_risk_score := v_risk_score + (ABS(v_balance) / v_credit_limit) * 40;
END IF;
-- Account age (newer accounts = higher risk)
IF v_account_age < 30 THEN
v_risk_score := v_risk_score + 30;
ELSIF v_account_age < 90 THEN
v_risk_score := v_risk_score + 20;
ELSIF v_account_age < 365 THEN
v_risk_score := v_risk_score + 10;
END IF;
-- Transaction frequency (too few or too many = higher risk)
IF v_transaction_count < 5 THEN
v_risk_score := v_risk_score + 20;
ELSIF v_transaction_count > 100 THEN
v_risk_score := v_risk_score + 15;
END IF;
-- Negative balance penalty
IF v_balance < 0 THEN
v_risk_score := v_risk_score + 25;
END IF;
RETURN LEAST(v_risk_score, 100); -- Cap at 100
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 100; -- Max risk for non-existent accounts
WHEN OTHERS THEN
RETURN 50; -- Default moderate risk on errors
END;
/
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "CUSTOMER_ACCOUNTS",
"columns": [
{
"name": "ACCOUNT_ID",
"position": 1,
"type": "NUMBER"
},
{
"name": "CUSTOMER_NAME",
"position": 2,
"type": "VARCHAR2(100 BYTE)"
},
{
"name": "ACCOUNT_TYPE",
"position": 3,
"type": "VARCHAR2(20 BYTE)"
},
{
"name": "BALANCE",
"position": 4,
"default": "0",
"nullable": true,
"type": "NUMBER(15,2)"
},
{
"name": "CREDIT_LIMIT",
"position": 5,
"default": "0",
"nullable": true,
"type": "NUMBER(15,2)"
},
{
"name": "STATUS",
"position": 6,
"default": "'ACTIVE'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)"
},
{
"name": "CREATED_DATE",
"position": 7,
"default": "SYSDATE",
"nullable": true,
"type": "DATE"
}
],
"indexes": [
{
"name": "PK_CUSTOMER_ACCOUNTS",
"expressions": [
"ACCOUNT_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
]
}
],
"functions": [
{
"name": "CALCULATE_ACCOUNT_RISK_SCORE",
"definition": "FUNCTION calculate_account_risk_score(p_account_id NUMBER)\nRETURN NUMBER\nIS\n v_balance NUMBER;\n v_credit_limit NUMBER;\n v_account_age NUMBER;\n v_transaction_count NUMBER;\n v_risk_score NUMBER := 0;\nBEGIN\n SELECT\n BALANCE,\n CREDIT_LIMIT,\n SYSDATE - CREATED_DATE,\n (SELECT COUNT(*) FROM AUDIT_LOG WHERE TABLE_NAME = 'CUSTOMER_ACCOUNTS')\n INTO v_balance, v_credit_limit, v_account_age, v_transaction_count\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id;\n\n -- Calculate risk based on multiple factors\n -- Balance utilization (higher utilization = higher risk)\n IF v_credit_limit \u003e 0 THEN\n v_risk_score := v_risk_score + (ABS(v_balance) / v_credit_limit) * 40;\n END IF;\n\n -- Account age (newer accounts = higher risk)\n IF v_account_age \u003c 30 THEN\n v_risk_score := v_risk_score + 30;\n ELSIF v_account_age \u003c 90 THEN\n v_risk_score := v_risk_score + 20;\n ELSIF v_account_age \u003c 365 THEN\n v_risk_score := v_risk_score + 10;\n END IF;\n\n -- Transaction frequency (too few or too many = higher risk)\n IF v_transaction_count \u003c 5 THEN\n v_risk_score := v_risk_score + 20;\n ELSIF v_transaction_count \u003e 100 THEN\n v_risk_score := v_risk_score + 15;\n END IF;\n\n -- Negative balance penalty\n IF v_balance \u003c 0 THEN\n v_risk_score := v_risk_score + 25;\n END IF;\n\n RETURN LEAST(v_risk_score, 100); -- Cap at 100\n\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN 100; -- Max risk for non-existent accounts\n WHEN OTHERS THEN\n RETURN 50; -- Default moderate risk on errors\nEND;"
},
{
"name": "GET_AVAILABLE_CREDIT",
"definition": "FUNCTION get_available_credit(p_account_id NUMBER)\nRETURN NUMBER\nDETERMINISTIC\nIS\n v_balance NUMBER;\n v_credit_limit NUMBER;\n v_available_credit NUMBER;\nBEGIN\n SELECT BALANCE, CREDIT_LIMIT\n INTO v_balance, v_credit_limit\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id;\n\n v_available_credit := v_credit_limit - v_balance;\n\n RETURN GREATEST(v_available_credit, 0);\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n RETURN 0;\n WHEN OTHERS THEN\n RETURN -1;\nEND;"
}
],
"procedures": [
{
"name": "UPDATE_ACCOUNT_BALANCE",
"definition": "CREATE OR REPLACE PROCEDURE update_account_balance(\n p_account_id IN NUMBER,\n p_amount IN NUMBER,\n p_transaction_type IN VARCHAR2,\n p_result OUT VARCHAR2\n)\nIS\n v_current_balance NUMBER;\n v_new_balance NUMBER;\n v_credit_limit NUMBER;\nBEGIN\n -- Get current balance and credit limit\n SELECT BALANCE, CREDIT_LIMIT\n INTO v_current_balance, v_credit_limit\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id\n FOR UPDATE;\n\n -- Calculate new balance\n IF p_transaction_type = 'CREDIT' THEN\n v_new_balance := v_current_balance + p_amount;\n ELSIF p_transaction_type = 'DEBIT' THEN\n v_new_balance := v_current_balance - p_amount;\n\n -- Check credit limit\n IF v_new_balance \u003c (v_credit_limit * -1) THEN\n p_result := 'ERROR: Transaction would exceed credit limit';\n ROLLBACK;\n RETURN;\n END IF;\n ELSE\n p_result := 'ERROR: Invalid transaction type';\n RETURN;\n END IF;\n\n -- Update balance\n UPDATE CUSTOMER_ACCOUNTS\n SET BALANCE = v_new_balance,\n STATUS = CASE\n WHEN v_new_balance \u003c 0 THEN 'OVERDRAWN'\n WHEN v_new_balance \u003e= 0 THEN 'ACTIVE'\n END\n WHERE ACCOUNT_ID = p_account_id;\n\n COMMIT;\n p_result := 'SUCCESS: Balance updated to ' || v_new_balance;\n\nEXCEPTION\n WHEN NO_DATA_FOUND THEN\n p_result := 'ERROR: Account not found';\n WHEN OTHERS THEN\n p_result := 'ERROR: ' || SQLERRM;\n ROLLBACK;\nEND;"
}
]
}
]
}
- description: packages_comprehensive
schema: |
-- Package specification
CREATE OR REPLACE PACKAGE financial_utils AS
-- Constants
c_max_overdraft CONSTANT NUMBER := 5000;
c_default_interest_rate CONSTANT NUMBER := 0.05;
-- Types
TYPE account_summary_type IS RECORD (
account_id NUMBER,
balance NUMBER,
available_credit NUMBER,
risk_score NUMBER,
status VARCHAR2(20)
);
TYPE account_list_type IS TABLE OF account_summary_type;
-- Procedures
PROCEDURE calculate_interest(
p_account_id IN NUMBER,
p_days IN NUMBER DEFAULT 30,
p_interest_amount OUT NUMBER
);
PROCEDURE generate_statement(
p_account_id IN NUMBER,
p_statement_date IN DATE DEFAULT SYSDATE,
p_statement_text OUT CLOB
);
-- Functions
FUNCTION get_account_summary(p_account_id NUMBER) RETURN account_summary_type;
FUNCTION get_high_risk_accounts(p_risk_threshold NUMBER DEFAULT 70)
RETURN account_list_type PIPELINED;
FUNCTION format_currency(p_amount NUMBER) RETURN VARCHAR2;
FUNCTION validate_account_number(p_account_id NUMBER) RETURN BOOLEAN;
-- Overloaded functions
FUNCTION calculate_fee(p_transaction_type VARCHAR2) RETURN NUMBER;
FUNCTION calculate_fee(p_transaction_type VARCHAR2, p_amount NUMBER) RETURN NUMBER;
END financial_utils;
/
-- Package body
CREATE OR REPLACE PACKAGE BODY financial_utils AS
PROCEDURE calculate_interest(
p_account_id IN NUMBER,
p_days IN NUMBER DEFAULT 30,
p_interest_amount OUT NUMBER
) IS
v_balance NUMBER;
v_daily_rate NUMBER;
BEGIN
SELECT BALANCE INTO v_balance
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id;
v_daily_rate := c_default_interest_rate / 365;
IF v_balance < 0 THEN
p_interest_amount := ABS(v_balance) * v_daily_rate * p_days;
ELSE
p_interest_amount := 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_interest_amount := 0;
END calculate_interest;
PROCEDURE generate_statement(
p_account_id IN NUMBER,
p_statement_date IN DATE DEFAULT SYSDATE,
p_statement_text OUT CLOB
) IS
v_account account_summary_type;
BEGIN
v_account := get_account_summary(p_account_id);
p_statement_text := 'ACCOUNT STATEMENT' || CHR(10) ||
'Account ID: ' || v_account.account_id || CHR(10) ||
'Statement Date: ' || TO_CHAR(p_statement_date, 'DD-MON-YYYY') || CHR(10) ||
'Current Balance: ' || format_currency(v_account.balance) || CHR(10) ||
'Available Credit: ' || format_currency(v_account.available_credit) || CHR(10) ||
'Account Status: ' || v_account.status || CHR(10) ||
'Risk Score: ' || v_account.risk_score;
END generate_statement;
FUNCTION get_account_summary(p_account_id NUMBER) RETURN account_summary_type IS
v_summary account_summary_type;
BEGIN
SELECT ACCOUNT_ID, BALANCE, STATUS
INTO v_summary.account_id, v_summary.balance, v_summary.status
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id;
v_summary.available_credit := get_available_credit(p_account_id);
v_summary.risk_score := calculate_account_risk_score(p_account_id);
RETURN v_summary;
END get_account_summary;
FUNCTION get_high_risk_accounts(p_risk_threshold NUMBER DEFAULT 70)
RETURN account_list_type PIPELINED IS
v_summary account_summary_type;
BEGIN
FOR rec IN (SELECT ACCOUNT_ID FROM CUSTOMER_ACCOUNTS WHERE STATUS = 'ACTIVE') LOOP
v_summary := get_account_summary(rec.ACCOUNT_ID);
IF v_summary.risk_score >= p_risk_threshold THEN
PIPE ROW(v_summary);
END IF;
END LOOP;
RETURN;
END get_high_risk_accounts;
FUNCTION format_currency(p_amount NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(p_amount, 'FM$999,999,990.00');
END format_currency;
FUNCTION validate_account_number(p_account_id NUMBER) RETURN BOOLEAN IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM CUSTOMER_ACCOUNTS
WHERE ACCOUNT_ID = p_account_id;
RETURN v_count > 0;
END validate_account_number;
FUNCTION calculate_fee(p_transaction_type VARCHAR2) RETURN NUMBER IS
BEGIN
CASE p_transaction_type
WHEN 'TRANSFER' THEN RETURN 2.50;
WHEN 'WITHDRAWAL' THEN RETURN 1.00;
WHEN 'DEPOSIT' THEN RETURN 0;
ELSE RETURN 5.00;
END CASE;
END calculate_fee;
FUNCTION calculate_fee(p_transaction_type VARCHAR2, p_amount NUMBER) RETURN NUMBER IS
v_base_fee NUMBER;
v_percentage_fee NUMBER;
BEGIN
v_base_fee := calculate_fee(p_transaction_type);
-- Add percentage-based fee for large amounts
IF p_amount > 10000 THEN
v_percentage_fee := p_amount * 0.001; -- 0.1%
RETURN v_base_fee + v_percentage_fee;
ELSE
RETURN v_base_fee;
END IF;
END calculate_fee;
END financial_utils;
/
metadata: |
{
"schemas": [
{
"packages": [
{
"name": "FINANCIAL_UTILS",
"definition": "CREATE OR REPLACE PACKAGE financial_utils AS\n -- Constants\n c_max_overdraft CONSTANT NUMBER := 5000;\n c_default_interest_rate CONSTANT NUMBER := 0.05;\n\n -- Types\n TYPE account_summary_type IS RECORD (\n account_id NUMBER,\n balance NUMBER,\n available_credit NUMBER,\n risk_score NUMBER,\n status VARCHAR2(20)\n );\n\n TYPE account_list_type IS TABLE OF account_summary_type;\n\n -- Procedures\n PROCEDURE calculate_interest(\n p_account_id IN NUMBER,\n p_days IN NUMBER DEFAULT 30,\n p_interest_amount OUT NUMBER\n );\n\n PROCEDURE generate_statement(\n p_account_id IN NUMBER,\n p_statement_date IN DATE DEFAULT SYSDATE,\n p_statement_text OUT CLOB\n );\n\n -- Functions\n FUNCTION get_account_summary(p_account_id NUMBER) RETURN account_summary_type;\n\n FUNCTION get_high_risk_accounts(p_risk_threshold NUMBER DEFAULT 70)\n RETURN account_list_type PIPELINED;\n\n FUNCTION format_currency(p_amount NUMBER) RETURN VARCHAR2;\n\n FUNCTION validate_account_number(p_account_id NUMBER) RETURN BOOLEAN;\n\n -- Overloaded functions\n FUNCTION calculate_fee(p_transaction_type VARCHAR2) RETURN NUMBER;\n FUNCTION calculate_fee(p_transaction_type VARCHAR2, p_amount NUMBER) RETURN NUMBER;\n\nEND financial_utils;\n\nCREATE OR REPLACE PACKAGE BODY financial_utils AS\n\n PROCEDURE calculate_interest(\n p_account_id IN NUMBER,\n p_days IN NUMBER DEFAULT 30,\n p_interest_amount OUT NUMBER\n ) IS\n v_balance NUMBER;\n v_daily_rate NUMBER;\n BEGIN\n SELECT BALANCE INTO v_balance\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id;\n\n v_daily_rate := c_default_interest_rate / 365;\n\n IF v_balance \u003c 0 THEN\n p_interest_amount := ABS(v_balance) * v_daily_rate * p_days;\n ELSE\n p_interest_amount := 0;\n END IF;\n EXCEPTION\n WHEN NO_DATA_FOUND THEN\n p_interest_amount := 0;\n END calculate_interest;\n\n PROCEDURE generate_statement(\n p_account_id IN NUMBER,\n p_statement_date IN DATE DEFAULT SYSDATE,\n p_statement_text OUT CLOB\n ) IS\n v_account account_summary_type;\n BEGIN\n v_account := get_account_summary(p_account_id);\n\n p_statement_text := 'ACCOUNT STATEMENT' || CHR(10) ||\n 'Account ID: ' || v_account.account_id || CHR(10) ||\n 'Statement Date: ' || TO_CHAR(p_statement_date, 'DD-MON-YYYY') || CHR(10) ||\n 'Current Balance: ' || format_currency(v_account.balance) || CHR(10) ||\n 'Available Credit: ' || format_currency(v_account.available_credit) || CHR(10) ||\n 'Account Status: ' || v_account.status || CHR(10) ||\n 'Risk Score: ' || v_account.risk_score;\n END generate_statement;\n\n FUNCTION get_account_summary(p_account_id NUMBER) RETURN account_summary_type IS\n v_summary account_summary_type;\n BEGIN\n SELECT ACCOUNT_ID, BALANCE, STATUS\n INTO v_summary.account_id, v_summary.balance, v_summary.status\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id;\n\n v_summary.available_credit := get_available_credit(p_account_id);\n v_summary.risk_score := calculate_account_risk_score(p_account_id);\n\n RETURN v_summary;\n END get_account_summary;\n\n FUNCTION get_high_risk_accounts(p_risk_threshold NUMBER DEFAULT 70)\n RETURN account_list_type PIPELINED IS\n v_summary account_summary_type;\n BEGIN\n FOR rec IN (SELECT ACCOUNT_ID FROM CUSTOMER_ACCOUNTS WHERE STATUS = 'ACTIVE') LOOP\n v_summary := get_account_summary(rec.ACCOUNT_ID);\n IF v_summary.risk_score \u003e= p_risk_threshold THEN\n PIPE ROW(v_summary);\n END IF;\n END LOOP;\n RETURN;\n END get_high_risk_accounts;\n\n FUNCTION format_currency(p_amount NUMBER) RETURN VARCHAR2 IS\n BEGIN\n RETURN TO_CHAR(p_amount, 'FM$999,999,990.00');\n END format_currency;\n\n FUNCTION validate_account_number(p_account_id NUMBER) RETURN BOOLEAN IS\n v_count NUMBER;\n BEGIN\n SELECT COUNT(*) INTO v_count\n FROM CUSTOMER_ACCOUNTS\n WHERE ACCOUNT_ID = p_account_id;\n\n RETURN v_count \u003e 0;\n END validate_account_number;\n\n FUNCTION calculate_fee(p_transaction_type VARCHAR2) RETURN NUMBER IS\n BEGIN\n CASE p_transaction_type\n WHEN 'TRANSFER' THEN RETURN 2.50;\n WHEN 'WITHDRAWAL' THEN RETURN 1.00;\n WHEN 'DEPOSIT' THEN RETURN 0;\n ELSE RETURN 5.00;\n END CASE;\n END calculate_fee;\n\n FUNCTION calculate_fee(p_transaction_type VARCHAR2, p_amount NUMBER) RETURN NUMBER IS\n v_base_fee NUMBER;\n v_percentage_fee NUMBER;\n BEGIN\n v_base_fee := calculate_fee(p_transaction_type);\n\n -- Add percentage-based fee for large amounts\n IF p_amount \u003e 10000 THEN\n v_percentage_fee := p_amount * 0.001; -- 0.1%\n RETURN v_base_fee + v_percentage_fee;\n ELSE\n RETURN v_base_fee;\n END IF;\n END calculate_fee;\n\nEND financial_utils;"
}
]
}
]
}
- description: table_comments_and_documentation
schema: |
CREATE TABLE PRODUCT_CATALOG (
PRODUCT_ID NUMBER PRIMARY KEY,
SKU VARCHAR2(50) UNIQUE NOT NULL,
PRODUCT_NAME VARCHAR2(200) NOT NULL,
DESCRIPTION CLOB,
CATEGORY_ID NUMBER NOT NULL,
PRICE NUMBER(10,2) NOT NULL,
COST NUMBER(10,2),
WEIGHT NUMBER(8,3),
DIMENSIONS VARCHAR2(100),
COLOR VARCHAR2(50),
PRODUCT_SIZE VARCHAR2(20),
BRAND VARCHAR2(100),
SUPPLIER_ID NUMBER,
IS_ACTIVE NUMBER(1) DEFAULT 1,
STOCK_QUANTITY NUMBER DEFAULT 0,
REORDER_LEVEL NUMBER DEFAULT 10,
CREATED_DATE DATE DEFAULT SYSDATE,
CREATED_BY VARCHAR2(100) DEFAULT USER,
LAST_UPDATED DATE DEFAULT SYSDATE,
UPDATED_BY VARCHAR2(100) DEFAULT USER,
VERSION_NUMBER NUMBER DEFAULT 1
);
-- Table comments
COMMENT ON TABLE PRODUCT_CATALOG IS 'Master catalog of all products available for sale. Contains detailed product information including pricing, inventory, and supplier details.';
-- Column comments
COMMENT ON COLUMN PRODUCT_CATALOG.PRODUCT_ID IS 'Unique identifier for the product. Primary key generated from sequence.';
COMMENT ON COLUMN PRODUCT_CATALOG.SKU IS 'Stock Keeping Unit - unique alphanumeric code for inventory tracking';
COMMENT ON COLUMN PRODUCT_CATALOG.PRODUCT_NAME IS 'Display name of the product as shown to customers';
COMMENT ON COLUMN PRODUCT_CATALOG.DESCRIPTION IS 'Detailed product description including features and specifications';
COMMENT ON COLUMN PRODUCT_CATALOG.CATEGORY_ID IS 'Foreign key reference to PRODUCT_CATEGORIES table';
COMMENT ON COLUMN PRODUCT_CATALOG.PRICE IS 'Current selling price in base currency (USD)';
COMMENT ON COLUMN PRODUCT_CATALOG.COST IS 'Cost of goods sold - used for profit margin calculations';
COMMENT ON COLUMN PRODUCT_CATALOG.WEIGHT IS 'Product weight in kilograms - used for shipping calculations';
COMMENT ON COLUMN PRODUCT_CATALOG.DIMENSIONS IS 'Product dimensions in format: Length x Width x Height (cm)';
COMMENT ON COLUMN PRODUCT_CATALOG.COLOR IS 'Primary color of the product';
COMMENT ON COLUMN PRODUCT_CATALOG.PRODUCT_SIZE IS 'Size designation (S, M, L, XL, etc.)';
COMMENT ON COLUMN PRODUCT_CATALOG.BRAND IS 'Brand or manufacturer name';
COMMENT ON COLUMN PRODUCT_CATALOG.SUPPLIER_ID IS 'Reference to primary supplier in SUPPLIERS table';
COMMENT ON COLUMN PRODUCT_CATALOG.IS_ACTIVE IS 'Flag indicating if product is active (1) or discontinued (0)';
COMMENT ON COLUMN PRODUCT_CATALOG.STOCK_QUANTITY IS 'Current inventory quantity on hand';
COMMENT ON COLUMN PRODUCT_CATALOG.REORDER_LEVEL IS 'Minimum stock level that triggers reorder process';
COMMENT ON COLUMN PRODUCT_CATALOG.CREATED_DATE IS 'Date when product record was first created';
COMMENT ON COLUMN PRODUCT_CATALOG.CREATED_BY IS 'Username of person who created the record';
COMMENT ON COLUMN PRODUCT_CATALOG.LAST_UPDATED IS 'Date of most recent update to any field';
COMMENT ON COLUMN PRODUCT_CATALOG.UPDATED_BY IS 'Username of person who last modified the record';
COMMENT ON COLUMN PRODUCT_CATALOG.VERSION_NUMBER IS 'Optimistic locking version number for concurrent update control';
CREATE TABLE ORDER_LINE_ITEMS (
LINE_ITEM_ID NUMBER PRIMARY KEY,
ORDER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
QUANTITY NUMBER NOT NULL,
UNIT_PRICE NUMBER(10,2) NOT NULL,
DISCOUNT_AMOUNT NUMBER(10,2) DEFAULT 0,
TAX_AMOUNT NUMBER(10,2) DEFAULT 0,
LINE_TOTAL NUMBER(12,2) NOT NULL,
SPECIAL_INSTRUCTIONS VARCHAR2(500),
FULFILLMENT_STATUS VARCHAR2(20) DEFAULT 'PENDING'
);
COMMENT ON TABLE ORDER_LINE_ITEMS IS 'Individual line items for customer orders. Each row represents one product within an order.';
COMMENT ON COLUMN ORDER_LINE_ITEMS.LINE_ITEM_ID IS 'Unique identifier for this line item';
COMMENT ON COLUMN ORDER_LINE_ITEMS.ORDER_ID IS 'Reference to parent order in ORDERS table';
COMMENT ON COLUMN ORDER_LINE_ITEMS.PRODUCT_ID IS 'Reference to product in PRODUCT_CATALOG table';
COMMENT ON COLUMN ORDER_LINE_ITEMS.QUANTITY IS 'Number of units ordered';
COMMENT ON COLUMN ORDER_LINE_ITEMS.UNIT_PRICE IS 'Price per unit at time of order (may differ from current catalog price)';
COMMENT ON COLUMN ORDER_LINE_ITEMS.DISCOUNT_AMOUNT IS 'Total discount applied to this line item';
COMMENT ON COLUMN ORDER_LINE_ITEMS.TAX_AMOUNT IS 'Total tax amount for this line item';
COMMENT ON COLUMN ORDER_LINE_ITEMS.LINE_TOTAL IS 'Calculated total: (quantity * unit_price) - discount + tax';
COMMENT ON COLUMN ORDER_LINE_ITEMS.SPECIAL_INSTRUCTIONS IS 'Customer-specific instructions for this item';
COMMENT ON COLUMN ORDER_LINE_ITEMS.FULFILLMENT_STATUS IS 'Status: PENDING, PICKED, PACKED, SHIPPED, DELIVERED, RETURNED';
metadata: |
{
"schemas": [
{
"tables": [
{
"name": "ORDER_LINE_ITEMS",
"columns": [
{
"name": "LINE_ITEM_ID",
"position": 1,
"type": "NUMBER",
"comment": "Unique identifier for this line item"
},
{
"name": "ORDER_ID",
"position": 2,
"type": "NUMBER",
"comment": "Reference to parent order in ORDERS table"
},
{
"name": "PRODUCT_ID",
"position": 3,
"type": "NUMBER",
"comment": "Reference to product in PRODUCT_CATALOG table"
},
{
"name": "QUANTITY",
"position": 4,
"type": "NUMBER",
"comment": "Number of units ordered"
},
{
"name": "UNIT_PRICE",
"position": 5,
"type": "NUMBER(10,2)",
"comment": "Price per unit at time of order (may differ from current catalog price)"
},
{
"name": "DISCOUNT_AMOUNT",
"position": 6,
"default": "0",
"nullable": true,
"type": "NUMBER(10,2)",
"comment": "Total discount applied to this line item"
},
{
"name": "TAX_AMOUNT",
"position": 7,
"default": "0",
"nullable": true,
"type": "NUMBER(10,2)",
"comment": "Total tax amount for this line item"
},
{
"name": "LINE_TOTAL",
"position": 8,
"type": "NUMBER(12,2)",
"comment": "Calculated total: (quantity * unit_price) - discount + tax"
},
{
"name": "SPECIAL_INSTRUCTIONS",
"position": 9,
"nullable": true,
"type": "VARCHAR2(500 BYTE)",
"comment": "Customer-specific instructions for this item"
},
{
"name": "FULFILLMENT_STATUS",
"position": 10,
"default": "'PENDING'",
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"comment": "Status: PENDING, PICKED, PACKED, SHIPPED, DELIVERED, RETURNED"
}
],
"indexes": [
{
"name": "PK_ORDER_LINE_ITEMS",
"expressions": [
"LINE_ITEM_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
}
],
"comment": "Individual line items for customer orders. Each row represents one product within an order."
},
{
"name": "PRODUCT_CATALOG",
"columns": [
{
"name": "PRODUCT_ID",
"position": 1,
"type": "NUMBER",
"comment": "Unique identifier for the product. Primary key generated from sequence."
},
{
"name": "SKU",
"position": 2,
"type": "VARCHAR2(50 BYTE)",
"comment": "Stock Keeping Unit - unique alphanumeric code for inventory tracking"
},
{
"name": "PRODUCT_NAME",
"position": 3,
"type": "VARCHAR2(200 BYTE)",
"comment": "Display name of the product as shown to customers"
},
{
"name": "DESCRIPTION",
"position": 4,
"nullable": true,
"type": "CLOB",
"comment": "Detailed product description including features and specifications"
},
{
"name": "CATEGORY_ID",
"position": 5,
"type": "NUMBER",
"comment": "Foreign key reference to PRODUCT_CATEGORIES table"
},
{
"name": "PRICE",
"position": 6,
"type": "NUMBER(10,2)",
"comment": "Current selling price in base currency (USD)"
},
{
"name": "COST",
"position": 7,
"nullable": true,
"type": "NUMBER(10,2)",
"comment": "Cost of goods sold - used for profit margin calculations"
},
{
"name": "WEIGHT",
"position": 8,
"nullable": true,
"type": "NUMBER(8,3)",
"comment": "Product weight in kilograms - used for shipping calculations"
},
{
"name": "DIMENSIONS",
"position": 9,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"comment": "Product dimensions in format: Length x Width x Height (cm)"
},
{
"name": "COLOR",
"position": 10,
"nullable": true,
"type": "VARCHAR2(50 BYTE)",
"comment": "Primary color of the product"
},
{
"name": "PRODUCT_SIZE",
"position": 11,
"nullable": true,
"type": "VARCHAR2(20 BYTE)",
"comment": "Size designation (S, M, L, XL, etc.)"
},
{
"name": "BRAND",
"position": 12,
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"comment": "Brand or manufacturer name"
},
{
"name": "SUPPLIER_ID",
"position": 13,
"nullable": true,
"type": "NUMBER",
"comment": "Reference to primary supplier in SUPPLIERS table"
},
{
"name": "IS_ACTIVE",
"position": 14,
"default": "1",
"nullable": true,
"type": "NUMBER(1)",
"comment": "Flag indicating if product is active (1) or discontinued (0)"
},
{
"name": "STOCK_QUANTITY",
"position": 15,
"default": "0",
"nullable": true,
"type": "NUMBER",
"comment": "Current inventory quantity on hand"
},
{
"name": "REORDER_LEVEL",
"position": 16,
"default": "10",
"nullable": true,
"type": "NUMBER",
"comment": "Minimum stock level that triggers reorder process"
},
{
"name": "CREATED_DATE",
"position": 17,
"default": "SYSDATE",
"nullable": true,
"type": "DATE",
"comment": "Date when product record was first created"
},
{
"name": "CREATED_BY",
"position": 18,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"comment": "Username of person who created the record"
},
{
"name": "LAST_UPDATED",
"position": 19,
"default": "SYSDATE",
"nullable": true,
"type": "DATE",
"comment": "Date of most recent update to any field"
},
{
"name": "UPDATED_BY",
"position": 20,
"default": "USER",
"nullable": true,
"type": "VARCHAR2(100 BYTE)",
"comment": "Username of person who last modified the record"
},
{
"name": "VERSION_NUMBER",
"position": 21,
"default": "1",
"nullable": true,
"type": "NUMBER",
"comment": "Optimistic locking version number for concurrent update control"
}
],
"indexes": [
{
"name": "PK_PRODUCT_CATALOG",
"expressions": [
"PRODUCT_ID"
],
"type": "NORMAL",
"unique": true,
"primary": true,
"visible": true,
"is_constraint": true
},
{
"name": "UK_PRODUCT_CATALOG_SKU",
"expressions": [
"SKU"
],
"type": "NORMAL",
"unique": true,
"visible": true,
"is_constraint": true
}
],
"comment": "Master catalog of all products available for sale. Contains detailed product information including pricing, inventory, and supplier details."
}
]
}
]
}