RE: Convert Long to Date
- From: "Brady, Mark" <Mark.Brady@xxxxxxxxxxxxxxxxx>
- To: <tim@xxxxxxxxx>
- Date: Thu, 26 Jun 2008 16:27:27 -0400
SO Pl/sql subtr will take a long but SQL substr does not.
begin
for p in (SELECT * FROM user_tab_partitions) loop
DBMS_OUTPUT.Put_line(substr(p.high_value, 1, p.high_value_length));
end loop;
end;
Works fine but
SELECT substr(p.high_value, 1, p.high_value_length) FROM user_tab_partitions p
Does not. That's my problem.
________________________________________
From: Tim Gorman [mailto:tim@xxxxxxxxx]
Sent: Thursday, June 26, 2008 4:16 PM
To: Brady, Mark
Cc: oracle-l-freelists
Subject: Re: Convert Long to Date
Mark,
This is embedded deep within a couple thousand lines of PL/SQL package code
posted on the internet. The package is named EXCHPART, the procedure is called
ADD_NEWER_THAN, and its URL is http://www.EvDBT.com/exchpart.sql. ; Here it is
excerpted into pseudo-PL/SQL...
procedure add_newer_than ...
...
v_errcontext varchar2(500);
v_high_value varchar2(100);
v_day date;
type cType is ref cursor;
c cType;
...
cursor get_partition_info ...
is
select ...,
high_value,
high_value_length,
...
from all_tab_partitions
...;
begin
...
for p in get_partition_info ... loop
...
v_high_value := substr(p.high_value, 1, p.high_value_length);
v_errcontext := 'select ' || v_high_value || ' from dual';
open c for v_errcontext;
fetch c into v_day;
close c;
...
end loop;
...
end;
/
So the end result is that the variable "v_day" has the value from HIGH_VALUE...
Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 1802, Evergreen CO 80437-1802
website = http://www.EvDBT.com/
email = Tim@xxxxxxxxx
mobile = +1-303-885-4526
fax = +1-303-484-3608
Yahoo IM = tim_evdbt
Brady, Mark wrote:
Oracle stores the High Value of a Partition in a Long column. I swear I've
searched the friendly web, and I just can't figure out how to get this long
column converted back to a date or char I can take a char to a date.
If it exists on the web, please tell me. I don't know why this is so hard to
find.
>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2
--
//www.freelists.org/webpage/oracle-l
Other related posts:
