Re: Using a view with object types in ORACLE 10

"Wolfgang" <wolfgang.oberaigner_at_gmail.com> a écrit dans le message de news: 1157465472.393855.168890_at_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
|

There is a CAST funtion:

CREATE VIEW CCM.V_LENGTH
(PRODUCT_NAME, PRODUCT_LENGTH) AS
SELECT
    product.product_name,
    CAST(greater(product.product_length, 10) as NUMBER(10,3)) FROM ..... Exemple:

SQL> create view v as select cast(10 as number(10,3)) c from dual;

View created.

SQL> desc v

 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C                                      NUMBER(10,3)

Regards
Michel Cadot Received on Tue Sep 05 2006 - 10:37:29 CDT