How to get section names in oracle while I was entering date
I have a table with many sections. I need to get the name of the entire section when I give the date. For example, if I entered the date 20/09/2014, it should list all sections up to the specified date.
create or replace function get_part_name(p_date in date)
return varchar2 is
d date;
retp varchar2(30);
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
cursor c is
select high_value, partition_name p
from user_tab_partitions
where table_name='TEST';
begin
for r in c loop
str := r.high_value;
execute immediate 'select '||str||' from dual' into d;
if p_date<d and d<mind then
retp:=r.p;
mind:=d;
end if;
end loop;
return retp;
end;
This is keeping one date. I need all dates, is this possible?
source to share
WITH DATA AS (
select table_name,
partition_name,
to_date (
trim (
'''' from regexp_substr (
extractvalue (
dbms_xmlgen.getxmltype (
'select high_value from all_tab_partitions where table_name='''
|| table_name
|| ''' and table_owner = '''
|| table_owner
|| ''' and partition_name = '''
|| partition_name
|| ''''),
'//text()'),
'''.*?''')),
'syyyy-mm-dd hh24:mi:ss')
high_value_in_date_format
FROM all_tab_partitions
WHERE table_name = 'SALES' AND table_owner = 'SH'
)
SELECT * FROM DATA
WHERE high_value_in_date_format < SYSDATE
/
TABLE_NAME PARTITION_NAME HIGH_VALU
-------------------- -------------------- ---------
SALES SALES_Q4_2003 01-JAN-04
SALES SALES_Q4_2002 01-JAN-03
SALES SALES_Q4_2001 01-JAN-02
SALES SALES_Q4_2000 01-JAN-01
SALES SALES_Q4_1999 01-JAN-00
SALES SALES_Q4_1998 01-JAN-99
SALES SALES_Q3_2003 01-OCT-03
SALES SALES_Q3_2002 01-OCT-02
SALES SALES_Q3_2001 01-OCT-01
SALES SALES_Q3_2000 01-OCT-00
SALES SALES_Q3_1999 01-OCT-99
SALES SALES_Q3_1998 01-OCT-98
SALES SALES_Q2_2003 01-JUL-03
SALES SALES_Q2_2002 01-JUL-02
SALES SALES_Q2_2001 01-JUL-01
SALES SALES_Q2_2000 01-JUL-00
SALES SALES_Q2_1999 01-JUL-99
SALES SALES_Q2_1998 01-JUL-98
SALES SALES_Q1_2003 01-APR-03
SALES SALES_Q1_2002 01-APR-02
SALES SALES_Q1_2001 01-APR-01
SALES SALES_Q1_2000 01-APR-00
SALES SALES_Q1_1999 01-APR-99
SALES SALES_Q1_1998 01-APR-98
SALES SALES_H2_1997 01-JAN-98
SALES SALES_H1_1997 01-JUL-97
SALES SALES_1996 01-JAN-97
SALES SALES_1995 01-JAN-96
28 rows selected.
SQL>
Use the date you want instead SYSDATE
in the above query. Or you can pass it as an INPUT through FUNCTION
and RETURN
a result set.
source to share
Find section name using date if you have meaningful date column in table in Oracle DB
WITH table_sample AS (select COLUMN_WITH_DATE from table SAMPLE (5))
SELECT uo.SUBOBJECT_NAME AS "PARTITION_NAME_1"
FROM table_sample sw,
SYS.USER_OBJECTS uo
WHERE sw.COLUMN_WITH_DATE = TRUNC(SYSDATE) -- ENTER DATE HERE AS 'DD-MM-YYYY 00:00:00'
AND OBJECT_ID = dbms_rowid.rowid_object(sw.rowid)
AND ROWNUM < 2;
source to share
Unified SQL solution: (high_value must be converted to date with correct format!)
SELECT
partition_name p
from user_tab_partitions
where table_name='TEST'
AND high_value < to_date('4444-01-01','yyyy-mm-dd') AND high_value > SYSDATE;
PL / SQL solution:
Create a global type;
create type ty_partition_names is table of varchar2(30);
/
Functions:
create or replace function get_part_name(p_date in date)
return ty_partition_names is
d date;
retp ty_partition_names := ty_partition_names();
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
idx number := 0;
cursor c is
select high_value, partition_name p
from user_tab_partitions
where table_name='test';
begin
for r in c loop
str := r.high_value;
/*execute immediate 'select '||str||' from dual' into d; */
if p_date<str and str <mind then
retp.extend(1);
idx := idx + 1;
retp(idx):=r.p;
mind:=str;
end if;
end loop;
return retp;
end;
Finally,
SELECT * FROM TABLE(get_part_name(sysdate));
source to share