{"id":158,"date":"2008-11-13T09:33:00","date_gmt":"2008-11-13T11:33:00","guid":{"rendered":"http:\/\/www.soudba.com.br\/?p=158"},"modified":"2008-11-13T09:33:00","modified_gmt":"2008-11-13T11:33:00","slug":"event-attribute-functions","status":"publish","type":"post","link":"https:\/\/www.soudba.com.br\/?p=158","title":{"rendered":"Event Attribute Functions"},"content":{"rendered":"

Quando uma trigger \u00e9 disparada, pode-se retornar certos atributos sobre o evendo que causou o disparo. Cada atributo \u00e9 retornado por uma fun\u00e7\u00e3o.<\/strong><\/span><\/p>\n

As fun\u00e7\u00f5es, retiradas da documenta\u00e7\u00e3o da Oracle. Confirmado que funciona na vers\u00e3o 9.2 e n\u00e3o funciona em algumas releases do 10g por causa de bugs (metalink.oracle.com<\/a> para maiores detalhes)<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Attribute<\/strong><\/span><\/th>\n<\/a> Type<\/strong><\/span><\/th>\n<\/a> Description<\/strong><\/span><\/th>\n<\/a> Example<\/strong><\/span><\/th>\n<\/tr>\n<\/thead>\n
\n
<\/a>ora_client_ip_address
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP\/IP<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'LOGON')
<\/a>  then addr :=
ora_client_ip_address;
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_database_name
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2(50)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Database name.<\/p>\n<\/td>\n

\n
<\/a>DECLARE
<\/a> db_name VARCHAR2(50);
<\/a>BEGIN
<\/a>   db_name := ora_database_name;
<\/a>END;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_des_encrypted_password
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

The DES encrypted password of the user being created or altered.<\/p>\n<\/td>\n

\n
<\/a>IF (ora_dict_obj_type = 'USER')
<\/a> THEN INSERT INTO event_table
<\/a>(ora_des_encrypted_password);
<\/a>END IF;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_dict_obj_name
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR(30)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Name of the dictionary object on which the DDL operation occurred.<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table
('Changed object is ' ||
ora_dict_obj_name');
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_dict_obj_name_list
(name_list OUT
ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Return the list of object names of objects being modified in the event.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'ASSOCIATE
STATISTICS')
<\/a>  then number_modified :=
ora_dict_obj_name_list
(name_list);
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_dict_obj_owner
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR(30)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Owner of the dictionary object on which the DDL operation occurred.<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table ('object
owner is' ||
ora_dict_obj_owner');
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_dict_obj_owner_list(owne
r_list OUT ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns the list of object owners of objects being modified in the event.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'ASSOCIATE
STATISTICS')
<\/a>  then
number_of_modified_objects :=
ora_dict_obj_owner_list(owner_li
st);
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_dict_obj_type
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR(20)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Type of the dictionary object on which the DDL operation occurred.<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table ('This
object is a ' ||
ora_dict_obj_type);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_grantee(
<\/a>  user_list
<\/a>  OUT ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'GRANT') then
<\/a>number_of_users :=
ora_grantee(user_list);
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_instance_num
<\/pre>\n<\/td>\n
\n
<\/a>NUMBER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Instance number.<\/p>\n<\/td>\n

\n
<\/a>IF (ora_instance_num = 1)
<\/a> THEN INSERT INTO event_table
('1');
<\/a>END IF;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_is_alter_column(
column_name IN VARCHAR2)
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns true if the specified column is altered.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'ALTER' and
<\/a>ora_dict_obj_type = 'TABLE')
<\/a>  then alter_column :=
ora_is_alter_column('FOO');
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_is_creating_nested_table
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Return TRUE if the current event is creating a nested table<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'CREATE' and
ora_dict_obj_type = 'TABLE' and
ora_is_creating_nested_table)
<\/a>  then insert into event_tab
values ('A nested table is
created');
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_is_drop_column(
column_name IN VARCHAR2)
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns true if the specified column is dropped.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'ALTER' and
<\/a>ora_dict_obj_type = 'TABLE')
<\/a>  then drop_column :=
ora_is_drop_column('FOO');
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_is_servererror
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns TRUE<\/code> if given error is on error stack, FALSE<\/code> otherwise.<\/p>\n<\/td>\n

\n
<\/a>IF
(ora_is_servererror(error_number
<\/em>))
<\/a>\n THEN INSERT INTO event_table
('Server error!!');
<\/a>END IF;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_login_user
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2(30)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Login user name.<\/p>\n<\/td>\n

\n
<\/a>SELECT ora_login_user
<\/a>FROM dual;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_partition_pos
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

In an INSTEAD OF<\/code> trigger for CREATE TABLE<\/code>, the position within the SQL text where you could insert a PARTITION<\/code> clause.<\/p>\n<\/td>\n

\n
<\/a>-- Retrieve ora_sql_txt into
<\/a>-- sql_text variable first.
<\/a>
<\/a>n := ora_partition_pos;
<\/a>new_stmt :=
<\/a>substr(sql_text, 1, n-1) ||
<\/a>' ' || my_partition_clause ||
<\/a> ' ' || substr(sql_text, n));
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_privilege_list(
<\/a>privilege_list OUT
ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

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.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'GRANT' or
ora_sysevent = 'REVOKE')
<\/a>  then number_of_privileges :=
ora_privilege_list(priv_list);
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_revokee (
<\/a>user_list OUT
ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in the return value..<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'REVOKE')
then
<\/a>number_of_users :=
ora_revokee(user_list);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_server_error
<\/pre>\n<\/td>\n
\n
<\/a>NUMBER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Given a position (1 for top of stack), it returns the error number at that position on error stack<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table ('top
stack error ' ||
ora_server_error(1));
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_server_error_depth
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns the total number of error messages on the error stack.<\/p>\n<\/td>\n

\n
<\/a>n := ora_server_error_depth;
<\/a>-- This value is used with
<\/code><\/a>-- other functions such as
<\/code><\/a>-- ora_server_error
<\/code> <\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_server_error_msg
(position in binary_integer)
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Given a position (1 for top of stack), it returns the error message at that position on error stack<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table ('top
stack error message' ||
ora_server_error_msg(1));
<\/a>
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_server_error_num_params
(position in binary_integer)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

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\"<\/code>.<\/p>\n<\/td>\n

\n
<\/a>n :=
ora_server_error_num_params(1);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_server_error_param
(position in binary_integer,
param in binary_integer)
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

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.<\/p>\n<\/td>\n

\n
<\/a>-- E.g. the 2rd %s in a message
<\/a>-- like \"Expected %s, found %s\"
<\/a>param :=
ora_server_error_param(1,2);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_sql_txt (sql_text out
ora_name_list_t)
<\/pre>\n<\/td>\n
\n
<\/a>BINARY_INTEGER
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

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.<\/p>\n<\/td>\n

\n
<\/a>sql_text ora_name_list_t;
<\/a>stmt VARCHAR2(2000);
<\/a>...
<\/a>n := ora_sql_txt(sql_text);
<\/a>FOR i IN 1..n LOOP
<\/a> stmt := stmt || sql_text(i);
<\/a>END LOOP;
<\/a>INSERT INTO event_table ('text
of triggering statement: ' ||
stmt);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_sysevent
<\/pre>\n<\/td>\n
\n
<\/a>VARCHAR2(20)
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

System event firing the trigger: Event name is same as that in the syntax.<\/p>\n<\/td>\n

\n
<\/a>INSERT INTO event_table
(ora_sysevent);
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>ora_with_grant_option
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

Returns true if the privileges are granted with grant option.<\/p>\n<\/td>\n

\n
<\/a>if (ora_sysevent = 'GRANT' and
ora_with_grant_option = TRUE)
<\/a>  then insert into event_table
('with grant option');
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n
\n
<\/a>space_error_info(
<\/a>error_number OUT NUMBER,
<\/a>error_type OUT VARCHAR2,
<\/a>object_owner OUT VARCHAR2,
<\/a>table_space_name OUT
VARCHAR2,
<\/a>object_name OUT VARCHAR2,
<\/a>sub_object_name OUT
VARCHAR2)
<\/pre>\n<\/td>\n
\n
<\/a>BOOLEAN
<\/pre>\n<\/td>\n
<\/a> <\/p>\n

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.<\/p>\n<\/td>\n

\n
<\/a>if (space_error_info(eno, typ,
owner, ts, obj, subobj) = TRUE)
then
<\/a>  dbms_output.put_line('The
object ' || obj || ' owned by '
|| owner || ' has run out of
space.');
<\/a>end if;
<\/pre>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
Quando uma trigger \u00e9 disparada, pode-se retornar certos atributos sobre o evendo que causou o disparo. Cada atributo \u00e9 retornado por uma fun\u00e7\u00e3o. As fun\u00e7\u00f5es, retiradas da documenta\u00e7\u00e3o da Oracle. Confirmado que funciona na vers\u00e3o 9.2 e n\u00e3o funciona em … Continue lendo →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[99,207,320],"tags":[],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-event-attribute-functions","category-oracle-trigger-event","category-trigger-functions-event"],"_links":{"self":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=158"}],"version-history":[{"count":0,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.soudba.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}