oracle 系統事件大全

season0891發表於2009-11-27

Event Attribute Functions

When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call.

Notes:

  • To make these attributes available, you must first run the CATPROC.SQL script.
  • The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.
  • In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

    Table 16-1 System Defined Event Attributes  
    Attribute Type Description Example
    ora_client_ip_address
    VARCHAR2

    Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP

    if (ora_sysevent = 'LOGON')
    then addr :=
    ora_client_ip_address;
    end if;
    ora_database_name
    VARCHAR2(50)

    Database name.

    DECLARE
    db_name VARCHAR2(50);
    BEGIN
    db_name := ora_database_name;
    END;
    ora_des_encrypted_password
    VARCHAR2

    The DES encrypted password of the user being created or altered.

    IF (ora_dict_obj_type = 'USER')
    THEN INSERT INTO event_table
    (ora_des_encrypted_password);
    END IF;
    ora_dict_obj_name
    VARCHAR(30)

    Name of the dictionary object on which the DDL operation occurred.

    INSERT INTO event_table 
    ('Changed object is ' ||
    ora_dict_obj_name');
    ora_dict_obj_name_list 
    (name_list OUT
    ora_name_list_t)
    BINARY_INTEGER

    Return the list of object names of objects being modified in the event.

    if (ora_sysevent = 'ASSOCIATE 
    STATISTICS')
    then number_modified :=
    ora_dict_obj_name_list
    (name_list);
    end if;
    ora_dict_obj_owner
    VARCHAR(30)

    Owner of the dictionary object on which the DDL operation occurred.

    INSERT INTO event_table ('object 
    owner is' ||
    ora_dict_obj_owner');
    ora_dict_obj_owner_list(owne
    r_list OUT ora_name_list_t)
    BINARY_INTEGER

    Returns the list of object owners of objects being modified in the event.

    if (ora_sysevent = 'ASSOCIATE 
    STATISTICS')
    then
    number_of_modified_objects :=
    ora_dict_obj_owner_list(owner_li
    st);
    end if;
    ora_dict_obj_type
    VARCHAR(20)

    Type of the dictionary object on which the DDL operation occurred.

    INSERT INTO event_table ('This 
    object is a ' ||
    ora_dict_obj_type);
    ora_grantee(
    user_list
    OUT ora_name_list_t)
    BINARY_INTEGER

    Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.

    if (ora_sysevent = 'GRANT') then
    number_of_users :=
    ora_grantee(user_list);
    end if;
    ora_instance_num
    NUMBER

    Instance number.

    IF (ora_instance_num = 1) 
    THEN INSERT INTO event_table
    ('1');
    END IF;
    ora_is_alter_column( 
    column_name IN VARCHAR2)
    BOOLEAN

    Returns true if the specified column is altered.

    if (ora_sysevent = 'ALTER' and
    ora_dict_obj_type = 'TABLE')
    then alter_column :=
    ora_is_alter_column('FOO');
    end if;
    ora_is_creating_nested_table
    BOOLEAN

    Return TRUE if the current event is creating a nested table

    if (ora_sysevent = 'CREATE' and 
    ora_dict_obj_type = 'TABLE' and
    ora_is_creating_nested_table)
    then insert into event_tab
    values ('A nested table is
    created');
    end if;
    ora_is_drop_column( 
    column_name IN VARCHAR2)
    BOOLEAN

    Returns true if the specified column is dropped.

    if (ora_sysevent = 'ALTER' and
    ora_dict_obj_type = 'TABLE')
    then drop_column :=
    ora_is_drop_column('FOO');
    end if;
    ora_is_servererror
    BOOLEAN

    Returns TRUE if given error is on error stack, FALSE otherwise.

    IF 
    (ora_is_servererror(error_number
    ))
    THEN INSERT INTO event_table
    ('Server error!!');
    END IF;
    ora_login_user
    VARCHAR2(30)

    Login user name.

    SELECT ora_login_user 
    FROM dual;
    ora_partition_pos
    BINARY_INTEGER

    In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you could insert a PARTITION clause.

    -- Retrieve ora_sql_txt into
    -- sql_text variable first.

    n := ora_partition_pos;
    new_stmt :=
    substr(sql_text, 1, n-1) ||
    ' ' || my_partition_clause ||
    ' ' || substr(sql_text, n));
    ora_privilege_list(
    privilege_list OUT
    ora_name_list_t)
    BINARY_INTEGER

    Returns the list of privileges being granted by the grantee or the list of privileges revoked from the revokee in the OUT parameter; returns the number of privileges in the return value.

    if (ora_sysevent = 'GRANT' or 
    ora_sysevent = 'REVOKE')
    then number_of_privileges :=
    ora_privilege_list(priv_list);
    end if;
    ora_revokee (
    user_list OUT
    ora_name_list_t)
    BINARY_INTEGER

    Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in the return value..

    if (ora_sysevent = 'REVOKE') 
    then
    number_of_users :=
    ora_revokee(user_list);
    ora_server_error
    NUMBER

    Given a position (1 for top of stack), it returns the error number at that position on error stack

    INSERT INTO event_table ('top 
    stack error ' ||
    ora_server_error(1));
    ora_server_error_depth
    BINARY_INTEGER

    Returns the total number of error messages on the error stack.

    n := ora_server_error_depth;
    -- This value is used with
    -- other functions such as
    -- ora_server_error

    ora_server_error_msg 
    (position in binary_integer)
    VARCHAR2

    Given a position (1 for top of stack), it returns the error message at that position on error stack

    INSERT INTO event_table ('top 
    stack error message' ||
    ora_server_error_msg(1));

    ora_server_error_num_params 
    (position in binary_integer)
    BINARY_INTEGER

    Given a position (1 for top of stack), it returns the number of strings that have been substituted into the error message using a format like "%s".

    n := 
    ora_server_error_num_params(1);
    ora_server_error_param 
    (position in binary_integer,
    param in binary_integer)
    VARCHAR2

    Given a position (1 for top of stack) and a parameter number, returns the matching "%s", "%d", and so on substitution value in the error message.

    -- E.g. the 2rd %s in a message
    -- like "Expected %s, found %s"
    param :=
    ora_server_error_param(1,2);
    ora_sql_txt (sql_text out 
    ora_name_list_t)
    BINARY_INTEGER

    Returns the SQL text of the triggering statement in the OUT parameter. If the statement is long, it is broken up into multiple PL/SQL table elements. The function return value specifies how many elements are in the PL/SQL table.

    sql_text ora_name_list_t;
    stmt VARCHAR2(2000);
    ...
    n := ora_sql_txt(sql_text);
    FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
    END LOOP;
    INSERT INTO event_table ('text
    of triggering statement: ' ||
    stmt);
    ora_sysevent
    VARCHAR2(20)

    System event firing the trigger: Event name is same as that in the syntax.

    INSERT INTO event_table 
    (ora_sysevent);
    ora_with_grant_option
    BOOLEAN

    Returns true if the privileges are granted with grant option.

    if (ora_sysevent = 'GRANT' and 
    ora_with_grant_option = TRUE)
    then insert into event_table
    ('with grant option');
    end if;
    space_error_info(
    error_number OUT NUMBER,
    error_type OUT VARCHAR2,
    object_owner OUT VARCHAR2,
    table_space_name OUT
    VARCHAR2,
    object_name OUT VARCHAR2,
    sub_object_name OUT
    VARCHAR2)
    BOOLEAN

    Returns true if the error is related to an out-of-space condition, and fills in the OUT parameters with information about the object that caused the error.

    if (space_error_info(eno, typ, 
    owner, ts, obj, subobj) = TRUE)
    then
    dbms_output.put_line('The
    object ' || obj || ' owned by '
    || owner || ' has run out of
    space.');
    end if;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/90618/viewspace-620845/,如需轉載,請註明出處,否則將追究法律責任。

相關文章