Home » SQL & PL/SQL » SQL & PL/SQL » Run PL SQL code from external file
Run PL SQL code from external file [message #304958] Fri, 07 March 2008 05:47 Go to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi all,

my problem is the following:

I have a table in Oracle DB in which are included names and order of some procedures that I have to execute.
Since the order can change many times a week, to execute them I tried this solution:

Create a PL SQL procedure that
- read the table classifying procedures by their order to be
executed
- write in a .txt or similar file the PL/SQL code to execute
each single procedure read from the table
- close the file
- run the file to execute the code written.

At the moment I've not found the code the run the file yet.
The file dosn't contain only SQL instruction but also conditions, loops and so on (like any other PL SQL procedure).

Any suggestions?

Thanks to all will help
Re: Run PL SQL code from external file [message #304960 is a reply to message #304958] Fri, 07 March 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you do all this in a PL/SQL procedure instead of unloading a table doing some work to finally call PL/SQL procedures?

Regards
Michel
Re: Run PL SQL code from external file [message #304978 is a reply to message #304960] Fri, 07 March 2008 07:12 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi Michel,

The order of procedures will not be always the same.

I need a fixed procedure that executes the whole process, without knowing how the process is.

So I built the table with the procedure list giving to each a position in the execution chain; my procedure will read the chain in the correct order (getting procedure names from the table) and then execute the chain.

So I want a procedure that writes a text file in which there will be all the PL SQL code to execute procedures taken from the table.
After that, the principal procedure will execute the text file (something like execution in batch.)

I need the code for this last part...

Thank you!
Re: Run PL SQL code from external file [message #304981 is a reply to message #304978] Fri, 07 March 2008 07:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Instead of writing the procedures to a text file, you could just execute them with execute immediate at that point.

(You might to want to additionally write them into a text file or log table before executing, though, to have a way to figure out the last statement that was executed when an error occurs in the dynamic SQL.)

Re: Run PL SQL code from external file [message #304983 is a reply to message #304978] Fri, 07 March 2008 07:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
for procs in (select proc_name from my_table order by what_i_want) loop
  execute immediate 'begin '||procs.proc_name'||; end;';
end loop;

Regards
Michel

[Updated on: Fri, 07 March 2008 07:25]

Report message to a moderator

Re: Run PL SQL code from external file [message #304994 is a reply to message #304983] Fri, 07 March 2008 07:55 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi Michel,

Thank you for your suggestions.

Only one last question:

is it possible to execute a batch file with PL/SQL code from a PL/SQL procedure?

How?

Thanks
Re: Run PL SQL code from external file [message #304997 is a reply to message #304994] Fri, 07 March 2008 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:952229840241

Regards
Michel
Re: Run PL SQL code from external file [message #305008 is a reply to message #304958] Fri, 07 March 2008 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
What a wonderful mechanism to support & facilitate SQL injection!
Re: Run PL SQL code from external file [message #305068 is a reply to message #305008] Sat, 08 March 2008 00:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
anacedent wrote on Fri, 07 March 2008 15:46
What a wonderful mechanism to support & facilitate SQL injection!

Not sure if you're referring the askTom post, but if you mean the general stuff discussed in this thread, then I disagree.
It is not harder to protect the contents of the table than it is to protect the contents of the pl/sql-code.
This does not mean that I get anything close to a joyful feeling, seeing this..
I think that if you have a bunch of procedures and functions, and you have to execute them in different order, depending on your data, chances are that the design of your code could do with some reviewing.
Previous Topic: Query help
Next Topic: How to List all parameters in a procedure
Goto Forum:
  


Current Time: Fri Dec 09 00:17:25 CST 2016

Total time taken to generate the page: 0.05283 seconds