ThatJeffSmith

Oracle SQL Developer: Configuring Object DDL Scripts

Social:

One of the more popular features in SQL Developer is the ability to generate the CREATE DDL script for any object. If you open an object editor, the SQL page shows this script.

You'll see this 'SQL' page for each type of database object editor in SQL Developer

You’ll see this ‘SQL’ page for each type of database object editor in SQL Developer

Now, what many of you don’t realize is that the way this code can be generated is configurable.

The requests I am most frequently asked to demo here are:

  1. No storage parameters
  2. No schema notation
  3. Show me the grants!

Actually, there’s another request that I won’t go into detail here, but the quoted identifiers when they are not necessary…that’s also on MY wish list :)

How to Configure the DDL Generation

Open the preferences. Go to the database page, then the utilities. And then, go to the Export page.

From there, the very top of that preference panel specifies how the DDL will be generated when you use features like the object editors, the Export wizard, and the Cart.

Grants aren't enabled by default.

Grants aren’t enabled by default.

Once I make my changes, I can just refresh my SQL page on my table editor.

And voila, my code is ready:

 
  CREATE TABLE EMPLOYEES 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20 BYTE), 
	LAST_NAME VARCHAR2(25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE, 
	EMAIL VARCHAR2(25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE, 
	PHONE_NUMBER VARCHAR2(20 BYTE), 
	HIRE_DATE DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE, 
	JOB_ID VARCHAR2(10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL ENABLE, 
	SALARY NUMBER(8,2), 
	COMMISSION_PCT NUMBER(2,2), 
	MANAGER_ID NUMBER(6,0), 
	DEPARTMENT_ID NUMBER(4,0), 
	 CONSTRAINT EMP_SALARY_MIN CHECK (salary > 0) ENABLE, 
	 CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL) ENABLE, 
	 CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID) ENABLE, 
	 CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID)
	  REFERENCES DEPARTMENTS (DEPARTMENT_ID) ENABLE, 
	 CONSTRAINT EMP_JOB_FK FOREIGN KEY (JOB_ID)
	  REFERENCES JOBS (JOB_ID) ENABLE, 
	 CONSTRAINT EMP_MANAGER_FK FOREIGN KEY (MANAGER_ID)
	  REFERENCES EMPLOYEES (EMPLOYEE_ID) ENABLE
   ) ;
 
   COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS 'Primary key of employees table.';
   COMMENT ON COLUMN EMPLOYEES.FIRST_NAME IS 'First name of the employee. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.LAST_NAME IS 'Last name of the employee. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.EMAIL IS 'Email id of the employee';
   COMMENT ON COLUMN EMPLOYEES.PHONE_NUMBER IS 'Phone number of the employee; includes country code and area code';
   COMMENT ON COLUMN EMPLOYEES.HIRE_DATE IS 'Date when the employee started on this job. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.JOB_ID IS 'Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.';
   COMMENT ON COLUMN EMPLOYEES.SALARY IS 'Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)';
   COMMENT ON COLUMN EMPLOYEES.COMMISSION_PCT IS 'Commission percentage of the employee; Only employees in sales
department elgible for commission percentage';
   COMMENT ON COLUMN EMPLOYEES.MANAGER_ID IS 'Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)';
   COMMENT ON COLUMN EMPLOYEES.DEPARTMENT_ID IS 'Department id where employee works; foreign key to department_id
column of the departments table';
   COMMENT ON TABLE EMPLOYEES  IS 'employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.';
 
  CREATE INDEX EMP_DEPARTMENT_IX ON EMPLOYEES (DEPARTMENT_ID) 
  ;
 
  CREATE INDEX EMP_JOB_IX ON EMPLOYEES (JOB_ID) 
  ;
 
  CREATE INDEX EMP_MANAGER_IX ON EMPLOYEES (MANAGER_ID) 
  ;
 
  CREATE INDEX EMP_NAME_IX ON EMPLOYEES (LAST_NAME, FIRST_NAME) 
  ;
 
  CREATE OR REPLACE TRIGGER SECURE_EMPLOYEES 
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/
ALTER TRIGGER SECURE_EMPLOYEES DISABLE;
 
  CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY 
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:OLD.employee_id, :OLD.hire_date, sysdate,
                  :OLD.job_id, :OLD.department_id);
END;
/
ALTER TRIGGER UPDATE_JOB_HISTORY ENABLE;
 
 
  GRANT SELECT ON EMPLOYEES TO DOES_NOT_OWN_ANYTHING;
  GRANT SELECT ON EMPLOYEES TO LOW_PRIVS;
  GRANT REFERENCES ON EMPLOYEES TO OE;
  GRANT SELECT ON EMPLOYEES TO OE;

But I Need Even More Options

Then might I suggest the Data Modeler?

Add your object(s) to a relational model, and then generate the DDL.

You have a few more options here. And you can specify if you want those darn quoted identifiers or not, e.g. CREATE OR REPLACE TABLE X vs CREATE OR REPLACE TABLE “X”.

More options!

More options!

One last thing.

If you don’t open the Physical model, the DDL generated won’t include the information from there. For example the triggers and tablespaces won’t be there. So you have even more flexibility.

Related Posts Plugin for WordPress, Blogger...
Social:

Similar Posts by Content Area: , , ,