Oracle Triggers Query..

Question

Lets consider a Table STUD and a ROW-Level TRIGGER is implemented over INSERT query.. My scenario goes like this, whenever a row is inserted, a trigger is fired and it should access some script file which is placed in the hard disk, and ultimately should print the result. So, is this thing is possible? and if yes, then this thing should exist in dynamic form, i.e. if we change the content of script file, then the oracle should reflect those changes as well.

I have tried doing this for java using External Procedures, but doesn't feel that much satisfied with the result that i wanted.

Kindly give your point-of-view for this kind of scenario and ways that this can be implemented.

Answer 1

A community wiki for reasons why this is a bad idea.

Reasons why using a file for dynamic code is dumb.

1. Each row being inserted has to open a file, read the entire contents, parse it and 'do something' with the result. That is going to be SLOW.

2. Depending on the OS environment, you may not be able to concurrently open the file for reading and writing. So you may find you have to shutdown the database to 'promote' code changes into the file.

3. Depending on the OS environment, you may even find that only one session can read from the file at a time.

4. Maybe the trigger will read the file in the middle of a 'save' and execute partial code.

5. File security will be totally separate from database security, creating a maintenance headache.

In short, storing the dynamic code in a table, rather than a file, would be a MASSIVE improvement.

Reasons why you shouldn't use dynamic code anyway:

1. Dynamic code hasn't been parsed/compiled. So it may not work and you don't find out until it gets executed.

2. Static code performs better than dynamic code because of reduced parsing overhead.

Misc. Reasons

1. Having a row level trigger read and execute the code means that, potentially, the same statement inserting multiple records into the table may pick up different versions of code to execute for different row insertions.

Answer 2

What if I wanted to access a sh file, which is doing some OS processing

There are three ways of executing OS from inside the database.

1. External procedures, which are PL/SQL wrappers to OS libraries written in C or Java. Available since Oracle 8.0. Find out more.

2. A Java Stored Procedure emulating a host command. This is a hand-rolled wrapper of a Java Runtime. This command will execute with the powerful privileges of the oracle OS user, so do not expose this procedure for general use. Instead have a secure user own it, and write stored procedures to expose specific slivers of OS functionality (e.g. a procedure to execute mkdir, a function to execute ls). Possible since Oracle 8i. Find out more.

3. DBMS_SCHEDULER. As well as background database jobs we can use the scheduled to run OS jobs. Available since Oracle 10g. Find out more

Of these three options, DBMS_SCHEDULER is probably the most secure option. As the name suggests it is intended to run programs on a timed basis (like unix cron), so it is not suitable for running things on demand (say through a trigger). However, it may be that your business logic can be satisfied without the need for a trigger. External procedures are not intended for running shell scripts.

External programs, being external, require more effort to co-ordinate with database components. This is applies when releasing new versions of the application, and doing back-ups. They can also be troublesome when (say) the development environment and the live environments are on different platforms.

In is a very bad idea to wrestle the database into executing arbitrary code. In most scenarios all that is needed is a mechanism for passing parameters to the executable.