Create a table with only scale specification for a column

Hi All,

Just came across a peculiar way of specifying only the column scale and not its precision, so thought of sharing with all. It looks pretty simple but could be a handy tip for table designs.

Scenario: Create a table CONT_EMPS where Salary column must store values upto 2 places of decimals (i.e. scale is 2). Here, note that no precision has been specified for the Salary column. Lets see how to do it.

SQL> CREATE TABLE CONT_EMPS
2 (EMPID NUMBER,
3 EMPNAME VARCHAR2(256),
4 SAL NUMBER(*,2))
5 /
Table created.

SQL> DESC CONT_EMPS
Name         Null?    Type
------------ -------- ----------------------------
EMPID                 NUMBER
EMPNAME               VARCHAR2(256)
SAL                   NUMBER(38,2)

When I specify '*' in place of precision, Oracle treats it as the maximum precision allowed for the column. Additionally, this convention doesn't applies to PL/SQL data type declaration.
 
Bye for now.
Saurabh

Related articles

• PL/SQL Part-1 (oramasters.com)


• SQL SERVER - How to work with Row Constructors , inserted and deleted tables and Handle Result Set (sqlexplore.wordpress.com)


• jOOQ-meta. A "hard-core SQL" proof of concept (lukaseder.wordpress.com)


• BISQL # 34 :What is Identity IN SQL {Identity Part - I} (sqlmentalist.wordpress.com)


• SQL Tuning or SQL Optimization (premaseem.wordpress.com)


• Pivot Data in SQL and Display in a Flex Advanced Data Grid Part One (boozydoo.wordpress.com)


• SQL injection (nuzhatg.wordpress.com)