Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Mr. Muskrat (4572)

Mr. Muskrat
  reversethis-{moc ... ta} {tarksum.rm}

I'm married with 2 girls. I work as a full time Perl programmer for a Land Mobile Radio company in the Dallas/Fort Worth area.

I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.

My other blog [blogspot.com]

Journal of Mr. Muskrat (4572)

Tuesday September 05, 2006
10:00 AM

Oracle Dynamic SQL File Execution

[ #30895 ]

We want to be able to perform some inserts to other tables and apply some grants if the criteria in an update trigger are met. Once met, the criteria will never change. The grants and inserts may change over time and other files may be added. I find myself in a position where the most logical (to me anyway) solution seems to be one in which I can dynamically run an SQL file.

Here's my pseudocode of how I envision it should work.

CREATE OR REPLACE TRIGGER au_some_tbl
  AFTER UPDATE ON some_tbl
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
    v_file VARCHAR2(1024); -- excessively large, I know
BEGIN
    IF (:NEW.enabled = 1 AND :OLD.enabled = 0) THEN
        v_file := '/path/to/sql/files/' || :NEW.name || '.sql';
        @@v_file; -- somehow execute the sql file
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

So let's say I have a "function" named say_foo in some_tbl and enabled is set to 1 for that row. The trigger should execute the SQL contained in /path/to/sql/files/say_foo.sql.

The problem is that there doesn't appear to be a way to do this. It looks like I have to add a case or a series of if statements to get it work; it also means that the trigger will have to be edited any time we need a new "function" added. I hope that either I am wrong and it can be done or that there is a cleaner solution. Does any one have any ideas?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.