do have some big oracle problems - I'm an Oracle newbie and I'm getting mad.....

here are my problems: :-((((

1. load data from file to external table each hour (this value is also in the config table and can be modified from the admin - i must check the value before importing the datafile :-(( ) from a 'directory' named in a config table --> that works...(create directory import_dir as '(select config_value from config where config_id=..)' --> grant read, write on directory....

but now I have to insert these data into a database table - (every day a new table is created....i made a stored procedure as a job - which is executed every day after midnight - the tablename is DATA_YYYYMMDD - e.g. DATA_20040923)

I have to check if records are almost in the table (maybe the external application has created the same file twice)...and check all datatypes - numeric, date..etc...

and create a "log table" and "error table" with these bad records from the file (EVERY HOUR - depends on the config value)

Can you help me please, I don't know how to import all records fronm the external table to the 'real table' because of the 'dynamic' table name?



(just a short overview) table structureATA_YYYYMMDD

--------------------

data_id number(20) (primary key),

data_date date,

......

data_fil_id number(10) (foreign key) referneces data_file (fil_id)

---------------------------------------------

--> primary key

---> create trigger - but how? the tablename is modified every day?



2. For each data file that was imported, i have to create a foreign key... how? filename is variable depends on the external application...now I'm testing and using a fix filename - can you give me some hints how to "select" or query or however this filename???

System is Unix and Oracle 9.2



THX ....