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;

   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