CODEWITHSUNDEEP
sqloracle-databasedatabase-partitioning
Vinod Yadav
Vinod Yadav

Reputation: 359

Extracting the actual date information of a partition based on date field in Oracle

I have a feed table which is using interval partitioning(Monthly partition) to hold 3 years of historic data with the partition key as a date field of the table. We need to drop partitions older than 3 years(36 months).

While dropping the partition I would like to display a message to the user telling the month and year of the partition to be dropped. To get the month info, I'll have to use the HIGH_VALUE column of USER_TAB_PARTITIONS table since the partition_name is system generated like 'SYSP###' giving no month information.

The value in HIGH_VALUE column is like: 

TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

I am unable to extract '2011-01-01' from above string using SUBSTR/REGEXP_SUBSTR.

I was using below query just to check if I can extract the required info:

select REGEXP_SUBSTR('TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')',10, 10 )  FROM DUAL;

The error I am getting is:
Error: ORA-01036: illegal variable name/number

I am not sure if I am doing some silly mistake here.

Would aqppreciate your kind assistance/any pointer/alternate way to achieve this.

Thanks in advance.

Regards,

Vinod

Upvotes: 2

Views: 1677

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156928

What you are doing doesn't make much sense to me.

You try to apply a regular expression on a date, instead of a varchar.

You could try this:

select to_char( TO_DATE( ' 2011-01-01 00:00:00'
                       , 'SYYYY-MM-DD HH24:MI:SS'
                       , 'NLS_CALENDAR=GREGORIAN'
                       )
              , 'yyyy-mm-dd'
              )
FROM   DUAL

If you want to extract the value from the statement try this:

select substr( 'TO_DATE('' 2011-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')',10, 11 )
FROM   DUAL

Note I changed the last 10 in your statement to 11 too.

Upvotes: 3

Related Questions