#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0

    PL/SQL procedures and triggers


    I have to create an empty file in one of the directories on unix server. This event should be triggered by a trigger attached to one table. So whenever there is an insert or update to that table it should invoke the trigger which should inturn create the blank file.
    Can anyone help me out.... i am not very comfortable in PL/SQL.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    One option:
    Create a java method that creates a directory.
    Load this java file as a Java Stored Procedure
    Create an ON UPDATE INSERT TRIGGER on this table that calls the java stored procedure passing the name of the new directory.
    ...
    Another option using Oracle Pipes is discussed here:
    http://www.oreview.com/9704flet.htm
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0
    Hi Victor,

    That is how its currently setup in my project except for that the directory is hardcoded in the java program. For some reason the java program is throwing an exception :

    ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException

    ORA-04088: error during execution of trigger 'MAPPER.INSUPDACCTMONTHTRG'



    Also can you explain how a compiled java class is loaded into oracle installed on a unix server.
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    Still you have another option to do so. look at the following code:

    DECALRE
    v_file UTL_FILE.FILE_TYPE;
    v_filename varchar2(40):='emp.log';
    v_locat varchar2(200):='/orant/bin';
    BEGIN
    if utl_file.is_open(v_file) then
    dbms_output.put_line('file opened');
    else
    dbms_output.put_line('file closed');
    end if;
    v_file:=SYS.UTL_FILE.FOPEN(v_locat,v_filename,'w');
    UTL_FILE.FCLOSE(v_file);
    dbms_output.put_line('File has been created');
    EXCEPTION
    when UTL_FILE.invalid_path then
    raise_application_error(-2000, '****Exception caught : Invalid path defined');
    when UTL_FILE.invalid_mode then
    raise_application_error(-2001, '****Exception caught : Invalid mode');
    END;
    /

    Before run the above code, Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. For example:

    UTL_FILE_DIR = <directory name>

    OR

    UTL_FILE_DIR = *

    '*' has a special meaning this entry turns off directory access checking, and it makes any directory accessible to the UTL_FILE functions.

    Regards
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    3
    Rep Power
    0
    Hi Shafique,

    I tried you suggestions and its working when i try to update the table in sqlplus.
    However the same setup when tried through the toad tool or a GUI running on windows gives me the following error:

    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at "SYS.UTL_FILE", line 101
    ORA-06512: at "SYS.UTL_FILE", line 157
    ORA-06512: at "ITSTMAPPER.TESTPROC", line 12
    ORA-06512: at "ITSTMAPPER.TESTCTRIG", line 1
    ORA-04088: error during execution of trigger 'ITSTMAPPER.TESTCTRIG'

    Can you help me out.




    Here is my procedure code:

    CREATE OR REPLACE PROCEDURE testproc
    IS
    v_file UTL_FILE.FILE_TYPE;
    v_filename varchar2(40):='emp.log';
    v_locat varchar2(200):='/var/opt/intf/data/stage';
    BEGIN
    if UTL_FILE.is_open(v_file) then
    dbms_output.put_line('file opened');
    else
    dbms_output.put_line('file closed');
    end if;
    v_file:=SYS.UTL_FILE.FOPEN(v_locat,v_filename,'w');
    UTL_FILE.FCLOSE(v_file);
    dbms_output.put_line('File has been created');
    EXCEPTION
    when UTL_FILE.invalid_path then
    raise_application_error(-20000, '****Exception caught : Invalid path defined');
    when UTL_FILE.invalid_mode then
    raise_application_error(-20001, '****Exception caught : Invalid mode');
    END;
    /




    Here is my trigger code:

    CREATE OR REPLACE TRIGGER testctrig
    AFTER UPDATE OR INSERT ON ControlData
    FOR EACH ROW
    CALL testproc
    ;

IMN logo majestic logo threadwatch logo seochat tools logo