I have nested table like below which is already loaded.
create or replace TYPE access_t AS OBJECT (
AccessID VARCHAR2(50),
Eligibility char(1)
);
/
create or replace TYPE Access_tab IS TABLE OF access_t;
/
create or replace TYPE add_t AS OBJECT (
city VARCHAR2(100),
state VARCHAR2(100),
zip VARCHAR2(10),
APOINTSARRAY Access_tab )
;
/
create or replace TYPE add_tab IS TABLE OF add_t;
/
CREATE TABLE RQST_STATUS
( RQST_ID NUMBER,
ADDRESS add_tab
)
NESTED TABLE ADDRESS STORE AS RQST_STATUS_ADDRESS
( NESTED TABLE APOINTSARRAY STORE AS RQST_STATUS_AP)
;
Now without changing TYPE add_t (since its used in other tables which do not need this change) , i need to add 2 additional columns to ADDRESS.
I took backup of the table , altered the table to add another new field.
create or replace TYPE add_t_new AS OBJECT (
city VARCHAR2(100),
state VARCHAR2(100),
zip VARCHAR2(10),
APOINTSARRAY Access_tab,
Latitude VARCHAR2(10),
Longitude VARCHAR2(10),
)
;
/
create or replace TYPE add_tab_new IS TABLE OF add_t_new;
/
CREATE TABLE RQST_STATUS
( RQST_ID NUMBER,
ADDRESS add_tab
)
NESTED TABLE ADDRESS STORE AS RQST_STATUS_ADDRESS
( NESTED TABLE APOINTSARRAY STORE AS RQST_STATUS_AP)
;
ALTER TABLE RQST_STATUS ADD
(
ADDRESS_NEW add_tab_new
)
NESTED TABLE ADDRESS_NEW STORE AS ADDRESS_NEW_V1
(NESTED TABLE APOINTSARRAY STORE AS APOINTSARRAY_V1) ;
So now , i have old and new fields in the same table.
How can i now insert data from old field to new field ? with null values for the 2 additional fields.
Something like this ..
UPDATE RQST_STATUS set
ADDRESS_NEW = cast(add_t_new(add_t(ADDRESS),'', '') as add_tab_new);
But i have not been able to figure out how to create the update DML.
Please help.
I intend to drop the old column and then rename the new column to old after.
Thanks.
Ick. Nested tables. What's wrong with standard relational tables?
Anyway, the address column is a table of objects. So you need to convert each element of the array to the new type. So first extract them using the table() operator. Then construct an add_t_new object for each row in the results:
insert into RQST_STATUS values
(1, add_tab(add_t('city', 'state', 'zip', Access_tab(access_t('access', 'Y')))));
insert into RQST_STATUS values
(2, add_tab(
add_t('city', 'state', 'zip', Access_tab(access_t('access', 'Y'))),
add_t('city2', 'state2', 'zip2', Access_tab(access_t('access2', 'Y'))))
);
select rqst_id, add_t_new(city, state, zip, apointsarray, null, null) addr_new
from RQST_STATUS s, table(address) a;
RQST_ID ADDR_NEW(CITY, STATE, ZIP, APOINTSARRAY(ACCESSID, ELIGIBILITY), LATITUDE, LONGITUDE)
---------- ---------------------------------------------------------------------------------------
1 ADD_T_NEW('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL)
2 ADD_T_NEW('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL)
2 ADD_T_NEW('city2', 'state2', 'zip2', ACCESS_TAB(ACCESS_T('access2', 'Y')), NULL, NULL)
You then need to group these back up into add_tab_new arrays for each rqst_id. So to assign the values to the new column:
- cast(collect(..) as add_new_tab) the values from the address table
- Use a subquery to update the values:
ALTER TABLE RQST_STATUS ADD
(
ADDRESS_NEW add_tab_new
)
NESTED TABLE ADDRESS_NEW STORE AS ADDRESS_NEW_V1
(NESTED TABLE APOINTSARRAY STORE AS APOINTSARRAY_V1);
update RQST_STATUS r
set address_new = (
select cast(collect(add_t_new(city, state, zip, apointsarray, null, null)) as add_tab_new)
from RQST_STATUS s, table(address) a
where r.rqst_id = s.rqst_id
);
select rqst_id, address_new from RQST_STATUS;
RQST_ID
----------
ADDRESS_NEW(CITY, STATE, ZIP, APOINTSARRAY(ACCESSID, ELIGIBILITY), LATITUDE, LONGITUDE)
------------------------------------------------------------------------------------------------
1
ADD_TAB_NEW(ADD_T_NEW('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL))
2
ADD_TAB_NEW(ADD_T_NEW('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL),
ADD_T_NEW('city2', 'state2', 'zip2', ACCESS_TAB(ACCESS_T('access2', 'Y')), NULL, NULL))
Though I have to ask. Why are you creating a new type? If you're using objects, why not take advantage of this and create a subtype with the extra attributes? e.g.:
create or replace TYPE add_geo_t under add_t (
Latitude VARCHAR2(10),
Longitude VARCHAR2(10)
);
/
If you're feeling really adventurous, you could add a new default constructor which takes an add_t type:
create or replace TYPE add_geo_t under add_t (
Latitude VARCHAR2(10),
Longitude VARCHAR2(10),
constructor function add_geo_t(self in out nocopy add_geo_t,
city VARCHAR2, state VARCHAR2, zip VARCHAR2,
APOINTSARRAY Access_tab, Latitude varchar2, Longitude VARCHAR2)
return self as result,
constructor function add_geo_t(self in out nocopy add_geo_t,
addr add_t)
return self as result
)
;
/
create or replace TYPE body add_geo_t is
constructor function add_geo_t(self in out nocopy add_geo_t,
city VARCHAR2, state VARCHAR2, zip VARCHAR2,
APOINTSARRAY Access_tab, Latitude varchar2, Longitude VARCHAR2)
return self as result is
begin
self.city := city;
self.state := state;
self.zip := zip;
self.APOINTSARRAY := APOINTSARRAY;
self.Latitude := Latitude;
self.Longitude := Longitude;
dbms_output.put_line('full constructor');
return;
end;
constructor function add_geo_t(self in out nocopy add_geo_t,
addr add_t)
return self as result is
begin
self.city := addr.city;
self.state := addr.state;
self.zip := addr.zip;
self.APOINTSARRAY := addr.APOINTSARRAY;
dbms_output.put_line('address constructor');
return;
end;
end;
/
create or replace TYPE add_geo_tab IS TABLE OF add_geo_t;
/
This makes it a bit easier to write your update:
ALTER TABLE RQST_STATUS ADD
(
ADDRESS_GEO add_geo_tab
)
NESTED TABLE ADDRESS_GEO STORE AS ADDRESS_GEO
(NESTED TABLE APOINTSARRAY STORE AS APOINTSARRAY_V2);
update RQST_STATUS r
set address_geo = (
select cast(collect(add_geo_t(value(a))) as add_geo_tab)
from RQST_STATUS s, table(address) a
where r.rqst_id = s.rqst_id
);
select rqst_id, address_geo from RQST_STATUS;
RQST_ID
----------
ADDRESS_GEO(CITY, STATE, ZIP, APOINTSARRAY(ACCESSID, ELIGIBILITY), LATITUDE, LONGITUDE)
------------------------------------------------------------------------------------------------
1
ADD_GEO_TAB(ADD_GEO_T('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL))
2
ADD_GEO_TAB(ADD_GEO_T('city', 'state', 'zip', ACCESS_TAB(ACCESS_T('access', 'Y')), NULL, NULL),
ADD_GEO_T('city2', 'state2', 'zip2', ACCESS_TAB(ACCESS_T('access2', 'Y')), NULL, NULL))
But I'd still seriously consider ditching the types and switching to plain old tables...