SQL & PL/SQL

Dear All,

I would like to run an automated script in solaris,

for that i need to run this query :

select application,count(*) from MBL where to_char(date_time,'ddmmyy') = to_char(sysdate, 'ddmmyy' ) group by application;

instead of the above query, since partition was employed i would like to use the following query :

select application,count(*) from mbl partition( p_23nov2006 )

Partition name is : p_23nov2006, p_24nov2006, p_25nov2006 and so on.

Hence my problem is to get the Partition name as an output from the following first query and pass this value into the second query.

select 'p_'||to_char(sysdate,'ddmonyyyy') from dual; result will be p_23nov2006

select count(*) from mbl partition( First_Query_Result )


I would like to use simple sql, since this query will be executed on hourly basis on 30 tables, i dont want to use procedures and functions.



Could you help me on this.

Thanks and Regards,
P.Sivakumar
chennsk@yahoo.com