Use of objects in PL/SQL

From: Lloyd Sheen <sqlguy_at_hotmail.com>
Date: Mon, 27 May 2002 17:04:37 GMT
Message-ID: <FGtI8.103504$ah_.30596@news01.bloor.is.net.cable.rogers.com>

I am trying to simulate the existance of objects in a table in PL/SQL.

The following shows 2 PL/SQL scripts and the resulting error. The first script is to create the address type. The second script is the script to attemp to create an object in PL/SQL and then assign it to a reference variable.
Following that is the error recieved.

If I store the object to a table I can then retrieve it as a ref but this is not what is needed.

CREATE OR REPLACE TYPE ADDRESS AS object (
 type char,
 first_line varchar(60),
 second_line varchar(60),
 city varchar(60),
 state_prov varchar(3),
 country varchar(20),
 zip_postalcode varchar(10)
)

declare

 addr address;
 daddr address;
 addref ref address;

begin
 addr := address('H','First Line','Second Line','City','Ont','Canada','K1C 3R5');
 dbms_output.put_line('1: ' || addr.first_line);

 addref := ref(addr);

 insert into addrtable values (addr);
 select ref(t) into addref from addrtable t where ROWNUM = 1;

• You cannot use the DEREF function in procedural code

 SELECT DEREF(addref) INTO daddr FROM dual;  dbms_output.put_line('2: ' || daddr.first_line); end;

ORA-06550: line 12, column 12:
PLS-00306: wrong number or types of arguments in call to 'REF'
ORA-06550: line 12, column 2:

PL/SQL: Statement ignored
 Error detected at position #:213 Received on Mon May 27 2002 - 12:04:37 CDT