Oracle Object

Null Objects and Attributes

An object whose value is NULL is called atomically null. An atomically null object is different from an object that has null values for all its attributes. In an object with null values, a table column, object attribute, collection, or collection element might be NULL if it has been initialized to NULL or has not been initialized at all. Usually, a NULL value is replaced by an actual value later on. When all the attributes are null, you can still change these attributes and call the object's subprograms or methods. With an atomically null object, you can do neither of these things.

Example 2-1 creates the contacts table and defines the person_typ object type and two instances of this type.

Example 2-1 Inserting NULLs for Objects in a Table

CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE OR REPLACE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/
CREATE TABLE contacts (
  contact        person_typ,
  contact_date   DATE );

INSERT INTO contacts VALUES (
  person_typ (NULL, NULL, NULL), '24 Jun 2003' );

INSERT INTO contacts VALUES (
  NULL, '24 Jun 2003' );

Two instances of person_typ are inserted into the table and give two different results. In both cases, Oracle Database allocates space in the contacts table for a new row and sets its DATE column to the value given. But in the first case, Oracle Database allocates space for an object in the contact column and sets each of the object's attributes to NULL. In the second case, Oracle Database sets the person_typ field itself to NULL and does not allocate space for an object.

In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is NULL.

A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, one that has no elements.

Character Length Semantics

Lengths for character types CHAR and VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections even if some of the characters consist of multiple bytes.

To specify character-denominated lengths for CHAR and VARCHAR2 attributes, you add the qualifier char to the length specification.

Like CHAR and VARCHAR2, NCHAR and NVARCHAR2 may also be used as attribute types in objects and collections. NCHAR andNVARCHAR2 are always implicitly measured in terms of characters, so no char qualifier is used.

For example, the following statement creates an object with both a character-length VARCHAR2 attribute and an NCHAR attribute:

Example 2-2 Creating the employee_typ Object Using a char Qualifier

CREATE OR REPLACE TYPE employee_typ AS OBJECT ( 
  name        VARCHAR2(30 char), 
  language    NCHAR(10), 
  phone       VARCHAR2(20) );
/

For CHAR and VARCHAR2 attributes whose length is specified without a char qualifier, the NLS_LENGTH_SEMANTICS initialization parameter setting (CHAR or BYTE) indicates the default unit of measure.

Constraints for Object Tables

You can define constraints on an object table just as you can on other tables. You can define constraints on the leaf-level scalar attributes of a column object, with the exception of REFs that are not scoped.

The following examples illustrate defining constraints.

Example 2-3 places an implicit PRIMARY KEY constraint on the office_id column of the object table office_tab.

Example 2-3 Creating the office_tab Object Table with a Constraint

-- requires Ex. 2-1
CREATE OR REPLACE TYPE location_typ AS OBJECT (
  building_no  NUMBER,
  city         VARCHAR2(40) );
/

CREATE OR REPLACE TYPE office_typ AS OBJECT (
  office_id    VARCHAR(10),
  office_loc   location_typ,
  occupant     person_typ );/

CREATE TABLE office_tab OF office_typ (
             office_id      PRIMARY KEY );

The object type location_typ defined in Example 2-3 is the type of the dept_loc column in the department_mgrs table inExample 2-4.

Example 2-4 defines constraints on scalar attributes of the location_typ objects in the table.

Example 2-4 Creating the department_mgrs Table with Multiple Constraints

-- requires Ex. 2-1 and 2-3
CREATE TABLE department_mgrs (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_mgr    person_typ,
  dept_loc    location_typ,
  CONSTRAINT  dept_loc_cons1
      UNIQUE (dept_loc.building_no, dept_loc.city),
  CONSTRAINT  dept_loc_cons2
       CHECK (dept_loc.city IS NOT NULL) );

INSERT INTO department_mgrs VALUES 
          ( 101, 'Physical Sciences', 
           person_typ(65,'Vrinda Mills', '1-1-650-555-0125'),
           location_typ(300, 'Palo Alto'));

See Also:

"Constraints on Objects"

Indexes for Object Tables

You can define indexes on an object table or on the storage table for a nested table column or attribute just as you can on other tables. For an example of an index on a nested table, see Example 5-5.

You can define indexes on leaf-level scalar attributes of column objects, as shown in Example 2-5. You can only define indexes on REF attributes or columns if the REF is scoped. This example indexes city, which is a leaf-level scalar attribute of the column object dept_addr.

Example 2-5 Creating an Index on an Object Type in a Table

-- requires Ex. 2-1, 2-3, 
CREATE TABLE department_loc (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_addr   location_typ );

CREATE INDEX  i_dept_addr1
          ON  department_loc (dept_addr.city);

INSERT INTO department_loc VALUES
          ( 101, 'Physical Sciences',
           location_typ(300, 'Palo Alto'));
INSERT INTO department_loc VALUES 
          ( 104, 'Life Sciences', 
           location_typ(400, 'Menlo Park'));
INSERT INTO department_loc VALUES 
          ( 103, 'Biological Sciences', 
           location_typ(500, 'Redwood Shores'));

