i 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
---> 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???
create directory import_dir as
'(select config_value from config where config_id=..)'
will not work. (That is, it will create a directory object successfully, but when you try to use it, Oracle will go looking for an OS directory named '(select config_value from config where config_id=..)' ). You must provide a literal path name.
Surely adding a partition to an existing table would be a more practical design. For one thing, existing applications would be able to query it, which I don't see how they can with the DATA_YYYYMMDD approach.
how can i pass the literal path? can you give me an example?
Do i have to use cursors? (Sorry, I'm a really newbie...)
maybe there's another possibilty loading the records into the internal_table.
in the datafile the first datafield is a date (DATA_date - e.g. 20040923 --> this record needs to be inserted into the DATA_20040923 table,
- it's possible of an external application error - that a record 20040922 is in the same datafile)
I have to insert the record into the "right" DATA_YYYYMMDD table.
Is it possible to query all records on the external table (after the datafile is loaded into the external table) e.g. for "date" and then insert the "right" records into the "right" table???
You need dynamic SQL anyway to create database objects, so it would be something like
SELECT config_value INTO v_config_dir FROM config WHERE config_id =...;
v_sql := 'CREATE DIRECTORY import_dir AS ''' || v_config_dir || '''';
EXECUTE IMMEDIATE v_sql;
...Of course after the first time you run this the directory object will already exist, so perhaps that should be
v_sql := 'CREATE OR REPLACE DIRECTORY import_dir AS ''' || v_config_dir || '''';
...unless another process is already using IMPORT_DIR, or relying on it not changing its location without warning...
after the datafile is loaded into the external table
Not sure what you mean. The external table is just a way of reading the contents of the datafile. The datafile itself stays put.
and then insert the "right" records into the "right" table
That is what partitioned tables do automatically. For example:
CREATE TABLE test_partitions
( partition_key NUMBER(4) NOT NULL
, local_key NUMBER(4) NOT NULL
, value VARCHAR2(40) NOT NULL )
PARTITION BY RANGE (partition_key)
( partition p1 VALUES LESS THAN (2)
, partition p2 VALUES LESS THAN (3)
, partition p3 VALUES LESS THAN (MAXVALUE) );
Now Oracle will automatically place any record in the correct partition according to the value of "partition_key" and the PARTITION BY clause.
Can you give me a hint, how to solve the problem regarding the "not fix" file name??
should i rename the file?? but i have to move the imported file to a new directory which is also a value in the config table....