-1

I have a stored procedure in oracle 11g containing three parameters.

Can I fetch the value of these three parameters from an excel file or any other file, eg: text file etc.

If so, then how can I do it.

Karan Gupta
  • 529
  • 2
  • 7
  • 21

1 Answers1

0

You can create an sqlloader and load the values from the file into a temporary table. Then create a trigger, which fires on insert, calling the procedure with the inserted values.

Edit: Added info about sqlloader and triggers.

TL/DR. Create a control file, which contains the format of the input file, which basicly explains to the loader how to load the contents of the file into a given table. A tigger can call your procedure using the insert event.

Gnqz
  • 3,292
  • 3
  • 25
  • 35
  • Well, I am comparatively new to oracle so it would be really helpful if you could add in a few details. Thanks, – Karan Gupta Apr 28 '17 at 09:20
  • I understood the first part of loading the data into a temp table. The second part I couldn't get. – Karan Gupta Apr 28 '17 at 09:21
  • alright I can do this with the import/export feature too right? Basically the data has to be loaded in a temp table and then whenever a row is inserted into the temp table, a trigger calls the procedure. The sql loader thing seems to be dynamic as in whenever i insert a row in the excel file, the roq gets added to my tables in oracle right? – Karan Gupta Apr 28 '17 at 09:49
  • No, the loader loads what's in the specified file once the loading is issued. Modifying the file itself will have no effect until new loading is done. – Gnqz Apr 28 '17 at 10:48
  • Yes I did it through SQL loader but the SQL loader loads all the data into the temp table everytime I run it. I want only the new data to be added in the table a d not the old one. And for that I everytime have to clear the excel file from which I'm loading so that the old data does not get appended. Any suggestions for this? – Karan Gupta Apr 29 '17 at 13:48
  • Write a batch script to spool the contents of the table into a csv file, then compare it with your excell file (also use csv for it) and then create a new file with the differences. This new file will be the one you load and then clear the excess files. – Gnqz Apr 29 '17 at 17:34
  • A little more explanation please. How do I compare the files? Manually by looking? – Karan Gupta May 01 '17 at 06:13
  • unable to compare the two files. – Karan Gupta May 01 '17 at 06:49
  • Check this question. http://stackoverflow.com/questions/13045754/bat-file-to-compare-two-text-files-and-output-the-difference. and this tutorial http://www.robvanderwoude.com/battech_redirection.php – Gnqz May 01 '17 at 12:48