Partition by Range on a Char datatype
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Partition by Range on a Char datatype

  1. #1
    Join Date
    Feb 2004
    Posts
    35

    Question Partition by Range on a Char datatype

    Hello,
    Is it possible to create a table and partition it (by range) on a column that is a “date value” but the datatype is character (character string).
    Due to vendor restrictions, we are using character datatype to store date and I am running into ORA-00907 when I try to create a partition table by range on that column.

    Thanks.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yes you can, but how meaningful it is will depend on how the date is formatted -- if it's formatted YYYY-MM-DD for example you'd be ok, but if it was DD-MON-YYYY then you'd probably be wasting your time.

    What a dumb vendor you have.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2004
    Posts
    35
    What i am doing wrong with following script:

    CREATE TABLE MYTABLE_PARTN ("COL1" CHAR(31), "COL2" CHAR(31), "COL3" CHAR(31))
    partition by range (to_date(COL3,'dd-mon-yyyy'))
    (
    partition p1 values less than (to_date('28-jul-2004','dd-mon-yyyy')) tablespace my_data,
    partition p2 values less than (to_date('29-jul-2004','dd-mon-yyyy')) tablespace my_data,
    );

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    You're telling oracle you have a table with a char datatype and you want to range partition on dates. USe something like:
    Code:
    CREATE TABLE MYTABLE_PARTN ("COL1" CHAR(31), "COL2" CHAR(31), "COL3" CHAR(31))
    partition by range (COL3)
    (
    partition p1 values less than ('2004-07-28') tablespace my_data,
    partition p2 values less than ('2004-07-29') tablespace my_data,
    );
    The date format will drive what you use as values for the partitioning clause. This is why slimdave said this design needs some work...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width