tricky SQL query (currently handled through PL/SQL)

I am working on Oracle 9.2 with the following table:

   CREATE TABLE my_table_v1
(

    my_name    NUMBER(9)   NOT NULL,
    my_date    DATE        NOT NULL,
    my_code    NUMBER(1)   NOT NULL

I would like to create a new table which (for each value of my_name) aggregates all of the records with an identical value of my_code that are "adjacent" time-wise into a single record with a start (min) and an end (max) time. The output table is described below.

   CREATE TABLE my_table_v2
(

    my_name    NUMBER(9)   NOT NULL,
    my_date1   DATE        NOT NULL,
    my_date2   DATE        NOT NULL,
    my_code    NUMBER(1)   NOT NULL

For example, consider this example data in my_table_v1:

   1   '01/01/03 00:00:00'   1
   1   '01/01/03 00:00:01'   1
   1   '01/01/03 00:00:04'   1
   1   '01/01/03 00:00:05'   3
   1   '01/01/03 00:00:09'   2
   1   '01/01/03 00:00:15'   2

This should become the following in my_table_v2:

   1   '01/01/03 00:00:00'   '01/01/03 00:00:04'   1
   1   '01/01/03 00:00:05'   '01/01/03 00:00:05'   3
   1   '01/01/03 00:00:09'   '01/01/03 00:00:15'   2

   SELECT my_name,
          my_date,
          my_code,
     FROM my_table_v1

Thanks for any help.

• jetlag --