Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003

    Question Unanswered: external tables - PROBLEMS.... :-((

    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


    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 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 a lot for your help....

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Why create a new table every day? It would be much better to have a single table with a column for the date. That would resolve your current problem, and many others too.

  3. #3
    Join Date
    Nov 2003

    Exclamation specification - requirement

    no chance - it's a requirement...
    about 6 - 8 000 000 of records a day.
    (that's why I have to load the records each hour in the db)

  4. #4
    Join Date
    Jan 2004
    i hope i understand your problem, but i think you simply can use the

    ( ASDF, QWER, XCVB ...)
    ASDF, QWER, XCVB ...

    of course you can use hints and all the other usefull things to make it fast.

  5. #5
    Join Date
    Sep 2004
    London, UK
    One immediate problem,
    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.

  6. #6
    Join Date
    Nov 2003


    thanks for your hint.

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

    I am googling for days....

  7. #7
    Join Date
    Jun 2004
    Liverpool, NY USA
    The easist way t do this is to use a unix script that would use a mv to rename the file to a known directory and name. Then invoke your import program.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Sep 2004
    London, UK
    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 || '''';

    ...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.
    Last edited by WilliamR; 09-24-04 at 19:08.

  9. #9
    Join Date
    Nov 2003

    Red face

    THX - that's perfect.

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

    Sorry...about the questions...

  10. #10
    Join Date
    May 2004
    BA [ARG]
    I solved that issue including in the procedure that handles the import of data an ALTER TABLE sentence, like this:

      l_tabla_externa all_tables.table_name%type;
      l_sql           varchar2(2048);
      l_filename         varchar2(512);
        select 'alter table ' || table_name || ' '
            || 'location (''' || l_filename || ''') '
          into l_sql
          from user_tables
         where table_name = l_tabla_externa;
         execute immediate l_sql;
        when no_data_found then print_error(-20004, 'External table not found. ');
        when others then print_error(-20005, 'Error while altering external table location. ');
    Hope you find this usefull.


Posting Permissions

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