Home » SQL & PL/SQL » SQL & PL/SQL » Reading the Highvalue of partition and performing dynamic operation (Oracle 11g)
Reading the Highvalue of partition and performing dynamic operation [message #636480] Sat, 25 April 2015 16:58 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I had a case to drop partition from a interval partition table monthly basis dynamically, i have written the below code but the high value is coming as blank.


I had written below code to get the value

CREATE OR REPLACE FUNCTION GFL_COMMON_DATA.GET_HIGH_VALUE_AS_DATE (
    p_TableName     IN VARCHAR2,
    p_PatitionName  IN VARCHAR2
) RETURN DATE
IS
   v_LongVal LONG;
BEGIN
    SELECT HIGH_VALUE INTO v_LongVal
      FROM ALL_TAB_PARTITIONS
     WHERE TABLE_NAME = p_TableName
       AND PARTITION_NAME = p_PatitionName;

    RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/


When i run the below query i am getting blank value

select GET_HIGH_VALUE_AS_DATE('TESTA', 'SYS_P4410') a from dual;  

a
-----

But when i checked from all_tab_partition table i can see the date parameter in the high value


Re: Reading the Highvalue of partition and performing dynamic operation [message #636482 is a reply to message #636480] Sat, 25 April 2015 17:12 Go to previous message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Sorry.. got the issue i was querying in another schema and the grant select was missing. Fixed it now.
Thanks
Smile
Previous Topic: inner query use outer query
Next Topic: SQL Query
Goto Forum:
  


Current Time: Thu Mar 28 04:54:49 CDT 2024