Home -> Mailing Lists -> Oracle-L -> Aw: Re: DBA_TAB_PARTITIONS

Aw: Re: DBA_TAB_PARTITIONS

From: <rogel_at_web.de>
Date: Wed, 17 Jan 2018 19:18:12 +0100
Message-ID: <trinity-6eac93f8-2bf1-460c-8145-3edde5cf9ccd-1516213092477_at_3c-app-webde-bs39> 







Hi Adric, hi Liz,



 



I use a similar function, however, no need for execute immediate here and also not for a custom date expression parser, just use the built-in to_date after splitting v_high_value_expr into to_date's arguments and 



 



return





   to_date(

     substr(h.v_high_value_expr, instr(h.v_high_value_expr, '''', 1, 1) + 1, instr(h.v_high_value_expr, '''', 1, 2) - instr(h.v_high_value_expr, '''', 1, 1) - 1),

     substr(h.v_high_value_expr, instr(h.v_high_value_expr, '''', 1, 3) + 1, instr(h.v_high_value_expr, '''', 1, 4) - instr(h.v_high_value_expr, '''', 1, 3) - 1),

     substr(h.v_high_value_expr, instr(h.v_high_value_expr, '''', 1, 5) + 1, instr(h.v_high_value_expr, '''', 1, 6) - instr(h.v_high_value_expr, '''', 1, 5) - 1)

   )



 



Regards



Matthias





 



Gesendet: Mittwoch, 17. Januar 2018 um 18:27 Uhr

Von: "Adric Norris" <landstander668_at_gmail.com>

An: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>

Betreff: Re: DBA_TAB_PARTITIONS







Here's an example version of a function I've used for this purpose in the past. Please note that it's generally a bad idea to use execute immediate to evaluate an expression pulled from a DB column in this manner. It should be safe in this specific case, however, because we're being careful to lock the source to a known good source in the SYS schema (so it can't inadvertently reference an item of the same name in the current schema).

 

declare

   v_date      date;



   function GET_HIGH_VALUE_AS_DATE ( p_TableOwner IN varchar2 := user,

                                     p_TableName IN VARCHAR2,

                                     p_PartitionName IN VARCHAR2

                                   )

      RETURN DATE

   IS

      v_high_value_expr    varchar2(200);

      v_result             date;



   BEGIN

      select p.high_value

         into v_high_value_expr

         from sys.dba_tab_partitions p

         where p.table_owner = p_TableOwner

           and p.table_name  = p_TableName

           and p.partition_name = p_PartitionName;



      execute immediate 'begin :b1 := ' || v_high_value_expr || '; end;'

         using OUT v_result;

      

      return v_result;

   END GET_HIGH_VALUE_AS_DATE;

   

begin

   v_date := get_high_value_as_date( upper('&owner'), upper('&table'), upper('&partition') );

   dbms_output.put_line( to_char( v_date, 'YYYY-MM-DD HH24:MI:SS' ) );

end;

/



From what I've seen, the only way to collapse the expression to a date (short of writing a custom date expression parser) is to actually execute the beastie.



 

On Tue, Jan 16, 2018 at 1:46 PM, Reen, Elizabeth <dmarc-noreply_at_freelists.org> wrote:








               We are trying to create a sql which will create a script of partitions to compress which are older than a certain date.  That information is kept in High_value.  When displayed high_value looks like this



 



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



 



               So the first thing we did was to query where high_value like ‘TO_DATE%’.  We got an invalid character error.   Long story short, we discovered that high_value’s data type is long.  There must be a way to translate this into text.  SQLplus, Sqldeveloper, and TOAD all do that.  The question is how do they do it?  Does anyone know how it is done?



 



Thanks,



 



Liz















--



"In the beginning the Universe was created. This has made a lot of people very angry and been widely regarded as a bad move." -Douglas Adams













--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 17 2018 - 19:18:12 CET












Original text of this message