Persistent And Transient Objects( SQL/PLSQL )

There are two different types of objects namely

1. Persistent and    2. Transient.

Persistent Objects: are those that are stored in the database [Objects created using abstract data types varrays, nested tables etc.].   These can be used both with SQL commands and also in PL/SQL blocks.  These reside in the data dictionary.  Persistent objects are available to the user until they are deleted explicitly.  They can be implemented as tables, columns or attributes.  

Transient object exists only within the scope of the PL/SQL block. These get automatically de-allocated once they go out of the scope of the PL/SQL block.  Examples of transient objects are PL/SQL variables. 

IN PL/SQL:

create or replace type Type_adds as object

(

Country varchar2(40),

phase_no varchar2(100),

postal varchar2(50),

city varchar2(200),

state varchar2(150),

pin number(10)

);

Type created.

declare

Add_emp

Type_adds:=Type_adds('America','phase01','ar','Phoenix','Arizona', 607);

begin

dbms_output.put_line('The postal is '|| Add_emp.postal);

end;

SQL> /

The postal is ar

In SQL:

create or replace type Type_adds as object

(

Country varchar2(40),

phase_no varchar2(100),

postal varchar2(50),

city varchar2(200),

state varchar2(150),

pin number(10)

);

Type created.

create table vend_mast(

vencode varchar2(5),vnename varchar2(10),venadd Type_adds,

tel_no number(10)

);

/

SQL> desc vend_mast;

 VENCODE VARCHAR2(5)

 VNENAME VARCHAR2(10)

 VENADD  TYPE_ADDS

 TEL_NO  NUMBER(10)

The data dictionaries to be used are:

• USER_TAB_COLUMNS for defined types used in a table’s columns.
• USER_TYPES_ATTRS for attributes of the defined type.

select column_name, data_type from user_tab_columns where table_name='vend_mast';

COLUMN_NAME       DATA_TYPE

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

VENCODE                VARCHAR2

VNENAME           VARCHAR2

VENADD            Type_adds

TEL_NO                 NUMBER