This discussion is archived
6 Replies Latest reply: Feb 7, 2013 11:26 AM by Haider Hussain RSS

Different Behaviour of user defined data type

Haider Hussain Newbie
Currently Being Moderated
Hi,

I have created a user defined datatype as below :

create or replace
type person1 as object (
first_name varchar2(20),
last_name varchar2(20),
date_of_birth timestamp,
phone varchar2(20));
/

I have then created a table as :

create table test_table1 (serial_no number, personal_data person);



Then have inserted some data into it , as:

insert into test_table1 values (1,person('James','Horner',to_timestamp('24-MAY-2005 23:11:12','DD-MON-YYYY HH24:MI:SS'),'12345'));
insert into test_table1 values (2,person('Matt','Darey',to_timestamp('21-JUNE-2006 10:45:12','DD-MON-YYYY HH24:MI:SS'),'666'));
insert into test_table1 values (3,person('Tim','Hall',to_timestamp('26-JAN-2003 17:11:34','DD-MON-YYYY HH24:MI:SS'),'5555'));
insert into test_table1 values (4,person('Steven','Feuerstein',to_timestamp('21-MAY-2001 15:56:34','DD-MON-YYYY HH24:MI:SS'),'90000'));
insert into test_table1 values (5,person('Thomas','Kyte',to_timestamp('29-MAY-1998 11:11:11','DD-MON-YYYY HH24:MI:SS'),'1234567890'));
insert into test_table1 values (6,person('Benny','Benassi',to_timestamp('24-OCT-2008 22:22:22','DD-MON-YYYY HH24:MI:SS'),'7890'));
insert into test_table1 values (7,person('Eric','Prydz',to_timestamp('22-MAY-2008 23:32:23','DD-MON-YYYY HH24:MI:SS'),'26066'));

I am then accessing the concerned data using the below query :

select
a.serial_no "Serial #",
a.personal_data.phone "Telephone #",
a.personal_data.first_name "First Name",
a.personal_data.last_name "Last Name",
a.personal_data.date_of_birth "Date of Birth"
from
test_table1 a
where a.serial_no in (1,4,7);



The output is :


Serial # Telephone #
First Name
Last Name
Date of B
---------- ---------------------------------------------------------------------
------------------------------- ------------------------------------------------
---------------------------------------------------- ---------------------------
------------------------------------------------------------------------- ------
---
1 12345
James
Horner
24-MAY-05
4 90000
Steven
Feuerstein
21-MAY-01
7 26066
Eric
Prydz
22-MAY-08



Question : I fail to see the timestamp.

Also, I have a Oracle AQ payload having similar structure as the above mentioned object type. The payload attribute (having data type as timestamp) requires me to use the TREAT function. However, the attribute is accessible simply using the (.) notation.


Could you please help


Thanks,
Haider
  • 1. Re: Different Behaviour of user defined data type
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Please Use
     
    
    
    also read   https://wikis.oracle.com/display/Forums/Forums+FAQ                                                                                                                                                                            
  • 2. Re: Different Behaviour of user defined data type
    P.Forstmann Guru
    Currently Being Moderated
    You just need to define TIMESTAMP format to be displayed (here I'm using SQL*Plus):
    SQL> create or replace
      2  type person as object (
      3  first_name varchar2(20),
      4  last_name varchar2(20),
      5  date_of_birth timestamp,
      6  phone varchar2(20));
      7  /
    
    Type created.
    
    SQL>
    SQL> create table test_table1 (serial_no number, personal_data person);
    
    Table created.
    
    SQL> insert into test_table1 values (1,person('James','Horner',to_timestamp('24-MAY-2005 23:11:12','DD-MON-YYYY HH24:MI:SS'),'12345'
    ));
    
    1 row created.
    
    SQL> insert into test_table1 values (2,person('Matt','Darey',to_timestamp('21-JUNE-2006 10:45:12','DD-MON-YYYY HH24:MI:SS'),'666'));
    
    
    1 row created.
    
    SQL> insert into test_table1 values (3,person('Tim','Hall',to_timestamp('26-JAN-2003 17:11:34','DD-MON-YYYY HH24:MI:SS'),'5555'));
    
    1 row created.
    
    SQL> insert into test_table1 values (4,person('Steven','Feuerstein',to_timestamp('21-MAY-2001 15:56:34','DD-MON-YYYY HH24:MI:SS'),'9
    0000'));
    
    1 row created.
    
    SQL> insert into test_table1 values (5,person('Thomas','Kyte',to_timestamp('29-MAY-1998 11:11:11','DD-MON-YYYY HH24:MI:SS'),'1234567
    890'));
    
    1 row created.
    
    SQL> insert into test_table1 values (6,person('Benny','Benassi',to_timestamp('24-OCT-2008 22:22:22','DD-MON-YYYY HH24:MI:SS'),'7890'
    ));
    
    1 row created.
    
    SQL> insert into test_table1 values (7,person('Eric','Prydz',to_timestamp('22-MAY-2008 23:32:23','DD-MON-YYYY HH24:MI:SS'),'26066'))
    ;
    
    1 row created.
    
    SQL>
    SQL> alter session set nls_timestamp_format='DD-MON-YYYY HH24:MI:SS';
    
    Session altered.
    
    SQL> column "Date of Birth" format a20
    SQL>
    SQL> select
      2  a.serial_no "Serial #",
      3  a.personal_data.last_name "Last Name",
      4  a.personal_data.date_of_birth "Date of Birth"
      5  from
      6  test_table1 a
      7  where a.serial_no in (1,4,7);
    
      Serial # Last Name            Date of Birth
    ---------- -------------------- --------------------
             1 Horner               24-MAY-2005 23:11:12
             4 Feuerstein           21-MAY-2001 15:56:34
             7 Prydz                22-MAY-2008 23:32:23
    
    SQL>
    Edited by: P. Forstmann on 7 févr. 2013 20:10
  • 3. Re: Different Behaviour of user defined data type
    Haider Hussain Newbie
    Currently Being Moderated
    Hi,

    Thanks for the response. Could you please help me on the issue related to AQ payload having a similar structure as the mentioned object type

    Thanks,
    Haider
  • 4. Re: Different Behaviour of user defined data type
    P.Forstmann Guru
    Currently Being Moderated
    Sorry but I don't have Advanced Queueing programming experience. I recommend that you post a detailed problem description in AQ dedicated forum Advanced Queueing
  • 5. Re: Different Behaviour of user defined data type
    Justin Cave Oracle ACE
    Currently Being Moderated
    I'm not sure that I understand the AQ payload problem. Any chance you could post a code snippet and show us what you are seeing (or not seeing) that you don't expect (or do expect) to see?

    Justin
  • 6. Re: Different Behaviour of user defined data type
    Haider Hussain Newbie
    Currently Being Moderated
    My queue payload type has a component having timestamp datatype.
    Eg : my_queue_payload(attr1 varchar2(100),
    attr_timestamp timestamp);

    I have successfully managed to query the component attr1 using the below query on the queue table(my_queue_tab)

    select a.user_data.attr1 from my_queue_tab a where a.msgid = 'AAABBBCCC1234';

    However, I am unable to query the component attr_timestamp using the above query due to Oracle error 'invalid identifier'.

    Thanks,
    Haider

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points