Solved

Importing data into Oracle tables

Posted on 2000-02-21
11
1,004 Views
Last Modified: 2008-03-10
I am pretty new to Oracle, but recently
done courses in PL/SQL, still finding my way around.
I need to automate a task to load data
into an oracle table from a csv file.
I know about SQLloader, but I need to run the task automatically based on a scheduled date and time. How can I achieve this. Is it also possible to write a PL/SQL program to do the same.
Please suggest the best solution to get the job done and sample code/instructions.
0
Comment
Question by:pcorreya
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 6

Expert Comment

by:crsankar
Comment Utility
You can create and schedule a job using Oracle Enterprise Manager.
0
 
LVL 6

Expert Comment

by:crsankar
Comment Utility
The easiest way to do this would be to use the Oracle Data Manager. This is an application supported by Oracle Enterprise Manager. This application has a wizard driven interface which will giude you thru the process of creating a scheduled job for loading data using sql*loader. It is very easy to use. Hope this helps.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
If OEM works for you, and you don't mind its interfaces and slow response times, then you don't need to read the rest of this comment.

There are at least two options that do not involve OEM.  You didn't indicate your operating system, and I know NT best, so I will give you NT examples.  (If you have a form of Unix or something else, the same concepts work.)

One option is to write a batch file that calls SQL*Loader (with a control file).  Run SQL*Loader manually first from a DOS prompt to make sure you have the control file right and the syntax for calling SQL*Loader.  Then scedule this batch file using NT's "at" scheduler (or a different O/S job scheduler if you have one).

Another option is to use PL\SQL with utl_file.  You need to have a "utl_file_dir" entry in your init*.ora for this.  Then write a PL\SQL procedure that uses the procedures and functions in utl_file to open the *.csv file, read each line of data and insert it, then close the file when done.  This procedure can be scheduled using Oracle's job queue.

Note that utl_file is slower than SQL*Loader for doing data loads, so depending on your data volumes, this may or may not be significant.  But, the utl_file PL\SQL combination is very flexible, so if your data needs any manipulation, this can be a great way to go.  Also, note that utl_file and PL\SQL do not directly support any O/S functions (like renaming or moving a data file after processing it), but it possible.  Leave a comment here if you want more help with a utl_file approach.
0
 
LVL 1

Author Comment

by:pcorreya
Comment Utility
PL\SQL with utl_file solution sounds like what I need. We are running
Oracle 8.0.3.2.0 on a unix box and access the host using the SQLPlus client. Can you provide me with a sample PL/SQL code to do this and the configuration requirements.

I need to run some jobs from the SQLPlus client and others scheduled as mentioned before.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
If you are going to do this manually (without using OEM) SQL*Loader is the easier way to go.  Based on my (limited) knowledge of Unix, it should be easy for you to schedule a job that runs SQL*Loader.  This job can also easily do things like check for the existance of the data file first, and move or rename it after processing.

If you want to use PL\SQL, there is more coding required and you cannot do O/S tasks (like moving or renaming the data file) directly.  Also, you must have a "utl_file_dir" entry in your init*.ora file to allow utl_file to read or write to one or more directories ON THE SERVER.  Note that utl_file cannot access client files.

Here is the package I use to handle file I/O tasks (via utl_file):

create or replace package file_io as
  function open_file (full_path in varchar2, file_nm in varchar2,
   open_for in varchar2)
    return utl_file.file_type;
  procedure write_line (file_name in utl_file.file_type,
    text_in in varchar2);
  procedure read_line (file_name in utl_file.file_type,
    text_out out varchar2, continue_flag out boolean);
  procedure close_file (file_name in utl_file.file_type);
end;
/
--
create or replace package body file_io as
  function open_file (full_path in varchar2, file_nm in varchar2,
      open_for in varchar2)
    return utl_file.file_type as
    out_file utl_file.file_type;
  begin
    -- Note: this will over-write an existing file with the same name,
    --  if any.
    out_file := utl_file.fopen(full_path,file_nm,open_for);
    return out_file;
  end;
--
  procedure write_line (file_name in utl_file.file_type,
    text_in in varchar2) as
  begin
    utl_file.put_line(file_name, text_in);
  end;
--
  procedure read_line (file_name in utl_file.file_type,
    text_out out varchar2, continue_flag out boolean) as
  begin
    continue_flag := TRUE;
    utl_file.get_line(file_name, text_out);
  exception
    when no_data_found then
      continue_flag := FALSE;
      text_out := '';
  end;
--
  procedure close_file (file_name in utl_file.file_type) as
    file_nm utl_file.file_type;
  begin
    -- use a local variable because "utl_file.fclose" has its parameter
    --  defined as an "in out" parameter.  (WHY???...)
    file_nm := file_name;
    utl_file.fclose(file_nm);
  end;
end;
/

Here is a procedure that uses that package to read an ASCII file of input data from another system and compare that to data in an Oracle table.  It also uses three output files to help with data cleanup issues:

