1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How do i capture statements generated by oracle?

Discussion in 'SQL PL/SQL' started by akshayd, Aug 7, 2009.

  1. akshayd

    akshayd Guest

    hello everyone,
    i m a student of computer engineering. i am doing a project in vb 2008 on sql databse browser which will provide gui to oracle queries. i am facing a problem that i cannot catch statements generated by oracle like TABLE CREATED or TABLE ALTERED or TABLE DROPPED or 'x' ROWS DELETED etc. So can anybody help me in catching these statements in vb textbox? plz reply asap. thank you
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi, write database trigger. prereq : create one table.

    table:
    create table logcatch(tname varchar2(20), action varchar2(20),uname varchar2,event_time varchar2(30));

    database trigger:

    in order to write this trigger u need a permissions.
    sql>connect system/manager

    sql>grant administer database trigger to <username>(enter ur user name);

    sql> connect <username>/<password>

    sql> ed logcatch(hit enter enter button, notepad opens, write this script)
    create or replace trigger logtrig
    after create or alter or drop or all
    on database

    begin

    insert into logcatch(ora_dict_obj_name,ora_sysevent,user,to_char(sysdate,'dd-mon-yyyy hh:mi:ss am'));

    end logtrig;

    save and close the notepad.
    sql>@logcatch(hit enter button).

    run this script and create a trigger.
    whenever a table created or altered or dropd, the log can be seen in logcatch table.
    this trigger catches other user's data also. if u dont want that
    replace "on database" with "on schema" in the trigger script.
    hope gave gud info.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Have you tested this code?

    The table doesn't create because the username column has no length specified. The trigger code doesn't execute due to a missing '/' as the last line. The trigger doesn't create (after the table has been successfully created and the missing '/' added) because there is no 'or all' specificaton for a schema level/database level trigger and you did not specify the VALUES keyword in your insert statement. Testing your (modified) code it does capture the action but not the actual statement the orginal poster wants to preserve and it won't capture messages like '43 rows deleted.'

    Have you used such a trigger before?
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    oh thanq. syntax errors are due to because i dint use that code. i imagined and written. is the logic provide is wrong? rows delete can be capture by using sql%rowcount? am i right?
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not from this sort of trigger; % is an invalid character in this context. Additionally the trigger will need to be a table-level trigger.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    oh thanq. i thought %rowcount can be used any where. so now cleared. thanq. n one doubt is that %rowcount is implicit cursor attribute. y cant we used that in trigger?
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can use %rowcount anywhere , even in Triggers.

    What zargon says is , to serve OP's requirement, trigger has to be at table level and not at the database level.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    SQL> CREATE OR REPLACE TRIGGER rowctcapture
      2  after INSERT OR UPDATE OR DELETE
      3  ON sess$stat
      4  FOR each ROW
      5  BEGIN
      6          IF UPDATING THEN
      7                  INSERT INTO logcatch(tname, action, uname, event_time)
      8                  VALUES (ora_dict_obj_name,sql%rowcount||' row(s) updated',USER,sysdate);
      9          elsif DELETING THEN
     10                  INSERT INTO logcatch(tname, action, uname, event_time)
     11                  VALUES (ora_dict_obj_name,sql%rowcount||' row(s) deleted',USER,sysdate);
     12          ELSE
     13                  INSERT INTO logcatch(tname, action, uname, event_time)
     14                  VALUES (ora_dict_obj_name,sql%rowcount||' row(s) inserted',USER,sysdate);
     15          END IF;
     16  END;
     17  /

    Warning: TRIGGER created WITH compilation errors.

    SQL>
    SQL> SHOW errors
    Errors FOR TRIGGER ROWCTCAPTURE:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/3      PL/SQL: SQL Statement ignored
    4/32     PL/SQL: ORA-00911: invalid CHARACTER
    6/3      PL/SQL: SQL Statement ignored
    7/32     PL/SQL: ORA-00911: invalid CHARACTER
    9/3      PL/SQL: SQL Statement ignored
    10/32    PL/SQL: ORA-00911: invalid CHARACTER
    SQL>
    Please provide a working example.
     
    kiran.marla likes this.
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    k thanq. trigger is created with compilation errors. why the errors occurred. what is the possible cause?
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  11. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    when i worked on this, i tried and tried and finally tired for not getting. :)
    this i achieved with packages. any clarrified my doubt thanq raj