Can anyone tell abt Execute Immediate
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Can anyone tell abt Execute Immediate

  1. #1
    Join Date
    Oct 2000
    Posts
    3

    Angry

    Hi ,
    I created a database trigger. In that i am dynamically inserting the values.

    the questions is i am using EXECUTE IMMEDIATE.

    when i am executing the database trigger, data is not inserting into the corresponding table.

    can anyone help me for this issue..

    i am using Oracle8i under HPunix 10.2.

    I copied my coding here..

    Jgun..

    CREATE OR REPLACE TRIGGER T_DYN AFTER
    INSERT ON EMP FOR EACH ROW
    DECLARE
    CURSOR C1 IS
    SELECT COLUMN_NAME
    FROM SYS.USER_TAB_COLUMNS
    WHERE TABLE_NAME = 'EMP';
    v_ins_col VARCHAR2(100);
    v_ins_val VARCHAR2(100);
    v_ins_col_st VARCHAR2(100);
    v_ins_val_st VARCHAR2(100);
    v_column_name VARCHAR2(100);
    BEGIN
    v_ins_col := ' ';
    v_ins_val := ' ';
    v_ins_col_st := 'INSERT INTO AUDIT_EMP(';
    v_ins_val_st := 'VALUES(';
    OPEN C1;
    WHILE 1=1 LOOP
    FETCH C1 INTO v_column_name;
    IF (C1%NOTFOUND) THEN
    EXIT;
    ELSE
    IF (v_ins_col != ' ') THEN
    v_ins_col := v_ins_col || ',';
    END IF;
    IF (v_ins_val != ' ') THEN
    v_ins_val := v_ins_val || ',';
    END IF;
    END IF;
    v_ins_col := v_ins_col || v_column_name;
    v_ins_val := v_ins_val ||':new.'|| v_column_name;
    END LOOP;
    CLOSE C1;
    v_ins_col := v_ins_col_st ||v_ins_col || ')';
    v_ins_val := v_ins_val_st||v_ins_val || ')';
    v_ins_col := v_ins_col ||v_ins_val;
    EXECUTE IMMEDIATE v_ins_col;
    EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE);
    END;
    /

  2. #2
    Join Date
    Feb 2001
    Posts
    125

    Execute Immediate

    Dear Friend,

    There are some mistakes in you trigger;

    please first replace dbms_output.put_line(sqlcode)
    with
    Raise_Application_Error(-20001,sqlcode)

    now recompile you trigger.
    insert a row into emp table
    you will get error messages
    it will also error line no where your Execute Immediate
    command is given.


    your are missing "Using" clause in Execute Immediate command.
    In youor script, you are not getting any error regarding Excetion because you using Dbms_Output.Put_line command
    which does not work for trigger. so use Raise_Application_Error() instead.

    better use of Execute Immediate command is for
    dynamic SQl statement

    use can use this command in procedure to create a table.
    see the example


    SQL>DECLARE
    BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE T1 (T1 NUMBER)';
    END;

    PL/SQL procedure successfully completed.

    SQL> DESC T1;
    Name Null? Type
    ------------------------------- -------- ----
    T1 NUMBER





    Thanks
    Pitamber Soni


  3. #3
    Join Date
    Oct 2000
    Posts
    3
    Thanx soni..

    Jgun.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width