external table -- problem :-(((
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: external table -- problem :-(((

  1. #1
    Join Date
    Sep 2004
    Posts
    24

    Question external table -- problem :-(((

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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  3. #3
    Join Date
    Sep 2004
    Posts
    24
    can you give me an exmample???

    thx.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Code:
    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;

  5. #5
    Join Date
    Sep 2004
    Posts
    24

    Question error

    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*

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  7. #7
    Join Date
    Sep 2004
    Posts
    24
    sorry, but @the moment I'm a little bit confused....
    :-(

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

    THX.

  8. #8
    Join Date
    Sep 2004
    Posts
    24
    it's a requirement that every day a new table is created because of 6-8 000 000 records a day.


  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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:
    Code:
    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".

  10. #10
    Join Date
    Sep 2004
    Posts
    24

    Talking

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


    thx for your help.

    *greetings from AUSTRIA to my favourite SWITZERLAND*

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width