Objects -- Missing Right Parenthesis RUNTIME Error 1

[PCStorm]

I am getting a RUNTIME error when using objects "Missing right parenthesis". We have run this in a different version installation of Oracle (8i) without a problem. But the newly installed oracle8 enterprise edition is giving us a problem. Oracle support closed the support track saying they no longer support this version of Oracle.

Unfortunately we have a client that we support and must match their environment. Does anyone know what might be missing from our environment?

This is the code and error:

declare
l_users gl_jointype := gl_joinType ( gl_JoinOBJECT (1,'test'));
begin
insert into t select * from TABLE ( CAST (
l_users as gl_JoinType ) );
END;

gives me the following RUNTIME error

ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at line 4


We are running oracle: this is our SQL Plus blurb

Connected to:
Oracle8 Enterprise Edition Release 8.0.5.0.0 -
Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.0.0 - Production

This is the object and type creation:

DROP TYPE gl_JoinType;
CREATE OR REPLACE TYPE gl_JoinObject AS OBJECT
( NumberJoin NUMBER, -- Number for Dynamic
Joins
CharJoin VARCHAR(80) -- Varchar for Dynamic
Joins
);

CREATE OR REPLACE TYPE gl_JoinType AS TABLE OF
gl_JoinObject;

Create the table:
CREATE TABLE t ( NumberJoin Number, CharJoin
VARCHAR2(80));

Thanks

 

[spenglerr]

Hi,

actually I am a little bit confused about your text. You tested it on Oracle 8i to port it then backwards to Oracle 8.0? Since your Oracle SQL*PLus tells you that you are running on 8.0.5 EE this is a downgrading!

 

[PCStorm]

The procedure using objects we are trying to get working is for Enterprise 8.0.5. I isolated the problem from the procedure, and created a simple demonstration of the problem. I then ran the same code in our 8i installation where I know objects is working correctly. This was just to be sure that it should work and there was nothing wrong with the syntax.

As far as I can tell this code should work in Enterprise 8.0.5 with objects installed.

 

[spenglerr]

Hi PC Storm,

I found this hint in the Oracle Metalink concerning your version, hope this clarifies the things for you:

-------------------------------------------


From: Engineering Services 11-Aug-00 01:07
Subject: CAST to VARCHAR2 in 8.0.5


CAST to VARCHAR2 in 8.0.5

I'm trying to use CAST to alter to datatype of a result set :

e.g. select cast(id as VARCHAR2(20)) from foo;

This works great in 8.1.5.

Is there any way of doing the same thing in 8.0.5?

Thanks,
Greg





--------------------------------------------------------------------------------

From: Oracle, Anil Shenoy 16-Aug-00 13:05
Subject: Re : CAST to VARCHAR2 in 8.0.5


Hi,
This is a new feature in Oracle 8i. In 8i its possible to cast
built-in datatype or collection type to another built-in datatype or collection type. This feature is not available in 8.0.5.




Regards,
Anil
Oracle Support Services