DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: HELP-Load Text File into Oracle Table - PL/SQL Script

  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Exclamation HELP-Load Text File into Oracle Table - PL/SQL Script

    It seems simple enough, but it's not working for me. If anyone can help with the syntax to make this work, i'd really appreciate it:

    File(s):
    "DataExtract-20051231.txt"
    "DataExtract-20051222.txt"
    "DataExtract-20051208.txt"

    Contents of a file could be:

    34 John A.Smith jasmith Engineer
    23 Jim P.Johnson jpjohnson Developer
    43 Bob D.User bduser King


    Lets say

    The First 4 characters are: Age
    Next 5: First name
    Next 2: Middle Initial
    Next 8: Last name
    Next 10: Username
    Last 9: Title.

    I have a table... my_table with the fields:
    AGE
    F_NAME
    M_NAME
    L_NAME
    U_NAME
    TITLE


    How do I put the data from all of these data files into the table using a PL/SQL script? Any ideas?

    Thank you!

  2. #2
    Join Date
    May 2002
    Posts
    2,645

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    How do I put the data from all of these data files into the table using a PL/SQL script? Any ideas?
    Where is your script?

    Tamil

  4. #4
    Join Date
    Jan 2006
    Posts
    6
    This is the only READ_FILE procedure that compiled.


    CREATE OR REPLACE PROCEDURE READ_FILE
    (PI_DIRECTORY IN VARCHAR2,
    PI_FILE_NAME IN VARCHAR2)
    AS
    V_File_handle UTL_FILE.FILE_TYPE;
    V_FILE_Line VARCHAR2(906);
    BEGIN
    V_File_handle :=
    UTL_FILE.FOPEN(PI_DIRECTORY, PI_FILE_NAME, 'R');
    LOOP
    UTL_FILE.GET_LINE( V_File_handle , v_file_line);
    DBMS_OUTPUT.PUT_LINE(v_file_line);
    END LOOP;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN UTL_FILE.FCLOSE( V_File_handle );
    END;

    /

  5. #5
    Join Date
    Jan 2006
    Posts
    6
    This link was related... :

    http://www.dbasupport.com/oracle/ora9i/data_obj2.shtml


    I'm going to try to post the important tidbits here...
    Last edited by bullcrap420; 01-10-2006 at 05:52 PM.

  6. #6
    Join Date
    Jan 2006
    Posts
    6
    Apparently, here is how to create a directory and set the permissions:

    In order to create a directory you, (the database user), should have the following privileges:

    CREATE ANY DIRECTORY.

    CREATE OR REPLACE DIRECTORY test_files AS ‘E:\oracleWork’;

    By default, you do get the READ WRITE privileges on this object. However, if you wish to assign a READ WRITE privilege to another user you can GRANT the necessary privileges as follows:

    GRANT READ ON DIRECTORY test_files TO PUBLIC;

  7. #7
    Join Date
    Jan 2006
    Posts
    6
    This is how to use a view to see the data from the file, but not load it:

    Consider a data file "emp_load.dat"

    This file emp_load.dat should be located in the physical directory "E:\oracleWork" identified by the directory object TEST_FILES.

    The DDL to create the external table will be as follows:

    CREATE TABLE emp_external
    ( emp_id NUMBER(4)
    , ename VARCHAR2(12)
    , job VARCHAR2(12)
    , mgr_id NUMBER(4)
    , hiredate DATE
    , salary NUMBER(8)
    , comm NUMBER(8)
    , dept_id NUMBER(2))
    ORGANIZATION EXTERNAL
    (TYPE oracle_loader
    DEFAULT DIRECTORY TEST_FILES
    ACCESS PARAMETERS (records delimited BY newline
    fields terminated BY ',')
    LOCATION ('emp_load.dat')
    );

    To view the data, all you have to do is query the table like a regular table viz:

    SELECT * FROM emp_external;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width