Hi All,
Recently i came cross a use case where i need to add a new column into a table which being already used in production and we need to support this new changes on both version of table schema.
However gradually we will get ride of old version of table. but for now we need to support both schema.
In my case it's only related to fetch operation ,so here i am going to explain how i achieved fetch functionality on both schema.
I have create a sample table name as BlogTable which has following column in my version 1(demo purpose)
1-id
2-Name
3-Address
Now my boss(they always demand :) ) came to me and asking in next version 2 we have to add one more column name as
4-city.
And he warned me that this should work in both data base schema. Since our application is already in production therefore the old table based VO should not give any error while fetching data.
I need to change VO the way which support both table version at a time.But obviously if i put column name directly and if it's try to execute on schema which does not have new column then this going to throw exception at run time.
I solve this problem by creating a function inside the package but the most importance part is here the function name should be same as newly added column name. so that when sql engine try to execute the query first it will look for column and if column is not present in table then it think it is function and it will execute dummy function which return null record.
And also in view query i am using package name as table alias name.
function code is following
CREATE OR REPLACE
PACKAGE BODY BLOGPACKAGE AS
function city return varchar2 IS
Begin
return 'dummy';
End city;
END BLOGPACKAGE;
And VO query is
SELECT
blogpackage.ID ID,
blogpackage.NAME NAME,
blogpackage.ADDRESS ADDRESS,
blogpackage.city city
FROM
BLOGTABLE blogpackage
And if you observer that the table alias name is same as package name.so when i try to get the column which is not present in this schema it will call function of package .
Here two point to remember
1-function name should be same as column name
2-table alias should same as package name.
Following screenshot is with real column table.
Following screenshot of application without city column.
Sample application attached with sample sql query inside resource folder.
link is : https://drive.google.com/file/d/0B8cP4jZuxLlXay1BNmNiOE5RaGM/view?usp=sharing
Thank
Recently i came cross a use case where i need to add a new column into a table which being already used in production and we need to support this new changes on both version of table schema.
However gradually we will get ride of old version of table. but for now we need to support both schema.
In my case it's only related to fetch operation ,so here i am going to explain how i achieved fetch functionality on both schema.
I have create a sample table name as BlogTable which has following column in my version 1(demo purpose)
1-id
2-Name
3-Address
Now my boss(they always demand :) ) came to me and asking in next version 2 we have to add one more column name as
4-city.
And he warned me that this should work in both data base schema. Since our application is already in production therefore the old table based VO should not give any error while fetching data.
I need to change VO the way which support both table version at a time.But obviously if i put column name directly and if it's try to execute on schema which does not have new column then this going to throw exception at run time.
I solve this problem by creating a function inside the package but the most importance part is here the function name should be same as newly added column name. so that when sql engine try to execute the query first it will look for column and if column is not present in table then it think it is function and it will execute dummy function which return null record.
And also in view query i am using package name as table alias name.
function code is following
CREATE OR REPLACE
PACKAGE BODY BLOGPACKAGE AS
function city return varchar2 IS
Begin
return 'dummy';
End city;
END BLOGPACKAGE;
And VO query is
SELECT
blogpackage.ID ID,
blogpackage.NAME NAME,
blogpackage.ADDRESS ADDRESS,
blogpackage.city city
FROM
BLOGTABLE blogpackage
And if you observer that the table alias name is same as package name.so when i try to get the column which is not present in this schema it will call function of package .
Here two point to remember
1-function name should be same as column name
2-table alias should same as package name.
Following screenshot is with real column table.
Following screenshot of application without city column.
Sample application attached with sample sql query inside resource folder.
link is : https://drive.google.com/file/d/0B8cP4jZuxLlXay1BNmNiOE5RaGM/view?usp=sharing
Thank