Oracle PL/SQL/System Packages/ORA
Содержание
- 1 ORA_CLIENT_IP_ADDRESS returns the client IP address as a VARCHAR2 datatype.
- 2 ORA_DES_ENCRYPTED_PASSWORD returns the DES-encrypted password as a VARCHAR2 datatype
- 3 ORA_DICT_OBJ_NAME_LIST: list of object names touched by the triggering event.
- 4 ORA_DICT_OBJ_NAME returns an object name as a VARCHAR2 datatype
- 5 ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.
- 6 ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event as a VARCHAR2 datatype.
- 7 ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a VARCHAR2 datatype.
- 8 ORA_GRANTEE returns the number of elements in the list indexed by a PLS_INTEGER datatype.
- 9 ORA_INSTANCE_NUM returns the current database instance number as a NUMBER datatype.
- 10 ORA_IS_ALTER_COLUMN returns a true or false value as a BOOLEAN datatype
- 11 ORA_IS_CREATING_NESTED_TABLE returns a true or false value as a BOOLEAN datatype when you create a table with a nested table.
- 12 ORA_LOGIN_USER function returns the current schema name as a VARCHAR2 datatype.
- 13 ORA_PRIVILEGE_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.
- 14 ORA_SERVER_ERROR_MSG returns an error message text as a VARCHAR2 datatype.
- 15 ORA_SERVER_ERROR returns an error number as a NUMBER datatype.
- 16 ORA_SYSEVENT function returns the system event that was responsible for firing the trigger as a VARCHAR2 datatype.
- 17 ORA_WITH_GRANT_OPTION returns a true or false value as a BOOLEAN datatype.
- 18 The ORA_IS_SERVERERROR returns a true or false value as a BOOLEAN datatype when the error is on the error stack.
ORA_CLIENT_IP_ADDRESS returns the client IP address as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 ip_address VARCHAR2(11); 3 BEGIN 4 IF ora_sysevent = "LOGON" THEN 5 ip_address := ora_client_ip_address; 6 dbms_output.put_line("ip_address:"||ip_address); 7 8 END IF; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_DES_ENCRYPTED_PASSWORD returns the DES-encrypted password as a VARCHAR2 datatype
<source lang="sql">
SQL> SQL> DECLARE
2 password VARCHAR2(60); 3 BEGIN 4 IF ora_dict_obj_type = "USER" THEN 5 password := ora_des_encrypted_password; 6 dbms_output.put_line("password:"||password); 7 END IF; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL>
</source>
ORA_DICT_OBJ_NAME_LIST: list of object names touched by the triggering event.
<source lang="sql">
SQL> SQL> DECLARE
2 name_list DBMS_STANDARD.ORA_NAME_LIST_T; 3 counter PLS_INTEGER; 4 BEGIN 5 IF ora_sysevent = "ASSOCIATE_STATISTICS" THEN 6 counter := ora_dict_obj_name_list(name_list); 7 dbms_output.put_line("counter:"||counter); 8 END IF; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_DICT_OBJ_NAME returns an object name as a VARCHAR2 datatype
<source lang="sql">
SQL> SQL> DECLARE
2 database VARCHAR2(50); 3 BEGIN 4 database := ORA_DICT_OBJ_NAME; 5 dbms_output.put_line("database:"||database); 6 END; 7 /
database: PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 owner_list DBMS_STANDARD.ORA_NAME_LIST_T; 3 counter PLS_INTEGER; 4 BEGIN 5 IF ora_sysevent = "ASSOCIATE_STATISTICS" THEN 6 counter := ora_dict_obj_owner_list(owner_list); 7 dbms_output.put_line("counter:"||counter); 8 END IF; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 owner VARCHAR2(30); 3 BEGIN 4 owner := ora_dict_obj_owner; 5 dbms_output.put_line("owner:"||owner); 6 END; 7 /
owner: PL/SQL procedure successfully completed. SQL>
</source>
ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 type1 VARCHAR2(19); 3 BEGIN 4 type1 := ora_dict_obj_type; 5 dbms_output.put_line("type1:"||type1); 6 END; 7 /
type1: PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_GRANTEE returns the number of elements in the list indexed by a PLS_INTEGER datatype.
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 user_list DBMS_STANDARD.ORA_NAME_LIST_T; 3 counter PLS_INTEGER; 4 BEGIN 5 IF ora_sysevent = "GRANT" THEN 6 counter := ora_grantee(user_list); 7 dbms_output.put_line("counter:"||counter); 8 9 END IF; 10 END; 11 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_INSTANCE_NUM returns the current database instance number as a NUMBER datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 instance NUMBER; 3 BEGIN 4 instance := ora_instance_num; 5 dbms_output.put_line("instance:"||instance); 6 END; 7 /
instance:1 PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_IS_ALTER_COLUMN returns a true or false value as a BOOLEAN datatype
<source lang="sql">
SQL> SQL>
</source>
ORA_IS_CREATING_NESTED_TABLE returns a true or false value as a BOOLEAN datatype when you create a table with a nested table.
<source lang="sql">
SQL> SQL> BEGIN
2 IF ora_sysevent = "CREATE" AND ora_dict_obj_type = "TABLE" AND ora_is_creating_nested_table THEN 3 dbms_output.put_line(ora_dict_obj_name); 4 END IF; 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_LOGIN_USER function returns the current schema name as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> SQL> BEGIN
2 dbms_output.put_line(ora_login_user||" is the current user."); 3 END; 4 /
sqle is the current user. PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
ORA_PRIVILEGE_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 priv_list DBMS_STANDARD.ORA_NAME_LIST_T; 3 counter PLS_INTEGER; 4 BEGIN 5 IF ora_sysevent = "GRANT" OR ora_sysevent = "REVOKE" THEN 6 counter := ora_privilege_list(priv_list); 7 dbms_output.put_line("counter:"||counter); 8 END IF; 9 END; 10 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_SERVER_ERROR_MSG returns an error message text as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 error VARCHAR2(64); 3 BEGIN 4 FOR i IN 1..ora_server_error_depth LOOP 5 error := ora_server_error_msg(i); 6 dbms_output.put_line("error:"||error); 7 END LOOP; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_SERVER_ERROR returns an error number as a NUMBER datatype.
<source lang="sql">
SQL> SQL> DECLARE
2 error NUMBER; 3 BEGIN 4 FOR i IN 1..ora_server_error_depth LOOP 5 error := ora_server_error(i); 6 dbms_output.put_line("error:"||error); 7 END LOOP; 8 END; 9 /
PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
ORA_SYSEVENT function returns the system event that was responsible for firing the trigger as a VARCHAR2 datatype.
<source lang="sql">
SQL> SQL> SQL> BEGIN
2 dbms_output.put_line(ora_sysevent||" fired the trigger."); 3 END; 4 /
fired the trigger. PL/SQL procedure successfully completed. SQL> SQL>
</source>
ORA_WITH_GRANT_OPTION returns a true or false value as a BOOLEAN datatype.
<source lang="sql">
SQL> SQL> SQL> BEGIN
2 IF ora_with_grant_option THEN 3 dbms_output.put_line("ORA-04082 error thrown."); 4 END IF; 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL> SQL>
</source>
The ORA_IS_SERVERERROR returns a true or false value as a BOOLEAN datatype when the error is on the error stack.
<source lang="sql">
SQL> SQL> SQL> BEGIN
2 IF ora_is_servererror(4082) THEN 3 dbms_output.put_line("ORA-04082 error thrown."); 4 END IF; 5 END; 6 /
PL/SQL procedure successfully completed. SQL>
</source>