This discussion is archived
5 Replies Latest reply: Oct 28, 2008 11:08 AM by 594273 RSS

ora-01830

575295 Newbie
Currently Being Moderated
I'm trying to run sql to grab just the weekend partitions

We have table partitions named as such

PAR20080302
PAR20080303
...

When I run this

select * from (
select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
from dba_tab_partitions
where table_name like 'PAR')

I get output like this

Partition_name pardate
--------------- -------
PAR20080325 TUE
PAR20080326 WED

Now when I try to grab just the partitions from Saturday and Sunday by running this

select * from (
select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
from dba_tab_partitions
where table_name like 'PAR')
where pardate in ('SAT','SUN')

it bombs with ORA-01830

How can I limit the results to just the weekends?

Thanks
  • 1. Re: ora-01830
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi,

    Could it be that have table partions which name is longer than 11 characters?

    This part of your query works fine:
    select to_char(to_date(substr('PAR20080302',4),'YYYYMMDD'),'DY') pardate from dual;
    Unless you encounter a partion with a longer name:
    select to_char(to_date(substr('PAR20080302_more',4),'YYYYMMDD'),'DY') pardate from dual;
    ORA-01830: date format picture ends before converting entire input string
    Depending of the nature of the other partion names you could change your arguments to substr:
    select to_char(to_date(substr('PAR20080302_more',4,8),'YYYYMMDD'),'DY') pardate from dual;
    Regards
    Peter
  • 2. Re: ora-01830
    Anurag Tibrewal Guru
    Currently Being Moderated
    Hi,

    The behaviour you have mentioned in something strange. So I was just interested in it. Do let me know if you have solved the issue. If yes then how and if no then could you please post the exact output and query.

    I have also noticed that in where clause you have used table_name like 'PAR', but it should be something like table_name like 'PAR%'. You might have mistyped this while posting the query and would have used it correctly in your implementation. Correct?

    Regards
  • 3. Re: ora-01830
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    this is a very common mistake
    select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
    from dba_tab_partitions
    where table_name like 'PAR%'
    the function to_date is possibly executed for every partition, the where condition is evaluated after the to_date.

    you probably want
    select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
    from (
      select rownum, partition_name
      from dba_tab_partitions
      where table_name like 'PAR%'
    )
    rownum should prevent view merging.

    HTH
  • 4. Re: ora-01830
    575295 Newbie
    Currently Being Moderated
    Thanks for the replies. When I get a chance I'll test them out and update
  • 5. Re: ora-01830
    594273 Newbie
    Currently Being Moderated
    I'm trying to run sql to grab just the weekend partitions on table AR_ATB_DATA

    We have table partitions named as such

    AR_ATB_DATA_2006
    AR_ATB_DATA_2007
    AR_ATB_DATA_2008
    AR_ATB_DATA_2009
    ...

    When I run this below query, I am getting the following error:


    select to_char(to_Date(substr(partition_name,6),'YYYY-MM-DD'),'DD-MON-YYYY') from (select partition_name from dba_tab_partitions where table_name='AR_ATB_DATA' and table_owner='ADBA' order by 1 desc) where rownum < 2
    *
    ERROR at line 1:
    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    Can you please help me how to avoid this error