Wherever Oracle Database expects a column name in an index definition, you can also specify a scalar attribute of a column object.

Triggers for Object Tables

You can define triggers on an object table just as you can on other tables. You cannot define a trigger on the storage table for a nested table column or attribute. You cannot modify LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.

Example 2-6 defines a trigger on the office_tab table defined in "Constraints for Object Tables".

Example 2-6 Creating a Trigger on Objects in a Table

-- requires Ex. 2-1 and 2-3
CREATE TABLE movement (
     idno           NUMBER,
     old_office     location_typ,
     new_office     location_typ );

CREATE TRIGGER trigger1
  BEFORE UPDATE
             OF  office_loc
             ON  office_tab
   FOR EACH ROW
           WHEN  (new.office_loc.city = 'Redwood Shores')
   BEGIN
     IF :new.office_loc.building_no = 600 THEN
      INSERT INTO movement (idno, old_office, new_office)
       VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc);
     END IF;
   END;/
INSERT INTO office_tab VALUES 
    ('BE32', location_typ(300, 'Palo Alto' ),person_typ(280, 'John Chan', 
       '415-555-0101'));
 
UPDATE office_tab set office_loc =location_typ(600, 'Redwood Shores')
  where office_id = 'BE32'; 
 
select * from office_tab;
 
select * from movement;

Rules for REF Columns and Attributes

In Oracle Database, a REF column or attribute can be unconstrained or constrained using a SCOPE clause or a referential constraint clause. When a REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.

Oracle Database does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that do not point to any existing row object. Such REF values are referred to as dangling references.

A SCOPE constraint can be applied to a specific object table. All the REF values stored in a column with a SCOPE constraint point at row objects of the table specified in the SCOPE clause. The REF values may, however, be dangling.

A REF column may be constrained with a REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.

PRIMARY KEY constraints cannot be specified for REF columns. However, you can specify NOT NULL constraints for such columns.

See Also:

• "Using References to Row Objects"
• "Substitution of REF Columns and Attributes"

Name Resolution

Oracle SQL lets you omit qualifying table names in some relational operations. For example, if dept_addr is a column in thedepartment_loc table and old_office is a column in the movement table, you can use the following:

SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE dept_addr = old_office);

Oracle Database determines which table each column belongs to.

Using dot notation, you can qualify the column names with table names or table aliases to make things more maintainable. For example:

Example 2-7 Using the Dot Notation for Name Resolution

-- requires Ex. 2-1, 2-3, 2-5, and 2-6
SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE department_loc.dept_addr = movement.old_office);

SELECT * FROM department_loc d WHERE EXISTS 
  (SELECT * FROM movement m WHERE d.dept_addr = m.old_office);

In some cases, object-relational features require you to specify the table aliases.

When Table Aliases Are Required

Using unqualified names can lead to problems. For example, if you add an assignment column to depts and forget to change the query, Oracle Database automatically recompiles the query so that the inner SELECT uses the assignment column from thedepts table. This situation is called inner capture.

To avoid inner capture and similar problems resolving references, Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects.

Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation. For example, the following statements define two tables that contain the person_typ object type. person_obj_table is an object table for objects of type person_typ, and contacts is a relational table that contains a column of the object person_typ.

The following queries show some correct and incorrect ways to reference attribute idno:

Note:

These statements are not related to other examples in this chapter.

#1 SELECT idno FROM person_obj_table; --Correct

#2 SELECT contact.idno FROM contacts; --Illegal
#3 SELECT contacts.contact.idno FROM contacts; --Illegal
#4 SELECT p.contact.idno FROM contacts p; --Correct

• In #1, idno is the name of a column of person_obj_table. It references this top-level attribute directly, without using the dot notation, so no table alias is required.
• In #2, idno is the name of an attribute of the person_typ object in the column named contact. This reference uses the dot notation and so requires a table alias, as shown in #4.
• #3 uses the table name itself to qualify the reference. This is incorrect; a table alias is required.

You must qualify a reference to an object attribute or subprogram with a table alias rather than a table name even if the table name is itself qualified by a schema name.

For example, the following expression incorrectly refers to the HR schema, department_loc table, dept_addr column, and cityattribute of that column. The expression is incorrect because department_loc is a table name, not an alias.

HR.department_loc.dept_addr.city

The same requirement applies to attribute references that use REFs.

Table aliases should uniquely pick out the same table throughout a query and should not be the same as schema names that could legally appear in the query.

Note:

Oracle recommends that you define table aliases in all UPDATE, DELETE, and SELECT statements and subqueries and use them to qualify column references whether or not the columns contain object types.

Restriction on Using User-Defined Types with a Remote Database

Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. Oracle Database restricts use of a database link as follows:

• You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.
  You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.
• You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
• You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Bulk collect , For all, save exception,Bulk Row count

Bulk collect , For all, save exception

PL/SQL anonymous block doing row-by-row INSERT 

SQL> CREATE TABLE sales_target_errors
  2  (sql_err_mesg varchar2(4000))
  3  /

