ESTADISTICAS SCRIPT DBMS_APPLICATION_INFO

CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_TABLAS(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_Tablas IS
  SELECT owner,table_name
  FROM dba_tables
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Tablas';
dbms_application_info.set_module(modulo, accion);
  --se recorren las tablas del esquema
  FOR i IN get_tablas loop
    --se prepara la sentencia SQL en la cual se hace el conteo de los registros y se inserta en la tabla de auditoria(aud_registros)
    sentencia:=' BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => ''' || esquema || ''', TABNAME => ''' || i.table_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE SP_ESTADISTICAS_INDICES(esquema varchar2) AS
sentencia VARCHAR2(500);
modulo VARCHAR2(64);
accion VARCHAR2(64);
--Cursor con todas las tablas del esquema
CURSOR get_indices IS
  SELECT owner,index_name
  FROM dba_indexes
  WHERE owner=esquema;
BEGIN
modulo := 'Administracion DBA';
accion := 'Estadisticas Indices';
dbms_application_info.set_module(modulo, accion);
  --se recorren los indices del esquema
  FOR i IN get_indices loop
    sentencia:=' BEGIN DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => ''' || esquema || ''', INDNAME => ''' || i.index_name || ''' ); END;';
    EXECUTE IMMEDIATE(sentencia);
    dbms_application_info.read_module(modulo, accion);
  END LOOP;
END;
/

Como Ver Set de Caracteres Oracle Database

How to check character set in Oracle

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

SELECT * FROM NLS_DATABASE_PARAMETERS

DBMS_SERVER_ALERT

Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)

This article describes how to set tablespace thresholds using the DBMS_SERVER_ALERTpackage as an early warning mechanism for space issues.

• Introduction
• Setting Thresholds
• Displaying Thresholds
• Scripts

Introduction

Oracle allows you to set tablespace thresholds using the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).

Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAMEparameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.

There are two types of tablespace thresholds that can be set.

• TABLESPACE_PCT_FULL : Percent full. When the warning or critical threshold based on percent full is crossed a notification occurs.
• TABLESPACE_BYT_FREE : Free Space Remaining (KB). The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example Cloud Control displays and sets these values in MB.

The thresholds are set using a value and an operator.

• OPERATOR_LE : Less than or equal.
• OPERATOR_GE : Greater than or equal.

Setting Thresholds

 Make a note of your existing thresholds before changing them, so you know what to set them back to.

The following examples show how to set the different types of alerts.

BEGIN
  -- Database-wide KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
    
  -- Database-wide percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '85',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '97',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);

  -- Tablespace-specific KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '90',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '98',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
END;
/

Setting the warning and critical levels to '0' disables the notification.

Displaying Thresholds

The threshold settings can be displayed using the DBA_THRESHOLDS view.

SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

TABLESPACE_NAME                METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ ------------------------------ ------------ ------------------------------ ------------ ---------------
TEMP                           Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS1                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
UNDOTBS2                       Tablespace Space Usage         DO NOT CHECK    DO_NOT_CHECK 0
USERS                          Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          Tablespace Space Usage         GE           90                             GE           98
                               Tablespace Space Usage         GE           85                             GE           97
                               Tablespace Bytes Space Usage   LE           1024000                        LE           102400

7 rows selected.

SQL>

Oracle 11g Release 2 introduced the DBA_TABLESPACE_THRESHOLDS view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.

SET LINESIZE 200

COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT tablespace_name,
       contents,
       extent_management,
       threshold_type,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_tablespace_thresholds
ORDER BY tablespace_name;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN THRESHOL METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
TEMP                           TEMPORARY LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
UNDOTBS1                       UNDO      LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK    DO NOT CHECK 0
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Space Usage         GE           90                             GE           98

10 rows selected.

SQL>

TRIGGER DDL_LOG

CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(64),
attempt_by  VARCHAR2(30),
attempt_dt  DATE);


CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON DATABASE | SCHEMA

DECLARE
 oper hr.ddl_log.operation%TYPE; sql_text ora_name_list_t;
 i        PLS_INTEGER;
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM DUAL;

  i := sql_txt(sql_text);

  IF oper IN ('CREATE', 'DROP') THEN
    INSERT INTO hr.ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM DUAL;
  ELSIF oper = 'ALTER' THEN
    INSERT INTO hr.ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM sys.gv_$sqltext
    WHERE UPPER(sql_text) LIKE 'ALTER%'
    AND UPPER(sql_text) LIKE '%NEW_TABLE%';
  END IF;
END ddl_trigger;
/

TRIGGER TRUNCATE

create table event_log 
(log_date varchar2(36), 
event varchar2(36), 
username varchar2(36), 
owner varchar2(36), 
object_name varchar2(36), 
object_type varchar2(36), 
dbname varchar2(36) 
); 

create or replace trigger truncate_trg 
before truncate on database 
begin
insert into event_log 
values(sysdate, 
ora_sysevent, 
ora_login_user, 
ora_dict_obj_owner, 
ora_dict_obj_name, 
ora_dict_obj_type, 
ora_database_name) ; 
end; 
/ 

TRIIGER AFTER SERVERERROR

CREATE TABLE servererror_log (
    error_datetime  TIMESTAMP,
    error_user      VARCHAR2(30),
    db_name         VARCHAR2(9),
    error_stack     VARCHAR2(2000),
    captured_sql    VARCHAR2(1000));
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
 captured_sql VARCHAR2(1000);
BEGIN
  SELECT q.sql_text
  INTO captured_sql
  FROM gv$sql q, gv$sql_cursor c, gv$session s
  WHERE s.audsid = audsid
  AND s.prev_sql_addr = q.address
  AND q.address = c.parent_handle;

  INSERT INTO servererror_log
  (error_datetime, error_user, db_name,
   error_stack, captured_sql)
  VALUES
  (systimestamp, sys.login_user, sys.database_name,
  dbms_utility.format_error_stack, captured_sql);
END log_server_errors;

TRIGGER SERVICE

CREATE OR REPLACE TRIGGER
   manage_service
after startup on database
DECLARE
   role VARCHAR(30);
BEGIN
   SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
   IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('sales_rw');
   ELSE
      DBMS_SERVICE.START_SERVICE('sales_ro');
END IF;
END;

TRIGGER COMPOUND

CREATE TABLE compound_trigger_test (
  id           NUMBER,
  description  VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER compound_trigger_test_trg
  FOR INSERT OR UPDATE OR DELETE ON compound_trigger_test
    COMPOUND TRIGGER

  -- Global declaration.
  TYPE t_tab IS TABLE OF VARCHAR2(50);
  l_tab t_tab := t_tab();

  BEFORE STATEMENT IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - DELETE';
    END CASE;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id || ')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id || ')';
    END CASE;
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - DELETE';
    END CASE;
    
    FOR i IN l_tab.first .. l_tab.last LOOP
      DBMS_OUTPUT.put_line(l_tab(i));
    END LOOP;
    l_tab.delete;
  END AFTER STATEMENT;

END compound_trigger_test_trg;
/

TRIGGER INSTEAD OF

Example 9-2 INSTEAD OF Trigger

This example creates the view oe.order_info to display information about customers and their orders. The view is not inherently updatable (because the primary key of the orders table, order_id, is not unique in the result set of the join view). The example creates an INSTEAD OF trigger to process INSERT statements directed to the view. The trigger inserts rows into the base tables of the view, customers and orders.

CREATE OR REPLACE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o
   WHERE c.customer_id = o.customer_id;

CREATE OR REPLACE TRIGGER order_info_insert
   INSTEAD OF INSERT ON order_info
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN
     INSERT INTO customers
       (customer_id, cust_last_name, cust_first_name)
     VALUES (
     :new.customer_id,
     :new.cust_last_name,
     :new.cust_first_name);
   INSERT INTO orders (order_id, order_date, customer_id)
   VALUES (
     :new.order_id,
     :new.order_date,
     :new.customer_id);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END order_info_insert;
/

RESULT CACHE PL/SQL

Supongamos que estoy en un equipo que está construyendo una aplicación de recursos humanos. La tabla de empleados es una de las estructuras clave, manteniendo todos los datos para todos los empleados. Cientos de usuarios ejecutan numerosos programas en la aplicación que se leen de esta tabla y leen con mucha frecuencia. Sin embargo, la tabla cambia con relativa poca frecuencia, tal vez una o dos veces por hora. Como resultado, el código de aplicación recupera repetidamente de la caché de memoria intermedia de bloque lo que es en su mayoría datos estáticos, soportando la sobrecarga de comprobar si la consulta particular ya se ha analizado, encontrando los datos en el búfer y devolviéndolos.

El equipo necesita mejorar el rendimiento de consultar los datos de la tabla de empleados. En la actualidad, utilizamos la siguiente función para devolver una fila de la tabla de empleados:


The team needs to improve the performance of querying data from the employees table. Currently,

we use the following function to return a row from the employees table:

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
IS
    l_employee   employees%ROWTYPE;
BEGIN
   SELECT *
      INTO l_employee
      FROM employees
    WHERE employee_id = employee_id_in;

    RETURN l_employee;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
       /* Return an empty record. */
       RETURN l_employee;
END one_employee;

In Oracle Database 11g, however, we can add a line to the header of this function as follows:

FUNCTION one_employee (employee_id_in 
IN employees.employee_id%TYPE)
   RETURN employees%ROWTYPE
   RESULT_CACHE RELIES_ON (employees)
IS
    l_employee   employees%ROWTYPE;
BEGIN

Esta cláusula RESULT_CACHE le dice a Oracle Database que debería recordar (almacenar en una memoria caché de resultados en memoria especial) cada registro recuperado para un número de identificación de empleado específico. Y cuando una sesión ejecuta esta función y pasa un ID de empleado que fue almacenado previamente, el motor de tiempo de ejecución de PL / SQL no ejecutará el cuerpo de la función, que incluye esa consulta.En su lugar, simplemente recuperará el registro del caché y devolverá los datos inmediatamente. El resultado es una recuperación mucho más rápida.Además, especificando RELIES_ON (empleados), informamos a Oracle Database que si cualquier sesión confirma cambios en esa tabla, se invalidarán todos los datos de la caché de resultados extraídos de la tabla. La siguiente llamada a la función one_employee tendría entonces que ejecutar la consulta y recuperar los datos frescos de la tabla.Debido a que el caché es una parte del Área Global del Sistema (SGA), su contenido está disponible para todas las sesiones conectadas a la instancia. Además, Oracle Database aplicará su "algoritmo utilizado menos recientemente" a la caché, para garantizar que los datos más recientemente accedidos se conservarán en la caché.Antes de Oracle Database 11g, era posible un tipo similar de almacenamiento en caché con colecciones a nivel de paquete, pero esta caché era específica de la sesión y estaba ubicada en el Área de Proceso Global (PGA). Esto significa que si tengo 1.000 sesiones diferentes ejecutando la aplicación, podría utilizar una enorme cantidad de memoria además de la consumida por la SGA.La cache de resultados de la función PL / SQL minimiza la cantidad de memoria necesaria para almacenar en caché y compartir estos datos en todas las sesiones. Este perfil de memoria baja, además de la purga automática de los resultados almacenados en caché siempre que se cometen cambios, hace que esta característica de Oracle Database 11g sea muy práctica para optimizar el rendimiento en aplicaciones PL / SQL.

Predefined PL/SQL Exceptions

Predefined PL/SQL Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.

PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the following list:

Exception	Oracle Error	SQLCODE Value
ACCESS_INTO_NULL	ORA-06530	-6530
CASE_NOT_FOUND	ORA-06592	-6592
COLLECTION_IS_NULL	ORA-06531	-6531
CURSOR_ALREADY_OPEN	ORA-06511	-6511
DUP_VAL_ON_INDEX	ORA-00001	-1
INVALID_CURSOR	ORA-01001	-1001
INVALID_NUMBER	ORA-01722	-1722
LOGIN_DENIED	ORA-01017	-1017
NO_DATA_FOUND	ORA-01403	+100
NOT_LOGGED_ON	ORA-01012	-1012
PROGRAM_ERROR	ORA-06501	-6501
ROWTYPE_MISMATCH	ORA-06504	-6504
SELF_IS_NULL	ORA-30625	-30625
STORAGE_ERROR	ORA-06500	-6500
SUBSCRIPT_BEYOND_COUNT	ORA-06533	-6533
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	-6532
SYS_INVALID_ROWID	ORA-01410	-1410
TIMEOUT_ON_RESOURCE	ORA-00051	-51
TOO_MANY_ROWS	ORA-01422	-1422
VALUE_ERROR	ORA-06502	-6502
ZERO_DIVIDE	ORA-01476	-1476

Brief descriptions of the predefined exceptions follow:

Exception	Raised when ...
ACCESS_INTO_NULL	Your program attempts to assign values to the attributes of an uninitialized (atomically null) object.
CASE_NOT_FOUND	None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	Your program attempts to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR	Your program attempts an illegal cursor operation such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCHstatement does not evaluate to a positive number.
LOGIN_DENIED	Your program attempts to log on to Oracle with an invalid username and/or password.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
NOT_LOGGED_ON	Your program issues a database call without being connected to Oracle.
PROGRAM_ERROR	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL	Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null.
STORAGE_ERROR	PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT	Your program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID	The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	A time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE	Your program attempts to divide a number by zero.