Oracle8: Problems updating an object table

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/07/25
Message-ID: <33d9ad11.1215670@netnews.worldnet.att.net>#1/1

I am having trouble updating an object table from a PL/SQL block. Strangely enough, the same update statement works when executed directly from SQL*Plus. This is really frustrating me. Any suggestions out there? The code is reproduced below. The table "buildings" is an object table based on object type "building". Notice that from within PL/SQL I get a type error, yet the very same statement works outside the pl/sql block. I am runnnig the Oracle8 beta, the one given to ODP members, on a Windows NT 4.0 platform.

SQL> describe buildings

 Name                            Null?    Type
 ------------------------------- -------- ----
 BLDGNAME                                 VARCHAR2(40)
 BLDGADDRESS                              NAMED TYPE
 BLDGMGR                                  NUMBER(38)

SQL>
SQL> DECLARE
  2 this_building building;
  3 BEGIN
  4 --This update calls the constructor in the SET clause.   5 UPDATE buildings b

  6       SET b = building(BldgName,BldgAddress,598)
  7     WHERE BldgName = 'Victor Building';

8 END;
9 /
DECLARE
*
ERROR at line 1:

ORA-06550: line 6, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3:

PL/SQL: SQL Statement ignored

SQL>
SQL> UPDATE buildings b
2 SET b = building(BldgName,BldgAddress,598) 3 WHERE BldgName = 'Victor Building';

1 row updated.

Jonathan Gennick
gennick_at_worldnet.att.net
"Brighten the Corner Where you Are" Received on Fri Jul 25 1997 - 00:00:00 CDT