6 Replies Latest reply: Feb 7, 2013 1:26 PM by Haider Hussain RSS

    Different Behaviour of user defined data type

    Haider Hussain
      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
          Please Use
           
          
          
          also read   https://wikis.oracle.com/display/Forums/Forums+FAQ                                                                                                                                                                            
          • 2. Re: Different Behaviour of user defined data type
            P.Forstmann
            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
              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
                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
                  JustinCave
                  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
                    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