Aw: Re: DBA_TAB_PARTITIONS
From: <rogel_at_web.de>
Date: Wed, 17 Jan 2018 20:44:24 +0100
Message-ID: <trinity-66885033-dd0e-4d33-be90-31314c5ecbf8-1516218264866_at_3c-app-webde-bs19>
--
-- http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 17 2018 - 20:44:24 CET
Date: Wed, 17 Jan 2018 20:44:24 +0100
Message-ID: <trinity-66885033-dd0e-4d33-be90-31314c5ecbf8-1516218264866_at_3c-app-webde-bs19>
:%s/h\.//g
return
to_date(
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 1) + 1, instr(v_high_value_expr, '''', 1, 2) - instr(v_high_value_expr, '''', 1, 1) - 1),
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 3) + 1, instr(v_high_value_expr, '''', 1, 4) - instr(v_high_value_expr, '''', 1, 3) - 1),
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 5) + 1, instr(v_high_value_expr, '''', 1, 6) - instr(v_high_value_expr, '''', 1, 5) - 1)
);
to_date(
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 1) + 1, instr(v_high_value_expr, '''', 1, 2) - instr(v_high_value_expr, '''', 1, 1) - 1),
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 3) + 1, instr(v_high_value_expr, '''', 1, 4) - instr(v_high_value_expr, '''', 1, 3) - 1),
substr(v_high_value_expr, instr(v_high_value_expr, '''', 1, 5) + 1, instr(v_high_value_expr, '''', 1, 6) - instr(v_high_value_expr, '''', 1, 5) - 1)
);
Gesendet: Mittwoch, 17. Januar 2018 um 19:18 Uhr
Von: rogel_at_web.de
An: oracle-l <oracle-l_at_freelists.org>
Betreff: Aw: Re: DBA_TAB_PARTITIONS
Von: rogel_at_web.de
An: oracle-l <oracle-l_at_freelists.org>
Betreff: Aw: Re: DBA_TAB_PARTITIONS
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)
)
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
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).
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.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;
/
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;
/
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