Re: Alter time datatype of a primary key column

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 31 Aug 2012 09:02:33 -0700 (PDT)
Message-ID: <dc0d6ec3-7141-4fe1-beb0-01380ce5b7c1_at_googlegroups.com>



On Thursday, August 30, 2012 11:22:29 AM UTC-4, ddf wrote:
> On Thursday, August 30, 2012 12:58:17 AM UTC-6, McX wrote: > Hi all, > > I've deeply searched something on this aspect of changing datatype ,but > > I'm still without any answer. > > Well , I've a table with a primary key DATE column. Now, I've changed > > DATA datatype in TIMESTAMP(3) without any problem. I've checked index on > > DBA_TABLES and is still VALID. I've inserted new line, upgraded old ones > > , selected them in every sauce. Is it really so easy and so safety to do > > ? Yes , I know : a datatype change means that an operation between DATE > > gives back a NUMBER type , between a TIMESTAMP(3) gives back an > > INTERVAL. Except this aspect, am I forgetting something ? > > > > Thanks Possibly. Any function written against the original definition and not using dynamic data typing (%TYPE) will return a DATE value rather than a TIMESTAMP: SQL> create table yarpnoo( 2 smertzle number, 3 cahoolie varchar2(23), 4 humgreeper date 5 ); Table created. Elapsed: 00:00:00.00 SQL> SQL> alter table yarpnoo 2 add constraint yarpnoo_pk primary key(humgreeper); Table altered. Elapsed: 00:00:00.01 SQL> SQL> create or replace function get_humgreeper(p_smertz in number) 2 return date 3 is 4 l_ret date; 5 begin 6 select humgreeper into l_ret 7 from yarpnoo 8 where smertzle = p_smertz; 9 10 return l_ret; 11 12 end; 13 / Function created. Elapsed: 00:00:00.01 SQL> SQL> select constraint_name, search_condition 2 from user_constraints 3 where constraint_name = 'YARPNOO_PK'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ ----------------------------------------------- YARPNOO_PK Elapsed: 00:00:00.22 SQL> SQL> select index_name, status 2 from user_indexes; INDEX_NAME STATUS ------------------------------ -------- YARPNOO_PK VALID Elapsed: 00:00:00.67 SQL> SQL> begin 2 for i in 1..1000 loop 3 insert into yarpnoo 4 values(i, 'Quag'||i, sysdate+i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> SQL> select humgreeper from yarpnoo where rownum <=11; HUMGREEPE --------- 31-AUG-12 01-SEP-12 02-SEP-12 03-SEP-12 04-SEP-12 05-SEP-12 06-SEP-12 07-SEP-12 08-SEP-12 09-SEP-12 10-SEP-12 11 rows selected. Elapsed: 00:00:00.01 SQL> select get_humgreeper(17) from dual; GET_HUMGR --------- 16-SEP-12 Elapsed: 00:00:00.01 SQL> SQL> alter table yarpnoo modify humgreeper timestamp; Table altered. Elapsed: 00:00:00.00 SQL> SQL> select constraint_name, search_condition 2 from user_constraints 3 where constraint_name = 'YARPNOO_PK'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ ----------------------------------------------- YARPNOO_PK Elapsed: 00:00:00.01 SQL> SQL> select index_name, status 2 from user_indexes; INDEX_NAME STATUS ------------------------------ -------- YARPNOO_PK VALID Elapsed: 00:00:00.86 SQL> SQL> select humgreeper from yarpnoo where rownum <=11; HUMGREEPER --------------------------------------------------------------------------- 31-AUG-12 09.17.46.000000 AM 01-SEP-12 09.17.46.000000 AM 02-SEP-12 09.17.46.000000 AM 03-SEP-12 09.17.46.000000 AM 04-SEP-12 09.17.46.000000 AM 05-SEP-12 09.17.46.000000 AM 06-SEP-12 09.17.46.000000 AM 07-SEP-12 09.17.46.000000 AM 08-SEP-12 09.17.46.000000 AM 09-SEP-12 09.17.46.000000 AM 10-SEP-12 09.17.46.000000 AM 11 rows selected. Elapsed: 00:00:00.01 SQL> select get_humgreeper(17) from dual; GET_HUMGR --------- 16-SEP-12 Elapsed: 00:00:00.03 SQL> Notice that even though the column definition changed the function still returns a DATE value as that is the defined return type. Using dynamic data typing (the %TYPE syntax) in the function allows it to return the 'correct' data type even after the column definition has changed: SQL> drop table yarpnoo purge; Table dropped. Elapsed: 00:00:00.03 SQL> SQL> create table yarpnoo( 2 smertzle number, 3 cahoolie varchar2(23), 4 humgreeper date 5 ); Table created. Elapsed: 00:00:00.00 SQL> SQL> alter table yarpnoo 2 add constraint yarpnoo_pk primary key(humgreeper); Table altered. Elapsed: 00:00:00.00 SQL> SQL> create or replace function get_humgreeper(p_smertz in number) 2 return yarpnoo.humgreeper%type 3 is 4 l_ret yarpnoo.humgreeper%type; 5 begin 6 select humgreeper into l_ret 7 from yarpnoo 8 where smertzle = p_smertz; 9 10 return l_ret; 11 12 end; 13 / Function created. Elapsed: 00:00:00.03 SQL> SQL> select constraint_name, search_condition 2 from user_constraints 3 where constraint_name = 'YARPNOO_PK'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ ----------------------------------------------- YARPNOO_PK Elapsed: 00:00:00.00 SQL> SQL> select index_name, status 2 from user_indexes; INDEX_NAME STATUS ------------------------------ -------- YARPNOO_PK VALID Elapsed: 00:00:00.73 SQL> SQL> begin 2 for i in 1..1000 loop 3 insert into yarpnoo 4 values(i, 'Quag'||i, sysdate+i); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> SQL> select humgreeper from yarpnoo where rownum <=11; HUMGREEPE --------- 31-AUG-12 01-SEP-12 02-SEP-12 03-SEP-12 04-SEP-12 05-SEP-12 06-SEP-12 07-SEP-12 08-SEP-12 09-SEP-12 10-SEP-12 11 rows selected. Elapsed: 00:00:00.00 SQL> select get_humgreeper(17) from dual; GET_HUMGR --------- 16-SEP-12 Elapsed: 00:00:00.00 SQL> SQL> alter table yarpnoo modify humgreeper timestamp; Table altered. Elapsed: 00:00:00.01 SQL> SQL> select constraint_name, search_condition 2 from user_constraints 3 where constraint_name = 'YARPNOO_PK'; CONSTRAINT_NAME SEARCH_CONDITION ------------------------------ ----------------------------------------------- YARPNOO_PK Elapsed: 00:00:00.00 SQL> SQL> select index_name, status 2 from user_indexes; INDEX_NAME STATUS ------------------------------ -------- YARPNOO_PK VALID Elapsed: 00:00:00.75 SQL> SQL> select humgreeper from yarpnoo where rownum <=11; HUMGREEPER --------------------------------------------------------------------------- 31-AUG-12 09.17.48.000000 AM 01-SEP-12 09.17.48.000000 AM 02-SEP-12 09.17.48.000000 AM 03-SEP-12 09.17.48.000000 AM 04-SEP-12 09.17.48.000000 AM 05-SEP-12 09.17.48.000000 AM 06-SEP-12 09.17.48.000000 AM 07-SEP-12 09.17.48.000000 AM 08-SEP-12 09.17.48.000000 AM 09-SEP-12 09.17.48.000000 AM 10-SEP-12 09.17.48.000000 AM 11 rows selected. Elapsed: 00:00:00.01 SQL> select get_humgreeper(17) from dual; GET_HUMGREEPER(17) --------------------------------------------------------------------------- 16-SEP-12 09.17.48.000000000 AM Elapsed: 00:00:00.03 SQL> This, of course, relies on implicit data type conversion and could break in subsequent releases of Oracle. David Fitzjarrell

David, very complete example. -- Mark -- Received on Fri Aug 31 2012 - 11:02:33 CDT

Original text of this message