Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Cool Unanswered: PL/SQL Comma Delimited file read in

    Oracle 9.0.1 PL/SQL question:

    Are utilities available in PL/SQL for reading in a comma delimited flat file?

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    External table = schema object - quite self explanatory
    utl_file = pl/sql package - for accesing external files
    sql loader = os utility - loads flat files onto Oracle.

    If you're still stuck, shout.

    Cheers
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    Bill:

    Thanks for the reply.

    I've gotten to where I can read each line of the comma delimited flat file into PL/SQL using the utl_file procedures.

    However, how do I go about seperating the fields in each record in the file?

    Is there a PL/SQL function or procedure which will seperate a varchar2 variable according to a specified delimiter?

    Also, how do you access the sql loader routines?

    This is my first PL/SQL program, so I appreciate your help.

    Ennis

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    However, how do I go about seperating the fields in each record in the file?
    use INSTR find each comma, use substr to break apart

    Is there a PL/SQL function or procedure which will seperate a varchar2 variable according to a specified delimiter?
    no, although you can write your own with instr/substr functions

    Also, how do you access the sql loader routines?
    you don't. sql*loader is it's own thing. external tables have similar capabilities, but the syntax is different. you could either read the file with utl_file and parse it yourself, or create an external table which points to the file and has the column definition defined (e.g., comma sep) and then select from the external table
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Oct 2004
    Posts
    3
    Bill and SHoblock:

    Perfect!

    Thank you kindly.

    Ennis

Posting Permissions

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