1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to load csv file into oracle 9i

Discussion in 'Other Development Tools' started by loenex, Oct 8, 2009.

  1. loenex

    loenex Guest

    HI!
    I’m new to oracle. I Need your help in importing csv file into database. We are using oracle 9i. The csv file will be stored in 2 tables. One for hrc4cdm and the other will be for hrc chemical properties. My test table was test.
    Here are the tables:
    hrc4cdm (
    coil_no, part_no,
    piece_id_no,
    seq_no,
    slab_no,
    nextline,
    move flag,
    nominal_gauge,
    gauge_ave,
    sched_gauge,
    sched_width,
    width_ave,
    width_min,
    fm-temp_ave,
    coiling_temp_ave,
    prod_date,
    seq_no)

    hrc_chem_properties (
    grade,
    chemistry,
    supplier,
    vessel,
    c,
    si,
    mn,
    p,
    sal,
    cu,
    ni,
    cr,
    mo,
    n,
    ars,
    sn )

    I created a window for locating the csv file and a button trigger to load the csv file but the problem was it can load the csv.

    Here is the code:

    Code (Text):
    DECLARE
       v_username      VARCHAR2 (30) := GET_APPLICATION_PROPERTY (username);
       v_password      VARCHAR2 (30) := GET_APPLICATION_PROPERTY (password);
       v_connect       VARCHAR2 (30) := GET_APPLICATION_PROPERTY (connect_string);
       v_conn_db VARCHAR2 (256)
             := v_username || '/' || v_password || '@' || v_connect;
       v_command       VARCHAR2 (1000);
       d_date          DATE := SYSDATE;
       -- time & date in character
       v_date          VARCHAR2 (7) := TO_CHAR (d_date, 'yyddmm');
       v_time          VARCHAR2 (7) := TO_CHAR (d_date, 'hh24mi');
       -- get directory
       v_filename      VARCHAR2 (256) := :ctl_load.un_filename;
       --
       -- strip off data to \data\ to get the ..\load\ folder for the control file
       --
       v_dir VARCHAR2 (256)
             := SUBSTR (v_filename, 1, INSTR (v_filename, '\data\', -1));
       v_ctl_file      VARCHAR2 (256) := v_dir || 'received_slabs_ctl.txt';
       v_log_file VARCHAR2 (256)
             := v_dir || 'log\rcv_slab_' || v_date || v_time || '.log';
       v_bad_file VARCHAR2 (256)
             := v_dir || 'log\rcv_slab_' || v_date || v_time || '.bad';
       v_dis_file VARCHAR2 (256)
             := v_dir || 'log\rcv_slab_' || v_date || v_time || '.dis';
       --
       --
       n_befor_count   NUMBER (8);
       n_after_count   NUMBER (8);
       n_uploaded      NUMBER (8);
    BEGIN
       IF :ctl_load.un_filename IS NULL
       THEN
          alert_pkg.msg ('CSV file required to upload.', 'S', TRUE);
       END IF;

       -- before count
       DECLARE
       BEGIN
          FOR befor_cnt IN (SELECT   COUNT (heat_no) cnt FROM hrc_chem_properties)
          LOOP
             n_befor_count := befor_cnt.cnt;
          END LOOP;
       END;

       --
       -- alert_pkg.msg('n_befor_count -> '||n_befor_count);
       --
       v_command :=
             'sqlldr73 userid='
          || v_conn_db
          || '  control='
          || v_ctl_File
          || '  data='
          || v_filename
          || '  bad='
          || v_bad_file
          || '  log='
          || v_log_file
          || '  discard='
          || v_dis_file;

       MESSAGE ('Loading data to Oracle...', no_acknowledge);
       SYNCHRONIZE;
       HOST (v_command, no_screen);

       --
       IF NOT FORM_SUCCESS
       THEN
          alert_pkg.msg ('Error in data load. Please check the log file.',
                         'S',
                         TRUE);
          MESSAGE ('Error in data load. Please check the log file.',
                   no_acknowledge);
          SYNCHRONIZE;
       ELSE
          -- after count
          DECLARE
          BEGIN
             FOR after_cnt
             IN (SELECT   COUNT (heat_no) cnt FROM hrc_chem_properties)
             LOOP
                n_after_count := after_cnt.cnt;
             END LOOP;
          END;

          -- alert_pkg.msg(n_after_count);
          --
          n_uploaded := NVL (n_after_count, 0) - NVL (n_befor_count, 0);
          --
          -- alert_pkg.msg(n_uploaded);
          --
          alert_pkg.msg(n_uploaded
                        || ' record/s uploaded. Please check the log file for verification.');
          MESSAGE(n_uploaded
                  || ' record/s uploaded. Please check the log file for verification.');
          SYNCHRONIZE;
       END IF;
    END;
    I really don’t know how to convert this into oracle 9i. pls help me.