Oracle PL/SQL/System Packages/ORA

Материал из SQL эксперт
Перейти к: навигация, поиск

Содержание

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>