Oracle Object Types problem

From: Udo Baumgart <info_at_ubit.de>
Date: Fri, 22 Feb 2002 13:57:02 +0100
Message-ID: <3C76401E.D9785799@ubit.de>

Hi,

i am using Oracle 8.1.6.1.0.

I defined the following type:

CREATE OR REPLACE TYPE t_plz AS OBJECT (

	lkz	VARCHAR2(3),
	plz	VARCHAR2(5)

);

Now i created a simple table using this type:

CREATE TABLE demo (

	id	NUMBER,
	plz	t_plz

);

I can fill this table using the following syntax:

INSERT INTO demo VALUES (1,t_plz('D','45473'));

No problem.

Now i tried to SELECT the field plz from the type:

SELECT plz.plz FROM demo;

This give an error message.... But:

SELECT t.plt.plt FROM demo.t;

Works. Strange.

But now i have a serious problem. I have to access the table via the dbms_sql package. It seems that there is NO define_column or column_value function that is able to deal with those user defined object types. Can this be true? Is there any solution or workaround for accessing user defined types via dbms_sql?

I DONT want to use the components in dynamic SQL.

-- 

Ciao, Udo (Mülheim a.d. Ruhr)

Received on Fri Feb 22 2002 - 06:57:02 CST