Writing Codes for Human Beings

Writing codes, in a human understandable format is very important thing in software engineering. Choosing good names for variables, methods, tables, columns and etc. is one of them. Writing comments *always* is not something good. It is another sign of code smell. Anyway, I will not go deep inside this concept. May be later :)

Now, I want to a simple way to make your codes in more readable form. This is a very simple trick. If you know the capabilities of your toolkit (I mean, programming language), you can do such these tricks very quickly.

Suppose, you have a scheduled job which runs every 15 minutes. It always searches some tables, 15 minutes before SYSDATE. (Actually, in production environment, you store last execution time of your program or you have a wise algorithm for selecting ). You can do this such a date arithmetic as shown below:

select date '2012-01-20' - ( (1*60*15) / 86400 ) from dual;

But this is not readable .The numbers can be difficult to read. You can use some constants( like, instead of 86400 you can define a constant SECONDS_IN_A_DAY) ; but instead of this, you can use a very simple function (to_dsinterval) as shown below:

select date '2012-01-20' - to_dsinterval('00 00:15:00') from dual;

This usage is more human readable. 0 days, 0 hours, 15 minutes and 0 seconds(actually 15 minutes).

Knowledge is power, please do not forget...

4 Ways to Create Primary Key Constraints

Let's do it :)


--- 1. METHOD ) Name implicit - Creation implicit 
CREATE TABLE customer 
  ( 
     id         NUMBER PRIMARY KEY, 
     first_name VARCHAR2(40), 
     last_name  VARCHAR2(40) 
  ); 

SELECT cons.constraint_name 
FROM   all_constraints cons, 
       all_cons_columns cc 
WHERE  cons.constraint_name = cc.constraint_name 
       AND cons.owner = cc.owner 
       AND cons.table_name = 'CUSTOMER' 
       AND cc.column_name = 'ID'; 

--- SYS_C007758 
INSERT INTO customer 
            (id) 
VALUES     (1); 

INSERT INTO customer 
            (id) 
VALUES     (1); 

--- ORA-00001: unique constraint (HR.SYS_C007758) violated 
------------------------------- 
DROP TABLE customer; 

--- 2. METHOD ) Name explicit - Creation implicit 
CREATE TABLE customer 
  ( 
     id         NUMBER, 
     first_name VARCHAR2(40), 
     last_name  VARCHAR2(40), 
     CONSTRAINT pk_customer PRIMARY KEY(id) 
  ); 

SELECT cons.constraint_name 
FROM   all_constraints cons, 
       all_cons_columns cc 
WHERE  cons.constraint_name = cc.constraint_name 
       AND cons.owner = cc.owner 
       AND cons.table_name = 'CUSTOMER' 
       AND cc.column_name = 'ID'; 

--- PK_CUSTOMER 
INSERT INTO customer 
            (id) 
VALUES     (1); 

INSERT INTO customer 
            (id) 
VALUES     (1); 

--- ORA-00001: unique constraint (HR.PK_CUSTOMER) violated 
------------------------------- 
DROP TABLE customer; 

--- 3. METHOD ) Name implicit - Creation explicit 
CREATE TABLE customer 
  ( 
     id         NUMBER, 
     first_name VARCHAR2(40), 
     last_name  VARCHAR2(40) 
  ); 

ALTER TABLE customer 
  MODIFY id PRIMARY KEY; 

SELECT cons.constraint_name 
FROM   all_constraints cons, 
       all_cons_columns cc 
WHERE  cons.constraint_name = cc.constraint_name 
       AND cons.owner = cc.owner 
       AND cons.table_name = 'CUSTOMER' 
       AND cc.column_name = 'ID'; 

--- SYS_C007753 
------------------------------- 
DROP TABLE customer; 

--- 4. METHOD ) Name explicit - Creation explicit 
CREATE TABLE customer 
  ( 
     id         NUMBER, 
     first_name VARCHAR2(40), 
     last_name  VARCHAR2(40) 
  ); 

ALTER TABLE customer 
  ADD CONSTRAINT pk_customer PRIMARY KEY(id); 

SELECT cons.constraint_name 
FROM   all_constraints cons, 
       all_cons_columns cc 
WHERE  cons.constraint_name = cc.constraint_name 
       AND cons.owner = cc.owner 
       AND cons.table_name = 'CUSTOMER' 
       AND cc.column_name = 'ID'; 
--- PK_CUSTOMER