Very Computer

Hallo, I need help or some information, where I can get examples on how to
use abstract data types in tables. creating tables where columns are defined
as abstract datatypes does work. Furthermore I can access the data in it
with standard select-syntax using alias for the table. Now my problem is
especially in declaring a cursor for complex data and insertion in another
table, using the same object type.
Would be great if someone could help me soon!
Sabine

Examples follow.
--
-cheers
  DW
--------------------------------------------------------------------
"It is a kind of good deed to say well; and yet words are not deeds.
  -William Shakespeare"

====================
SIMPLE TABLE
====================

CREATE:
--------------
CREATE TYPE TYPE1 AS OBJECT(
        TYPE1_C1 NUMBER(5),
        TYPE1_C2 VARCHAR2(40));

CREATE TYPE TYPE2 AS OBJECT(
        TYPE2_C1 NUMBER(5),
        TYPE2_C2 VARCHAR2(40));

CREATE TYPE TABLE_OF_OBJECTS AS OBJECT(
        FIELD1 TYPE1,
        FIELD2 TYPE2);

--The following Create statements will create two different tables of
the same type.
CREATE TABLE TABLE_OF_TYPES(
        FIELD1 TYPE1,
        FIELD2 TYPE2);

CREATE TABLE MYTABLE_OF_OBJECTS OF TABLE_OF_OBJECTS

INSERT:
-------------
--These insert statement will work for either table.
INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(1, 'DAVE'), TYPE2
(100, 'JIM'));
INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(2, 'BILL'), TYPE2
(200, 'AL'));
INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(1, 'DAVE'), TYPE2(NULL,
NULL));
INSERT INTO MYTABLE_OF_OBJECTS VALUES (TYPE1(NULL, NULL), TYPE2
(200, 'LISA'));

SELECT:
--------------
--Generic select
SELECT * FROM MYTABLE_OF_OBJECTS;
--Select specific data from Field1
SELECT X.FIELD1.TYPE1_C1, X.FIELD1.TYPE1_C2 FROM MYTABLE_OF_OBJECTS X;
--Select specific data from Field2
SELECT X.FIELD2.TYPE2_C1, X.FIELD2.TYPE2_C2 FROM MYTABLE_OF_OBJECTS X;

UPDATE:
--------------
--Updates only one of the two fields contained in Field1's datatype
UPDATE MYTABLE_OF_OBJECTS X
SET X.FIELD1.TYPE1_C2 = 'JIM'
WHERE X.FIELD2.TYPE2_C2 = 'LISA';

DELETE:
---------------
--Deletes one record based on one of Field2's datatypes
DELETE MYTABLE_OF_OBJECTS X WHERE X.FIELD2.TYPE2_C2 = 'JIM';

================================================================
Complex tables with a Simple VARRAY and a VARRAY of a Structure (NO
REFs)
================================================================

CREATE:
---------------
CREATE TYPE TYPE_VARRAY1 AS VARRAY(10) OF INT;

CREATE TYPE TYPE1_STRUCT AS OBJECT (C1 NUMBER(5), C2 VARCHAR2(30));
CREATE TYPE TYPE2_STRUCT_VARRAY AS VARRAY(10) OF TYPE1_STRUCT;

CREATE TYPE CUSTOM_TYPE2 AS OBJECT (
    CUSTOMFIELD1 TYPE_VARRAY1,
    CUSTOMFIELD2 TYPE2_STRUCT_VARRAY);

CREATE TABLE CUSTOM_TABLE2(ID NUMBER(5), CUSTOMFIELD CUSTOM_TYPE2);

INSERT:
--------------
INSERT INTO CUSTOM_TABLE2 VALUES (1,
        CUSTOM_TYPE2( TYPE_VARRAY1(20), TYPE2_STRUCT_VARRAY(TYPE1_STRUCT
(1, 'ROW1'))))

