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!

Reading &parameters from text file

Discussion in 'SQL PL/SQL' started by rudolf_r, Jun 10, 2014.

  1. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Good day All,

    I need to run a select query on a monthly, two-weekly scheduled basis with minimum involvement from the user executing the SQL report. The file specifies the areas and formats for which the reports should be generated. The first line in the file contain column headings. Data starts at line two.
    TownName,Subburb,pdf,xls
    New York,Brooklyn,pdf,xls
    Windhoek,Windhoek-West,pdf,xls

    Any ideas on how I could get this done will be appreciated.

    Many Thanks
    Rudolf
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If you want this largely automated for users, I highly recommend using external tables. I have several files that my users must upload on a periodic basis. I create an external table definition pointing to a set directory & file (that does not skip line one). I then have a load page (mine is generated in Application Express, but the interface is not important) that tells the user to save the file as [filename].csv in 'x' location and then click the 'Load' button to load the file.

    When the load button is pressed, I have a PL/SQL procedure that checks line one of that table to make sure the column names are what I expect, in the order I expect (i.e. 'TownName','Suburb','pdf','xls'). If they are not, I return an error to the user about the file format problem. If the columns *are* in the right order, I execute a cursor FOR loop to read the records (skipping line one).

    In my case, I'm normally inserting the records into the table. In yours, I imagine the cursor FOR loop would call a second 'reporting' procedure and pass it the values from each line of the file.