1 Reply Latest reply: Dec 7, 2012 1:16 PM by L-MachineGun RSS

    sql loader problem

    kaminanikamini
      i am using oracle database version 10.2.0.1.0

      i have data in CSV file in following format

      value1,data2,data3
      ........,data2,data3
      ........,data2,data3
      ........,data2,data3
      value2,data2,data3
      ........,data2,data3
      ........,data2,data3
      ........,data2,data3
      value3,data2,data3
      ........,data2,data3
      ........,data2,data3
      ........,data2,data3

      how can i insert it in data base in following format using sql*loader

      value1,data2,data3
      value1,data2,data3
      value1,data2,data3
      value1,data2,data3
      value2,data2,data3
      value2,data2,data3
      value2,data2,data3
      value2,data2,data3
      value3,data2,data3
      value3,data2,data3
      value3,data2,data3
      value3,data2,data3
        • 1. Re: sql loader problem
          L-MachineGun
          '
          Do it like this:

          1) Create external table:
          CREATE TABLE test_csv
          ( v1   VARCHAR2 (10)
          , d2   VARCHAR2 (10)
          , d3   VARCHAR2 (10))
           ORGANIZATION EXTERNAL
            ( TYPE oracle_loader
              DEFAULT DIRECTORY mydir
              ACCESS PARAMETERS
              ( FIELDS TERMINATED BY "," 
                ( v1, d2, d3 )
              )
             LOCATION ('test.csv')
            )
          /
          2) Then code PL/SQL program to load into db table:
          SQL> DECLARE
            2    lst_v1   VARCHAR2 (10) := '?';
            3  BEGIN
            4    FOR x IN (SELECT * FROM test_csv)
            5    LOOP
            6      IF x.v1 IS NOT NULL
            7      THEN
            8        lst_v1   := x.v1;
            9      END IF;
           10
           11      -- Here you insert into database table:
           12      DBMS_OUTPUT.put_line (lst_v1 || ', ' || x.d2 || ', ' || x.d3);
           13    END LOOP;
           14  END;
           15  /
          value1, data2, data3
          value1, data2, data3
          value1, data2, data3
          value1, data2, data3
          value2, data2, data3
          value2, data2, data3
          value2, data2, data3
          value2, data2, data3
          value3, data2, data3
          value3, data2, data3
          value3, data2, data3
          value3, data2, data3
          
          PL/SQL procedure successfully completed.
          :p