external table

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Erohsik
    #1

    external table

    I am using external tables to load data from the data file. But the
    log files
    are getting created in the datafile's directory (because of CREATE OR
    REPLACE DIRECTORY ...)
    But I want those log files get created in a perticular directory. How
    can I do that ?
    Thank you
    Tags: None
    • Billy Verreynne
      #2
      Re: external table

      helpora@yahoo.c om (Erohsik) wrote in message
      I am using external tables to load data from the data file. But the
      log files
      are getting created in the datafile's directory (because of CREATE OR
      REPLACE DIRECTORY ...)
      But I want those log files get created in a perticular directory. How
      can I do that ?
      Parsing error. Provide more detail.

      What external tables? Are you loading into a remote db via an Oracle
      db link?

      What log files? CREATE DIRECTORY is for using BFILES. Not log files
      (as in alert log, sql loader log, import log) files.

      --
      Billy

        Comment

        • Erohsik
          #3
          Re: external table

          Billy,

          I am using external tables to load data from a data file to an Oracle
          tables (instead of using SQLLoader).

          Before we create an external table, we use CREATE OR REPLACE DIRECTORY
          testing AS '/../../../testing/' command.
          The data file(s) should be in that (testing) directory.

          After creating the external table, we load the data from external
          table to the existing table,
          then the log files and the bad files go to the 'testing' directory
          where the data files are.

          Now my request: I need to chang the code so that the log files and the
          bad files go to a specified
          directory which I specify in the 'create directory.... (.sql) script.

          How to specify that ?



          vslabs@onwe.co. za (Billy Verreynne) wrote in message news:<1a75df45. 0309110443.1445 e0fc@posting.go ogle.com>...
          helpora@yahoo.c om (Erohsik) wrote in message
          >
          I am using external tables to load data from the data file. But the
          log files
          are getting created in the datafile's directory (because of CREATE OR
          REPLACE DIRECTORY ...)
          But I want those log files get created in a perticular directory. How
          can I do that ?
          >
          Parsing error. Provide more detail.
          >
          What external tables? Are you loading into a remote db via an Oracle
          db link?
          >
          What log files? CREATE DIRECTORY is for using BFILES. Not log files
          (as in alert log, sql loader log, import log) files.

            Comment

            • Billy Verreynne
              #4
              Re: external table

              helpora@yahoo.c om (Erohsik) wrote
              I am using external tables to load data from a data file to an Oracle
              tables (instead of using SQLLoader).
              <snipped>
              Now my request: I need to chang the code so that the log files and the
              bad files go to a specified
              directory which I specify in the 'create directory.... (.sql) script.
              >
              How to specify that ?
              Something like:


              CREATE OR REPLACE DIRECTORY csv AS '/csv'

              CREATE OR REPLACE DIRECTORY log AS '/log'

              CREATE OR REPLACE DIRECTORY dump AS '/dump'


              CREATE TABLE foo_external (
              place_name varchar2(100),
              postcode varchar2(4),
              streetcode varchar2(4),
              town_name varchar2(100)
              )
              ORGANIZATION EXTERNAL
              (TYPE oracle_loader
              DEFAULT DIRECTORY CSV
              ACCESS PARAMETERS
              (
              RECORDS DELIMITED BY newline
              BADFILE DUMP:'postcodes .bad'
              DISCARDFILE DUMP:'postcodes .dis'
              LOGFILE LOG:'postcodes. log'
              FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
              (
              place_name char(100),
              postcode char(4),
              streetcode char(4),
              town_name char(100)
              )
              )
              LOCATION ('postcodes.csv ')
              )
              REJECT LIMIT UNLIMITED;


              --
              Billy

                Comment

                • Erohsik
                  #5
                  Re: external table

                  Thank you Billy


                  vslabs@onwe.co. za (Billy Verreynne) wrote in message news:<1a75df45. 0309120409.2a2a 3226@posting.go ogle.com>...
                  helpora@yahoo.c om (Erohsik) wrote
                  >
                  I am using external tables to load data from a data file to an Oracle
                  tables (instead of using SQLLoader).
                  <snipped>
                  Now my request: I need to chang the code so that the log files and the
                  bad files go to a specified
                  directory which I specify in the 'create directory.... (.sql) script.

                  How to specify that ?
                  >
                  Something like:
                  >
                  >
                  CREATE OR REPLACE DIRECTORY csv AS '/csv'
                  >
                  CREATE OR REPLACE DIRECTORY log AS '/log'
                  >
                  CREATE OR REPLACE DIRECTORY dump AS '/dump'
                  >
                  >
                  CREATE TABLE foo_external (
                  place_name varchar2(100),
                  postcode varchar2(4),
                  streetcode varchar2(4),
                  town_name varchar2(100)
                  )
                  ORGANIZATION EXTERNAL
                  (TYPE oracle_loader
                  DEFAULT DIRECTORY CSV
                  ACCESS PARAMETERS
                  (
                  RECORDS DELIMITED BY newline
                  BADFILE DUMP:'postcodes .bad'
                  DISCARDFILE DUMP:'postcodes .dis'
                  LOGFILE LOG:'postcodes. log'
                  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                  (
                  place_name char(100),
                  postcode char(4),
                  streetcode char(4),
                  town_name char(100)
                  )
                  )
                  LOCATION ('postcodes.csv ')
                  )
                  REJECT LIMIT UNLIMITED;

                    Comment

                    Previous template Next
                    Working...