Oracle PL/SQL Tutorial/PL SQL Programming/ORA

ORA_CLIENT_IP_ADDRESS returns the client IP address as a VARCHAR2 datatype.

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>

ORA_DES_ENCRYPTED_PASSWORD returns the DES-encrypted password as a VARCHAR2 datatype

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>

ORA_DICT_OBJ_NAME_LIST: list of object names touched by the triggering event.

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>

ORA_DICT_OBJ_NAME returns an object name as a VARCHAR2 datatype

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>

ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.

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>

ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event as a VARCHAR2 datatype.

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>

ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a VARCHAR2 datatype.

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>

ORA_GRANTEE returns the number of elements in the list indexed by a PLS_INTEGER datatype.

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>

ORA_INSTANCE_NUM returns the current database instance number as a NUMBER datatype.

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>

ORA_IS_ALTER_COLUMN returns a true or false value as a BOOLEAN datatype

SQL>
SQL>

ORA_IS_CREATING_NESTED_TABLE returns a true or false value as a BOOLEAN datatype when you create a table with a nested table.

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>

ORA_LOGIN_USER function returns the current schema name as a VARCHAR2 datatype.

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>

ORA_PRIVILEGE_LIST returns the number of elements in the list indexed by a PLS_INTEGER datatype.

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>

ORA_SERVER_ERROR_MSG returns an error message text as a VARCHAR2 datatype.

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>

ORA_SERVER_ERROR returns an error number as a NUMBER datatype.

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>

ORA_SYSEVENT function returns the system event that was responsible for firing the trigger as a VARCHAR2 datatype.

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>

ORA_WITH_GRANT_OPTION returns a true or false value as a BOOLEAN datatype.

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>