Guaranteed way of creating proper SQL Scripts

Published Wed 17 Mar, 04:20 by admin in oracle

Creating multiple tables views in a single shot

Most developers end up writing their DDL (data definition ) scripts that create various objects related to their design. However there is a great feature in Oracle (existed for a while) that allows the script act as single unit of work (much like a transaction but on Definition level) which is really useful when the script is big and contains quite a few tables and/or views.

Note:Only Create table, create view and grant statements are allowed to be executed as a single operation (it hasn't changed in Oracle 11g

A good example of use (from Oracle):

CREATE SCHEMA AUTHORIZATION scott
    CREATE TABLE dept (
        deptno NUMBER(3,0) PRIMARY KEY,
        dname VARCHAR2(15),
        loc VARCHAR2(25))
    CREATE TABLE emp (
        empno NUMBER(5,0) PRIMARY KEY,
        ename VARCHAR2(15) NOT NULL,
        job VARCHAR2(10),
        mgr NUMBER(5,0),
        hiredate DATE DEFAULT (sysdate),
        sal NUMBER(7,2),
        comm NUMBER(7,2),
        deptno NUMBER(3,0) NOT NULL
        CONSTRAINT dept_fkey REFERENCES dept)
   CREATE VIEW sales_staff AS
        SELECT empno, ename, sal, comm
        FROM emp
        WHERE deptno = 30
        WITH CHECK OPTION CONSTRAINT sales_staff_cnst
        GRANT SELECT ON sales_staff TO human_resources;

If something fails , say a column is too long or default value is of unacceptable type, the whole operation will be rolled back.