Re: Alter time datatype of a primary key column

From: ddf <oratune_at_msn.com>
Date: Thu, 30 Aug 2012 08:22:28 -0700 (PDT)
Message-ID: <4dcb1b9f-2183-4aca-b932-bd9d7f5caa14_at_googlegroups.com>

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 Received on Thu Aug 30 2012 - 10:22:28 CDT

This message: [ Message body ]
Next message: McX: "Re: Alter time datatype of a primary key column"
Previous message: McX: "Alter time datatype of a primary key column"
In reply to: McX: "Alter time datatype of a primary key column"
Next in thread: McX: "Re: Alter time datatype of a primary key column"
Reply: McX: "Re: Alter time datatype of a primary key column"
Reply: Mark D Powell: "Re: Alter time datatype of a primary key column"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message