OraFAQ Forum: SQL & PL/SQL » Partition table - High

Home » SQL & PL/SQL » SQL & PL/SQL » Partition table - High_Value in DD-MON-YYYY format (Oracle 11.2.0.3, Linux x86 64)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition table - High_Value in DD-MON-YYYY format [message #639508]

Thu, 09 July 2015 00:49

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi All,

I have to create procedure which will accept DATE and TABLE_NAME as Input and provide respective PARTITION_NAME, so I used following SQL script -

select table_name,
       partition_name,
       to_date (
          trim (
             '''' from regexp_substr (
                          extractvalue (
                             dbms_xmlgen.
                             getxmltype (
                                'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and table_owner = '''
                                || table_owner
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  from all_tab_partitions;

The above script works fine and gives output as "DD-MON-YYYY", if the HIGH_VALUE data as follows -

TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'

But if the HIGH_VALUE data as follows, then it gives output as BLANK -

20101001

Can anyone please help how to modify script so it will give the output for all the format as - DD-MON-YYYY

Many Thanks in advance.

Hitesh

Report message to a moderator

Re: Partition table - High_Value in DD-MON-YYYY format [message #639515 is a reply to message #639508]

Thu, 09 July 2015 01:49

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just remove trim and regexp_substr and change the date format.

Report message to a moderator

Re: Partition table - High_Value in DD-MON-YYYY format [message #639524 is a reply to message #639515]

Thu, 09 July 2015 03:46

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Thanks for reply, but I need the script so it will work for both, so can I use UNION in the script?

Report message to a moderator

Re: Partition table - High_Value in DD-MON-YYYY format [message #639536 is a reply to message #639524]

Thu, 09 July 2015 05:17

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can.
I advise you to use a factoring WITH clause.

Show us what you try or achieve.

Report message to a moderator

Previous Topic:	How to use computed result of one row in the next row for inserting in table
Next Topic:	Get data from various partitions with having one extra column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 07:25:26 CDT 2024