Mega Code Archive
Use rowtype with object table
<![CDATA[
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','Reading Rd','Vancouver','NJ','00000'));
1 row created.
SQL>
SQL> select * from address_master;
LINE1 LINE2 CITY ST
-------------------- -------------------- -------------------- --
ZIP
-------------
19 J Reading Rd Vancouver NJ
00000
1 row selected.
SQL>
SQL> declare
2 cursor c1 is select VALUE(a)from address_master a;
3 v_add address_master%ROWTYPE;
4 begin
5 open c1;
6 loop
7 fetch c1 into v_add;
8 exit when c1%notfound;
9 dbms_output.put_line(v_add.line1);
10 end loop;
11 close c1;
12 end;
13 /
19 J
PL/SQL procedure successfully completed.
SQL>
SQL> drop table address_master;
Table dropped.
SQL>
]]>