Run PL SQL code from external file [message #304958] |
Fri, 07 March 2008 05:47  |
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 #304978 is a reply to message #304960] |
Fri, 07 March 2008 07:12   |
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   |
ThomasG
Messages: 3212 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 #305068 is a reply to message #305008] |
Sat, 08 March 2008 00:44  |
Frank
Messages: 7901 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.
|
|
|