INSERT INTO CUSTOM_TABLE2 VALUES (3,
        CUSTOM_TYPE2( TYPE_VARRAY1(30,31,32),
   TYPE2_STRUCT_VARRAY(TYPE1_STRUCT(1, 'ROW 1'),
                       TYPE1_STRUCT(2, 'ROW 2'),
                       TYPE1_STRUCT(3, 'ROW 3') ) ) )

--The following PL/SQL block will append a new element to the array of
an existing record
DECLARE
     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := 9;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(9, 'Adding 9 using
PL/SQL');
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = 1;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = 1;
     -- Don't forget to commit
 END;
/

--The following Stored Procedure will append a new element to the array
of an existing --record by passing in the ID of the record to append
followed by the data
--for both arrays.
CREATE OR REPLACE PROCEDURE ADD_ELEMENT
(UPDATEID IN NUMBER, VARRAY1 IN NUMBER, VARRAY2_1 NUMBER, VARRAY2_2
VARCHAR2)
AS
     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := VARRAY1;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(VARRAY2_1,
VARRAY2_2);
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = UPDATEID;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = UPDATEID;
     --Don't forget to commit.
 END;
/

SELECT:
--------------
--Generic select
SELECT X.ID FROM CUSTOM_TABLE2 X;
--Selects data contained in the first VARRAY
SELECT X.CUSTOMFIELD.CUSTOMFIELD1 FROM CUSTOM_TABLE2 X;
--Selects data contained in the VARRAY of struct
SELECT X.CUSTOMFIELD.CUSTOMFIELD2 FROM CUSTOM_TABLE2 X;
--In order to select specific fields within the VARRAY structure you
must first create table type of --the object.
CREATE TYPE NESTTYPE IS TABLE OF TYPE1_STRUCT;
SELECT C1 FROM THE
(SELECT CAST(X.CUSTOMFIELD.CUSTOMFIELD2 AS NESTTYPE)
 FROM CUSTOM_TABLE2 X WHERE X.ID = 3);

UPDATE:
--------------
--Update of VARRAY elements must be done in a PL/SQL block or Stored
Procedures

--Sample PL/SQL
DECLARE
     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = 1;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := 9;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(9, 'Adding 9 using
PL/SQL');
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = 1;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = 1;
 END;
/

--Sample Stored Procedure which makes the above PL/SQL a little more
dynamic
CREATE OR REPLACE PROCEDURE ADD_ELEMENT
(UPDATEID IN NUMBER, VARRAY1 IN NUMBER, VARRAY2_1 NUMBER, VARRAY2_2
VARCHAR2)
AS
     NEW_ELEMENT1 TYPE_VARRAY1;
     NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
  BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD1 INTO NEW_ELEMENT1 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = UPDATEID;
     NEW_ELEMENT1.extend;
     NEW_ELEMENT1(NEW_ELEMENT1.LAST) := VARRAY1;
     NEW_ELEMENT2.extend;
     NEW_ELEMENT2(NEW_ELEMENT2.LAST) := TYPE1_STRUCT(VARRAY2_1,
VARRAY2_2);
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD1 =
NEW_ELEMENT1 WHERE ID = UPDATEID;
     UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = UPDATEID;
 END;
/

DELETE:
----------------
--The following PL/SQL block will delete an element from an array.
--NOTE: You can only delete the last element of any given array.
DECLARE
      NEW_ELEMENT2 TYPE2_STRUCT_VARRAY;
   BEGIN
     SELECT X.CUSTOMFIELD.CUSTOMFIELD2 INTO NEW_ELEMENT2 FROM
CUSTOM_TABLE2 X WHERE ID = 3;
     DBMS_OUTPUT.PUT_LINE(NEW_ELEMENT2.COUNT);
      NEW_ELEMENT2.TRIM(1);
      UPDATE CUSTOM_TABLE2 X SET X.CUSTOMFIELD.CUSTOMFIELD2 =
NEW_ELEMENT2 WHERE ID = 3;
     DBMS_OUTPUT.PUT_LINE(NEW_ELEMENT2.COUNT);
  END;

==============================
Nested Tables (NO REFs)
==============================

CREATE:
---------------
CREATE TYPE NESTED_TYPE AS OBJECT (C1 NUMBER(5), C2 VARCHAR2(30));
CREATE TYPE NESTED_TYPE_TABLE AS TABLE OF NESTED_TYPE;
CREATE TABLE NESTED_SAMPLE (
FIELD1 NUMBER(5),
FIELD2 NESTED_TYPE_TABLE) NESTED TABLE FIELD2 STORE AS NESTED_TABLE;

INSERT:
-------------
INSERT INTO NESTED_SAMPLE VALUES (1,     NESTED_TYPE_TABLE(NESTED_TYPE
(100, 'Entry 1')));
INSERT INTO NESTED_SAMPLE VALUES (2,       NESTED_TYPE_TABLE(NESTED_TYPE
(200, NULL)));
INSERT INTO NESTED_SAMPLE VALUES (3,       NESTED_TYPE_TABLE(NESTED_TYPE
(NULL, 'Entry 3')));
INSERT INTO NESTED_SAMPLE VALUES (5,       NESTED_TYPE_TABLE(NESTED_TYPE
(500, 'Entry 4),
                                              NESTED_TYPE(501, 'Entry
5')))
INSERT INTO NESTED_SAMPLE VALUES (6,       NESTED_TYPE_TABLE(NESTED_TYPE
(100, 'Entry 6')));
--Inserts into the nest table of an existing parent record.
--Note: You can only insert one record at a time.
INSERT INTO THE (SELECT FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6)
VALUES (600, 'NEW ROW');

SELECT:
--------------
--Generic select
SELECT * FROM NESTED_SAMPLE;
--Selects only the fields in the Nested Table
SELECT * FROM THE (SELECT FIELD2 FROM NESTED_SAMPLE X WHERE X.FIELD1=1);
--Displays field(s) using a selection wihich is based on criteria
against the nested table
SELECT   D.FIELD1
   FROM  NESTED_SAMPLE D
   WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E WHERE E.C1 = 100);

UPDATE:
-----------------
--Updates parent record based on nest table criteria
UPDATE NESTED_SAMPLE D
   SET D.FIELD1 = 10
   WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E WHERE E.C1 = 100);

--Updates nested table record based on parent criteria
UPDATE THE (SELECT D.FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6) I
    SET I.C1 = 10

DELETE:
-----------------
--Deleting entire record based on nested table selection criteria
DELETE NESTED_SAMPLE D WHERE EXISTS (SELECT * FROM TABLE (D.FIELD2) E
WHERE E.C2 = 'Entry 6');

--Deleting data in the nested table
DELETE THE (SELECT FIELD2 FROM NESTED_SAMPLE D WHERE D.FIELD1 = 6) X
 WHERE X.C1 = 600;

--Deleting data from outer table
DELETE FROM NESTED_SAMPLE D WHERE D.FIELD1 = 2;

Individual elements of the VARRAY are not queryable. You add a method
to your type that will return a condition you can query on or change it
to using nested tables. Queryability of individual elements is one of
the reasons to use a nested table over a varray.
------------------------------------------------------------------------
--------

CREATE TYPE STRUCT1_TYPE AS OBJECT (F1 NUMBER(5), F2 VARCHAR2(30));
/

CREATE TYPE VARRAY_OF_STRUCT1_TYPE IS VARRAY(5) OF STRUCT1_TYPE;
/

CREATE OR REPLACE TYPE VARRAY_OF_STRUCT1_OBJECT as object (
field1 VARRAY_OF_STRUCT1_TYPE,
MEMBER FUNCTION containsF1 (testnum NUMBER) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES ...

read more »