create or replace procedure update_po_cstmas as
  v_part_no  varchar2(15);
  v_cost     number(10,4);
  p_cost     number(10,4);
  p_row      rowid;
  adds       pls_integer;
  chgd       pls_integer;
  same       pls_integer;
  error      pls_integer;
  bad        pls_integer;
  obsolete   pls_integer;
  input_data varchar2(80);
  input_file utl_file.file_type;
  bad_file   utl_file.file_type;
  log_file   utl_file.file_type;
  diff_file  utl_file.file_type;
  continue   boolean;
  cursor c1 is select ccvmcs, rowid
    from cstmas
    where citmas = v_part_no;
begin
  adds  := 0;
  chgd  := 0;
  same  := 0;
  error := 0;
  bad   := 0;
  input_file := file_io.open_file('C:\temp','p_cstmas.txt','R');
  bad_file := file_io.open_file('C:\temp','p_csterr.txt','W');
  log_file := file_io.open_file('C:\temp','p_cstmas.log','W');
  diff_file := file_io.open_file('C:\temp','p_cstdif.txt','W');
  utl_file.put_line(log_file,'Started at: '||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
  continue := TRUE;
  -- Do a "priming" read outside of the loop
  file_io.read_line(input_file, input_data, continue);
  while continue loop
    begin
      v_part_no := substr(input_data,1,15);
      v_cost := to_number(substr(input_data,17,9));
    exception
      when value_error then
        error := error +1;
        utl_file.put_line(bad_file,input_data||' value');
      when others then
        error := error +1;
        utl_file.put_line(bad_file,input_data||' unknown');
    end;
    open c1;
    fetch c1 into p_cost, p_row;
    if c1%found then
      if p_cost = v_cost then
        same := same +1;
        update cstmas set verified_date = trunc(sysdate)
        where rowid = p_row;
      else
        chgd := chgd +1;
        update cstmas
        set ccvmcs = nvl(v_cost,0), verified_date = trunc(sysdate)
        where rowid = p_row;
      end if;
    else
      begin
        insert into cstmas
         (citmas, ccvmcs, verified_date)
        values(v_part_no, v_cost, trunc(sysdate));
        adds := adds +1;
        commit;
      exception
        when others then
          error := error +1;
          utl_file.put_line(bad_file,input_data||' insert error');
      end;
     end if;
    close c1;
    -- The "read" must be at the end of the loop, to exit after the read at end-of-file
    file_io.read_line(input_file, input_data, continue);
  end loop;
  file_io.write_line(log_file,'Compare done'||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
  update cstmas
  set deleted_date = trunc(sysdate)
  where nvl(verified_date,sysdate -1) < trunc(sysdate)
  and deleted_date is null;
  obsolete := sql%rowcount;
  commit;
  file_io.write_line(log_file,'Ended at:   '||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));
  file_io.write_line(log_file,'Input items '||to_char(total,'999,999'));
  file_io.write_line(log_file,'Same:       '||to_char(same,'999,999'));
  file_io.write_line(log_file,'Changed:    '||to_char(chgd,'999,999'));
  file_io.write_line(log_file,'Added:      '||to_char(adds,'999,999'));
  file_io.write_line(log_file,'Errors:     '||to_char(error,'999,999'));
  file_io.write_line(log_file,'Obsolete:   '||to_char(obsolete,'999,999'));
  file_io.close_file(log_file);
end;
/


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:pcorreya
Comment Utility
Thanks for your PL/SQL solution. So can I run this script to pick up a file from my c: drive without any further configuration changes.

Can you also give me an example of how you will run an import with sqlloader and then get it to move and rename files.


0
 
LVL 6

Expert Comment

by:crsankar
Comment Utility
crsankar changed the proposed answer to a comment
0
 
LVL 1

Expert Comment

by:prabhusameer
Comment Utility

i had achieved data transfer thro' the foll. code(this code shows transfer of data for 3 tables). try similar code.


CREATE OR REPLACE PACKAGE make_archive IS
PROCEDURE make_file(msend_cocode IN poih.cocode%type,mcocode IN poih.cocode%type);
END make_archive;


CREATE OR REPLACE PACKAGE BODY make_archive IS
--
PROCEDURE make_file(msend_cocode IN poih.cocode%type,mcocode IN poih.cocode%type) IS
--
  filehandle UTL_FILE.FILE_TYPE;
  mystr varchar2(600);
  fn varchar2(20) := mcocode||'-'||'TO'||'-'||msend_cocode||TO_CHAR(sysdate,'_dd_mon');
dirname varchar2(30) := 'c:\JslExports';
--
--
  CURSOR c_accmast_i IS
SELECT 'INSERT INTO account_master
VALUES('||accno||','||normg||','||abnormg||','||accname||','||accabb||','||
gltype||','||normbal||','||glevel||','||schno||','||category||','||
cocode||','||user_code||','||entrydt||') ;'
          FROM account_master
       WHERE cocode = mcocode
         AND entrydt = sysdate
         AND (accno,cocode) NOT IN
         (SELECT accno, cocode
      FROM account_master_hist);
--
--
  CURSOR c_poih_i IS
      SELECT 'INSERT INTO poih
            VALUES('||poino||','||poidt||','||projectno||','||delivdt||','||destcode||','||
reference||','||testcert||','||colorcode||','||inspection||','||totwt||','||
sendto||','||qtychartno||','||qtychardt||','||remark||','||status||','||
cocode||','||fys||','||user_code||','||entrydt||') ;'
          FROM poih
       WHERE send_cocode <> mcocode
         AND cocode = mcocode
         AND entrydt = sysdate
         AND (poino,fys,cocode) NOT IN
               (SELECT poino,fys,cocode
                  FROM poih_hist);
--
  CURSOR c_poit_i IS
      SELECT 'INSERT INTO poit
              VALUES('||a.poino||','||a.poidt||','||a.poisrno||','||a.accno||','||a.sub_accno||','||
drgno||','||a.quality_spec||','||a.length||','||a.qty||','||a.weight||','||
a.remark||','||a.cocode||','||a.fys||','||a.user_code||','||a.entrydt||') ;'
        FROM poit a, poih b
       WHERE b.send_cocode <> mcocode
         AND a.cocode = mcocode
           AND a.entrydt = sysdate
              AND b.cocode = a.cocode
         AND b.poino = a.poino
         AND b.fys = a.fys
           AND b.poidt = a.poidt
         AND (a.poino, a.poisrno, a.fys, a.cocode) NOT IN(SELECT poino,  poisrno, fys, cocode
            FROM poit_hist);
--
--
BEGIN
     filehandle := UTL_FILE.FOPEN(dirname,fn,'w');
--
      UTL_FILE.PUT_LINE(filehandle,'/*Account Master*/');
      OPEN c_accmast_i;
         LOOP
      FETCH c_accmast_i INTO mystr;
            
UTL_FILE.PUT_LINE(filehandle,mystr);
      EXIT WHEN c_accmast_i%NOTFOUND;
         END LOOP;
      CLOSE c_accmast_i;
--
         UTL_FILE.PUT_LINE(filehandle,'/*Purchase Order Indent Header*/');
      OPEN c_poih_i;
         LOOP
      FETCH c_poih_i INTO mystr;
            
UTL_FILE.PUT_LINE(filehandle,mystr);
      EXIT WHEN c_poih_i%NOTFOUND;
         END LOOP;
      CLOSE c_poih_i;
--
      UTL_FILE.PUT_LINE(filehandle,'/*Purchase Order Indent Tailer*/');
           OPEN c_poit_i;
         LOOP
      FETCH c_poit_i INTO mystr;
            
UTL_FILE.PUT_LINE(filehandle,mystr);
      EXIT WHEN c_poit_i%NOTFOUND;
         END LOOP;
      CLOSE c_poit_i;
--
END make_file;
--
END make_archive;
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"So can I run this script to pick up a file from my c: drive without any further configuration changes."

No, not with your database configuration (on Unix).  This works in our NT database.  Utl_file is limited to drives on the server (or to those that the server has access to, which usually does not include a client C: drive).

"Can you also give me an example of how you will run an import with sqlloader and then get it to move and rename files?"

SQL*Loader does not move or rename data files.  You will need to write an operating script to call SQL*Loader and then move or rename the data file after SQL*Loader processes it.  If your data file is on your server (and if the data file is large you will want it there) this will need to be a Unix shell script.  If your data file is on your PC, this will need to be a DOS batch file.  Either way, it is exactly the same steps you would do manually from the operating system.
0
 
LVL 1

Author Comment

by:pcorreya
Comment Utility
Sorry to bother you, I have never used sqlloader before.

So I gather from what you say that sqlloader can be run from the NT box just like sqlplus client. I will need to install sqlloader on my nt client. Is this sqlloader executable just part of the oracle 8 CD or do I need to get it seperately.
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 100 total points
Comment Utility
SQL*Loader is usually installed on the server as part of the database install.  It can be installed on a client, but you will need to do a "Custom" install and select "Oracle8 utilities".  Check your Oracle8 Client CD, I think it will have this.

The executable for NT is "sqlldr80.exe".  Check your Oracle_home\bin directory, it may be there already.  I'm not sure if the install creates a Windows shortcut for SQL*Loader, I just looked on my machine and couldn't find one.  I always run SQL*Loader from a DOS prompt or a batch file so I can specify the control file.

SQL*Loader is a fast way to load data into Oracle from ASCII files, but you do need to write a control file for each data file.  Look at the documentation for SQL*Loader and look at the sample files in your Oracle_home\Rdbms80\Loader directory (named ulcase*.ctl).

The easiest way may be to follow crsankar's suggestion in his first responses to your question, but if you prefer to do it manually, my comments should help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now