Home » SQL & PL/SQL » SQL & PL/SQL » Need Design input to execute store proc (ORACLE 11g)
Need Design input to execute store proc [message #614929] Wed, 28 May 2014 16:20 Go to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Hello All,

I have store proc written where it will take single VARCHAR as input. I have 1000 inputs so i need to pass each one to stored proc to execute 1000 times.

Question is how can i pass 1000 inputs and execute the store proc. What is the best design to implement.


thanks
Re: Need Design input to execute store proc [message #614931 is a reply to message #614929] Wed, 28 May 2014 16:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
put the strings in a file & then use EXTERNAL TABLE to access them.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: Need Design input to execute store proc [message #614934 is a reply to message #614931] Wed, 28 May 2014 16:51 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Thanks BS for your response

My stored proc has following Delete statment it will take input from file and popluate the fac_alias_id value. i will populate all 1000 inputs in txt file. How does EXTERNAL will take 1000 entries in file and pass it to stored proc , do i have to put loop to execute 1000 times. Could you please provied few more inputs


DELETE FROM FACILITY WHERE FACILITY_ID = (SELECT FAC_ID FROM FAC where fac_alias_id = '9401') ;

Re: Need Design input to execute store proc [message #614936 is a reply to message #614934] Wed, 28 May 2014 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
There are multiple ways to complete this task.
1) use any text editor to manipulate the 1000 lines in the file so that each contain a single DELETE statement.
2) use EXECUTE IMMEDIATE to compose or construct each DELETE statement
3) place DELETE statement inside its own single line stored procedure & pass in the value as a bind variable.

the last two will require some sort of LOOP
Re: Need Design input to execute store proc [message #614941 is a reply to message #614934] Thu, 29 May 2014 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
My stored proc has following Delete statment it will take input from file and popluate the fac_alias_id value. i will populate all 1000 inputs in txt file. How does EXTERNAL will take 1000 entries in file


If 9401 (why quotes?) is from the file then you can use something like:

DELETE FROM FACILITY 
WHERE FACILITY_ID = 
      (SELECT FAC_ID FROM FAC 
       where fac_alias_id IN (SELECT the_value_from_file FROM external_table))
/

No need of stored procedure or PL/SQL, if this is the only thing that your stored procedure does.

Re: Need Design input to execute store proc [message #615045 is a reply to message #614941] Thu, 29 May 2014 13:28 Go to previous messageGo to next message
gorants
Messages: 85
Registered: May 2014
Location: ATL
Member
Michel

Will this query get values from configured flat file, let say if file has 1..100 it will return all of the values.

SELECT the_value_from_file FROM external_table


Thanks
Re: Need Design input to execute store proc [message #615048 is a reply to message #615045] Thu, 29 May 2014 13:50 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.

Previous Topic: Delete getting stuck
Next Topic: Using SQL in pass through query in Access to Oracle
Goto Forum:
  


Current Time: Thu Mar 28 18:42:46 CDT 2024