Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Loader - Alternative Option??

Re: SQL Loader - Alternative Option??

From: <fitzjarrell_at_cox.net>
Date: Fri, 06 Jul 2007 12:16:27 -0700
Message-ID: <1183749387.233733.209620@q75g2000hsh.googlegroups.com>


On Jul 6, 1:54 pm, fd96..._at_yahoo.com wrote:
> Hi,
>
> Is there any other option of loading file into a database apart from
> SQL loader and tools like informatica?
>
> We have a set up with 2 boxes, with a limitation that one is an app
> server, other the database. And the requirement is we load the files
> from the app server box to the oracle db.
>
> One of the options is create insert scripts using unix shell scripting
> and load the file into the database using oracle instant client for
> 10g. Is there any other option???
>
> Please let me know.
>
> Thanks

Yes, it's called External Tables, however these are limited in that you cannot index them and the source file for each table must reside on the database server. You may also need to create directories (within Oracle) to allow 'housekeeping' files to be written. The overall syntax is very much like SQL*Loader; an example from the documentation appears below:

CONNECT / AS SYSDBA;
-- Set up directories and grant access to hr CREATE OR REPLACE DIRECTORY admin_dat_dir

    AS '/flatfiles/data';
CREATE OR REPLACE DIRECTORY admin_log_dir

    AS '/flatfiles/log';
CREATE OR REPLACE DIRECTORY admin_bad_dir

    AS '/flatfiles/bad';
GRANT READ ON DIRECTORY admin_dat_dir TO hr; GRANT WRITE ON DIRECTORY admin_log_dir TO hr; GRANT WRITE ON DIRECTORY admin_bad_dir TO hr; -- hr connects
CONNECT hr/hr
-- create the external table
CREATE TABLE admin_ext_employees

                   (employee_id       NUMBER(4),
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25),
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY admin_dat_dir
       ACCESS PARAMETERS
       (
         records delimited by newline
         badfile admin_bad_dir:'empxt%a_%p.bad'
         logfile admin_log_dir:'empxt%a_%p.log'
         fields terminated by ','
         missing field values are null
         ( employee_id, first_name, last_name, job_id, manager_id,
           hire_date char date_format date mask "dd-mon-yyyy",
           salary, commission_pct, department_id, email
         )
       )
       LOCATION ('empxt1.dat', 'empxt2.dat')
     )
     PARALLEL
     REJECT LIMIT UNLIMITED;

For what you're wanting to accomplish (loading tables from a client) it appears SQL*Loader is likely your best option (remember the files for external tables must reside on the database server)..

David Fitzjarrell Received on Fri Jul 06 2007 - 14:16:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US