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@xxxxxxxxxxxxx> 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