Oracle PL/SQL Tutorial/Object Oriented/VALUE Function

Use the built-in Oracle database VALUE() function to select a row from an object table

Value() treats the row as an actual object and returns the attributes for the object within a constructor for the object type.

The VALUE() function accepts a parameter containing a table alias

SQL> CREATE Or Replace TYPE ProductType AS OBJECT (
  2    id          NUMBER,
  3    name        VARCHAR2(15),
  4    description VARCHAR2(22),
  5    price       NUMBER(5, 2),
  6    days_valid  NUMBER
  7  )
  8  /
Type created.
SQL>
SQL> CREATE TABLE object_products OF ProductType
  2  /
Table created.
SQL>
SQL> INSERT INTO object_products (
  2    id, name, description, price, days_valid
  3  ) VALUES (
  4    2, "AAA", "BBB", 2.99, 5
  5  );
1 row created.
SQL>
SQL> select * from object_products;
 ID NAME            DESCRIPTION                 PRICE DAYS_VALID
--- --------------- ---------------------- ---------- ----------
  2 AAA             BBB                          2.99          5
SQL>
SQL> SELECT VALUE(op)
  2  FROM object_products op;
VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)
---------------------------------------------------
PRODUCTTYPE(2, "AAA", "BBB", 2.99, 5)
SQL>
SQL>
SQL> drop table object_products;
Table dropped.
SQL>

VALUE operator

SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT
  2              (line1 VARCHAR2(20),
  3               line2 VARCHAR2(20),
  4               city VARCHAR2(20),
  5               state_code VARCHAR2(2),
  6               zip VARCHAR2(13),
  7    MEMBER FUNCTION get_address RETURN VARCHAR2,
  8    MEMBER PROCEDURE set_address
  9              (addressLine1 VARCHAR2,
 10               addressLine2 VARCHAR2,
 11               address_city VARCHAR2,
 12               address_state VARCHAR2,
 13               address_zip VARCHAR2)
 14  );
 15  /
Type created.
SQL> CREATE OR REPLACE TYPE BODY address AS
  2    MEMBER FUNCTION get_address RETURN VARCHAR2
  3    IS
  4    BEGIN
  5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
  6    END get_address;
  7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
  8                  addressLine2 VARCHAR2,
  9                  address_city VARCHAR2,
 10                  address_state VARCHAR2,
 11                  address_zip VARCHAR2)
 12    IS
 13    BEGIN
 14      line1 :=addressLine1;
 15      line2 :=addressLine2;
 16      city :=address_city;
 17      state_code :=address_state;
 18      zip :=address_zip;
 19    END set_address;
 20  END;
 21  /
Type body created.
SQL>
SQL> CREATE TABLE address_master OF address;
Table created.
SQL>
SQL> INSERT INTO address_master VALUES (address("19 J","R Rd","Vancouver","NJ","00000"));
1 row created.
SQL>
SQL> select * from address_master;
LINE1                LINE2                CITY                 ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J                 R Rd                 Vancouver            NJ
00000

1 row selected.
SQL>
SQL> DECLARE
  2    addressValue address;
  3  BEGIN
  4    SELECT VALUE(a) INTO addressValue FROM address_master a WHERE a.city ="Vancouver";
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_master;
Table dropped.
SQL>