SQL>  DECLARE
  2        TYPE array IS TABLE OF sales_target%ROWTYPE
  3           INDEX BY BINARY_INTEGER;
  4        sales_src_arr   ARRAY;
  5        errors          NUMBER;
  6        error_mesg     VARCHAR2(255);
  7        bulk_error      EXCEPTION;
  8        l_cnt           NUMBER := 0;
  9        PRAGMA exception_init
 10              (bulk_error, -24381);
 11        CURSOR c IS 
 12           SELECT * 
 13           FROM   sales_src;
 14        BEGIN
 15        OPEN c;
 16        LOOP
 17          FETCH c 
 18             BULK COLLECT 
 19             INTO sales_src_arr 
 20             LIMIT 100;
 21          BEGIN
 22             FORALL i IN 1 .. sales_src_arr.count 
 23                      SAVE EXCEPTIONS
 24               INSERT INTO sales_target VALUES sales_src_arr(i);
 25          EXCEPTION
 26          WHEN bulk_error THEN
 27            errors := 
 28               SQL%BULK_EXCEPTIONS.COUNT;
 29            l_cnt := l_cnt + errors;
 30            FOR i IN 1..errors LOOP
 31              error_mesg := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 32              INSERT INTO sales_target_errors 
 33              VALUES     (error_mesg);
 34       END LOOP;
 35          END;
 36          EXIT WHEN c%NOTFOUND;
 37      
 38       END LOOP;
 39       CLOSE c;
 40       DBMS_OUTPUT.PUT_LINE
 41        ( l_cnt || ' total errors' );
 42       END;

-------------------------------------------------------------------------------

2)

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS





SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS are used in bulk opeartions with FORALL

SQL%BULK_ROWCOUNT
Let say, we are processing a bulk operation, need to know how many records are processed by each interation for the given DML (Update, Delete and Insert).

We have SQL%BULK_ROWCOUNT attribute to know the number of records processed in each iteration in a bulk operation.

The follwoing example shows how to use it.

ORCL> create table t_emp2 as select * from emp
  2  /

Table created.

ORCL> select deptno, count(*)
  2     from t_emp2
  3     where deptno in (10, 30)
  4     group by deptno
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        30          6

Dept 30 have 6 rows and dept 10 have 3 rows.

ORCL> declare
  2  
  3     type lt_dept_arr is table of number;
  4     l_dept_arr lt_dept_arr := lt_dept_arr(10, 30);
  5  
  6  begin
  7  
  8     forall i in l_dept_arr.first..l_dept_arr.last
  9     delete from t_emp2 where deptno = l_dept_arr(i);
 10  
 11     for j in l_dept_arr.first..l_dept_arr.last loop
 12        dbms_output.put_line ('The number of records deleted in '
 13                               || j || 'th iteration are  '
 14                               ||sql%bulk_rowcount(j) );
 15     end loop;
 16  
 17  end;
/
The number of records deleted in 1th iteration are  3
The number of records deleted in 2th iteration are  6

PL/SQL procedure successfully completed.


Handling exceptions in FORAll using SQL%BULK_EXCEPTIONS
PL/SQL provides a mechanism to handle exceptions that were raised during the FORALL bulk operation using SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ). noted that '-' is added at the beginning
of the SQL%BULK_EXCEPTIONS in the SQLERRM function.

Lets take an example to illustrate the functionality.

srikar@ORCL> create table t_emp as select * from emp;

Table created.

We have created a table 't_emp' whice is similar to emp table.

Now, we append each empname with ' Oracl' due to which error is raised in the bulk operation as the empname column maximum size is 10 characters and the append is not allowed for few columns,so the records whose length is crossing 10 characters would raise an exception.

In the follwoing code, if an exception is raised then the exception is saved and processed with the next records.


ORCL> ed
Wrote file afiedt.buf

  1  declare
  2         type lt_emp_arr is table of t_emp.empno%type;
  3         l_emp_arr    lt_emp_arr;
  4         errors       number;
  5  begin
  6         select empno bulk collect into l_emp_arr from t_emp;
  7         forall i in l_emp_arr.first..l_emp_arr.last save exceptions
  8         update t_emp set ename = ename||' Oracl' where empno = l_emp_arr(i) ;
  9  exception
 10       when others then
 11        errors := sql%bulk_exceptions.count;
 12        dbms_output.put_line ('The total number of errors occured are '
 13                                  || errors
 14                             );
 15        for j in 1..errors loop
 16           dbms_output.put_line ('The error iteration is  '
 17                                  || sql%bulk_exceptions(j).error_index
 18                                  || ' and the error code is '
 19                                  || sql%bulk_exceptions(j).error_code
 20                                  || ' and the error message is '
 21                                  || sqlerrm (- sql%bulk_exceptions(j).error_code)
 22                                );
 23        end loop;
 24* end;
 25  /
The total number of errors occured are 11
The error iteration is  1 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  2 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  4 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  5 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  6 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  7 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  8 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  12 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )

PL/SQL procedure successfully completed.

ORCL> select ename from t_emp;

ENAME
----------
SMITH
ALLEN
WARD Oracl
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING Oracl
TURNER
ADAMS
JAMES
FORD Oracl
MILLER

14 rows selected.