Using a view with object types in ORACLE 10

From: Wolfgang <wolfgang.oberaigner_at_gmail.com>
Date: 5 Sep 2006 07:11:12 -0700
Message-ID: <1157465472.393855.168890@i42g2000cwa.googlegroups.com>

I had the problem that I could not get any meta data from some view columns if the result was calculated like:

CREATE VIEW CCM.V_LENGTH
(PRODUCT_NAME, PRODUCT_LENGTH) AS

SELECT
product.product_name,
greater(product.product_length, 10)
FROM ..... for example if I do a DESCRIBE V_LENGTH I get a data type NUMBER for PRODUCT_LENGTH. I found a workaround by using an object type for this view in which I specified a name and a data type like:

CREATE OR REPLACE
TYPE T_LENGTH
AS OBJECT
(

    ,PRODUCT_NAME               VARCHAR2(20)
    ,PRODUCT_LENGTH             NUMBER(10,3)

);

If I now create the view like this:

CREATE OR REPLACE FORCE VIEW CCM.V_LENGTH OF T_LENGTH
WITH OBJECT IDENTIFIER .... and I do a describe of that view I get as data type NUMBER(10,3) for PRODUCT_LENGTH. This was working fine with ORACLE 9i, but since ORACLE 10g this does not work any more.

Does anybody have any idea why this is not working any more and is there a aorkaround?

Thanks

Wolfgang Received on Tue Sep 05 2006 - 09:11:12 CDT