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!

flat file to table columns

Discussion in 'SQL PL/SQL' started by Bharat, Jun 10, 2015.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi All,

    Is it possible to load the column values from Flat File (Fixed Width) Column values to table columns using pl/sql program ?

    For now we have loaded the file to blob variable in another table. Now we want to read the BLOB content and need to place in another table in column wise.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It's not really clear what you're asking. I load data from flat files into Oracle tables all the time. The external tables capability of Oracle makes doing so very simple. I normally use delimited files, but the SQL Loader engine user for external tables can also handle fixed width files.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO


    Why would you use PL/SQL when external tables are available? Here's an example using delimited records:


    Code (SQL):

    SQL> CREATE TABLE admin_ext_employees
      2                     (test_id           NUMBER(4),
      3                      test_name         VARCHAR2(25)
      4                     )
      5       ORGANIZATION EXTERNAL
      6       (
      7         TYPE ORACLE_LOADER
      8         DEFAULT DIRECTORY admin_dat_dir
      9         ACCESS PARAMETERS
     10         (
     11           records delimited BY newline
     12           badfile admin_bad_dir:'loader_tst%a_%p.bad'
     13           logfile admin_log_dir:'loader_tst%a_%p.log'
     14           FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     15           missing FIELD VALUES are NULL
     16           ( test_id, test_name
     17           )
     18         )
     19         LOCATION ('loader_tst1.dat', 'loader_tst2.dat')
     20  --       LOCATION ('loader_tst1.dat')
     21       )
     22       PARALLEL
     23       REJECT LIMIT UNLIMITED;


    TABLE created.
     

    It can also be done with fixed-length records, just like with SQL*Loader, using the following syntax:


    Code (SQL):

    SQL> CREATE TABLE admin_ext_employees
      2                     (test_id           NUMBER(4),
      3                      test_name         VARCHAR2(25)
      4                     )
      5       ORGANIZATION EXTERNAL
      6       (
      7         TYPE ORACLE_LOADER
      8         DEFAULT DIRECTORY admin_dat_dir
      9         ACCESS PARAMETERS
     10         (
     11           records delimited BY newline
     12           badfile admin_bad_dir:'loader_tst%a_%p.bad'
     13           logfile admin_log_dir:'loader_tst%a_%p.log'
     14           FIELDS ldrtrim
     15           missing FIELD VALUES are NULL
     16           ( test_id (1:4) CHAR(4),
     17             test_name (6:30) CHAR(25)
     18           )
     19         )
     20         LOCATION ('loader_tst1_fxd.dat', 'loader_tst2_fxd.dat')
     21  --       LOCATION ('loader_tst1.dat')
     22       )
     23       PARALLEL
     24       REJECT LIMIT UNLIMITED;


    TABLE created.
     

    I think this would be easier than loading physical table in the database, but that's your choice to make.