If I Ask You What Enhancements Should Be Done In Oracle Create Table ?
Yes, I am asking to you, if you are Oracle Database Application developer and design many applications, then obviously you must have created many tables and while creating a table what inconvenience you feel and what improvements you want to make it more sensible.
I am going to share with you just some of my ideas I got while creating tables and while using them in queries and if they will be implement in future then it will definitely help us.
I will describe all new three ideas below and every idea would be in three parts first what enhancement is required and second what will be the benefit and the third what keyword can be add to accomplish that task.
First One: Specify Column Alias At The Time Of Create Table
What I want is to specify ALIAS to a column at the time of creating a table, something like this:
Create Table Employee
ECODE NUMBER(5) PRIMARY KEY ALIAS “Employee Code”,
ENAME VARCHAR2(50) NOT NULL ALIAS “Employee Name”,
SAL NUMBER NOT NULL ALIAS “Salary”,
COMM NUMBER ALIAS “INCENTIVE”
Benefits of the aliases specification at the time of creating a table is whenever you will query to see the data or query to build the reports in SQLPLUS or in any Report Builder Tool then you will see the column names more meaning full and always the same, like for example you will not found the column COMM in one report as Commission and in another report as Incentive, they will be in one standard.
But don’t worry I am not telling to forget the original names of the column, I know that the original name is very important because you will make the programs by those names, so I have an idea to query the records with column aliases with a new keyword if I am not specifying that keyword then the Oracle will show the original column name but if I am specifying that keyword then only I will get the result with aliases.
And the keyword could be something like this:
Column Names As Alias
Yes the whole command could be:
Select * From Emp Column Names As Alias
Second One: Varchar2 Length Specifications Should Not Be Necessary
It is very difficult sometimes for some specific fields to specify the length of a Varchar2 Data Type field at the time of table creation, because we don’t know the exactly length what could be in future, for example any Address field or any Description field which could in a reasonable size but not specific, so for that kind of fields, there is must be facility to specify Varchar2 type without length like for Number data type fields we specify sometimes.
… Address_1 Varchar2, …
Benefits would be to prevent many issues can occur while importing data from external sources, suppose you have specified an address field size to 60 bytes and data is coming in 65 bytes.
Third One: History Table Maintenance By Database Engine
Yes this feature should be maintain by database engine for the tables we want to maintain the history for any purposes, then for this feature there would be no need to create trigger which can be manually disabled.
Keywords could be in Create Table command:
Benefits would be more reliable and secure history table data.
For now that is all from my side, so what is your opinion? How about the ideas? What do you think? You can share your ideas and views by commenting below, thanks.