Oracle sql Sub query to check table exists from first day to last

My requirement is to check from the first date of current month ie 01/12/2010 with table name in the format suresh_20101201 exists in the database, if not then it should check for table suresh_20101202 and thereon till suresh_20101231 . is it possible to do 开发者_高级运维in oracle sql query.

for example

select  object_name from all_objects where object_type ='TABLE' 
and upper(object_name) = 'SURESH_' || to_char(TRUNC(SYSDATE, 'mm'),'YYYYMMDD')  
and ROWNUM =1

the above query should run till last day ,if no table exists from 01 to 29th of dec.

You could create a convenience view such as this:

CREATE OR REPLACE VIEW ALL_SURESH_TABLES AS
SELECT
  table_name,
  to_date(regexp_replace(table_name, 'SURESH_([[:digit:]]+)', '\1'), 'YYYYMMDD')
    AS table_date
FROM
  ALL_TABLES
WHERE
  regexp_like(table_name, 'SURESH_[[:digit:]]+')

And then query it like this:

SELECT
  MAX(table_name) KEEP (DENSE_RANK LAST ORDER BY table_date)
FROM
  ALL_SURESH_TABLES
WHERE
  TRUNC(table_date, 'mm') = TRUNC(SYSDATE, 'mm')

(I didn't verify the second query, but it should be straight forward once the ALL_SURESH_TABLES view is created)

Edit: In light of your comment regarding the "ORA-01830" error, I am led to believe that you have at least one table whose name starts with SURESH_, ends with digits, but those digits don't form a valid date. For example, this causes the same error to occur:

/* Notice the "date" has nine digits instead of eight */
SELECT to_date('201012088', 'YYYYMMDD') FROM DUAL

Therefore, I recommend you change this:

regexp_replace(table_name, 'SURESH_([[:digit:]]+)', '\1')

into this:

regexp_replace(table_name, 'SURESH_([[:digit:]]{8}).*', '\1')

The modified regular expression will only pay attention to the first eight digits after "SURESH_".