Stored Procedure to import text file into Oracle

Question

I have a text file that I have to check against a master database in Oracle, for duplicates, 3 times a day. I would just create an SSIS package for this in the past, but unfortunately, I'm dealing with Oracle now and I know very little about Oracle. I've been searching for days and the best solution I could come up with is to create a Sql Loader, but I can't figure out how to put that in a stored procedure.

Also, my text file is not formatted, so I have to take the first two fields by position and ignore the rest of the data in that row.

Any suggestions. I'm at a loss and any help would be greatly appreciated.

This is what I've come up with so far:

    CREATE TABLE Duplicate_Check 
    (field1 numeric(14), 
    PO numeric (7) Primary Key 
    )

         load data
         infile '\\(file location\.txt'
         into table Duplicate_Check
         (field1 position(1:14),
         PO position(16:22) ) 
        (field1, PO)

Answer 1

I would use the oracle UTL_FILE package to read the file and parse out the data you need to check for duplicates. It appears you'd only need to read the first two lines.

The file will have to exist on a path accessible by the DB, and the DBA will have to setup access in an ACL.