external table -- problem :-(((

[petzi]

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 ....

[DaPi]

I think I would first blindly load the data into a table with a fixed name, and then do the complicated logic with that. (There might be more direct, elegant solutions, but let's KISS).

You can dynamically construct your SQL statement to copy from external to internal table and then execute it with EXECUTE IMMEDIATE: http://download-west.oracle.com/docs...ynam.htm#13131

[petzi]

can you give me an exmample???

thx.

[DaPi]

Declare
   my_sql varchar2(200);
Begin
   Execute Immediate 'truncate fixed_table';
   Select 'Insert Into fixed_table (Select * From DATA_'||to_char(sysdate,'YYYYMMDD')||')' 
           Into my_sql From dual;
   dbms_output.put_line(my_sql);
   Execute Immediate my_sql;
   Commit;
End;

[petzi]

thx...

i got an error code because of "SQL command not properly ended" -

it's a stored procedure creating a sequence for the DATA_YYYYMMDD table



create or replace procedure proc_seq
as
v_sql_text varchar(255);
v_seq_text varchar(512);
begin
select (max(cdr_id)+1) into v_sql_text from cdr_'|| to_char(sysdate,'YYYYMMDD') ||';
v_seq_text :='create or replace sequence cdr_id_seq incremented by 1 start with '|| v_sql_text ||'';
execute immediate v_seq_text;
end;




???

*griazi*

[DaPi]

select (max(cdr_id)+1) into v_sql_text from cdr_'|| to_char(sysdate,'YYYYMMDD') ||';

. . . is exactly what you CAN'T do without Dynamic SQL = Execute Immediate. You need dynamic SQL when:
- you want to make the names of any objects variable.
- you want to execute DDL in PL/SQL.

If you want to work directly with the external table, you will have to construct ALL your SQL the way I suggested to construct the INSERT statement. This is why I suggested to limit the name dependant logic to one staement by dumping everything into a table with a fixed name.

[petzi]

sorry, but @the moment I'm a little bit confused....
:-(

*hihi* this should run in 2 days....

THX.

[petzi]

it's a requirement that every day a new table is created because of 6-8 000 000 records a day.

[DaPi]

That's a requirement or an imposed solution?
Sounds like whoever imposed the solution should be helping you with this!

I guess you will have to construct ALL your SQL from the date, including CREATE TRIGGER etc etc. This is not a good solution.
BTW - my appologies for mis-reading your original post - I now realise that the external table has fixed name and the internal table name is date dependant. The principle of the code I posted remains the same:

Declare
   my_sql varchar2(200);
   tb_name varchar2(30);
Begin
   Select 'DATA_'||to_char(sysdate,'YYYYMMDD') Into tb_name From dual;
   my_sql := 'Insert Into '||tb_name||' (Select * From ext_table)' 
   dbms_output.put_line(my_sql);
   Execute Immediate my_sql;
   Commit;
End;

Daily structure, 8 mio rows per day - how about partitioning?

To be honest (no reflection on you, petzi) this doesn't sound like a problem for an "Oracle newbie".

[petzi]

*hihi* my boss thinks that EVERYBODY can do that SH..!!!


thx for your help.

*greetings from AUSTRIA to my favourite SWITZERLAND*