Re: Oracle8, object schema, REFs in nested table

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 17 Apr 1998 14:19:33 GMT
Message-ID: <353764c8.6811003@192.86.155.100>

A copy of this was sent to leskor_at_hron.fei.tuke.sk (if that email address didn't require changing) On Thu, 16 Apr 1998 19:01:07 -0600, you wrote:

>Hello,
>can somebody help me with the object schema in Oracle8 ?
>( REFs in nested tables )
>
>For example, if I have the object schema ( it is from the Oracle8
>documentation ):
>
> CREATE TYPE emp_t AS OBJECT ( eno number, ename char(31));
> CREATE TYPE emps_t AS TABLE OF REF emp_t;
> CREATE TABLE emptab OF emp_t;
> CREATE TABLE dept (dno NUMBER, employees EMPS_T)
> NESTED TABLE employees STORE AS deptemps;
>
>how can I access attributes from the table emptab, by means of REF in
>nested table employees of the table dept ???
>
>Example:
>SELECT e.eno FROM THE ( SELECT employees FROM dept WHERE dno=1 ) e;
>

Here is an example:

SQL> CREATE TYPE emp_t AS OBJECT ( eno number, ename char(31));   2 /

Type created.

SQL> CREATE TYPE emps_t AS TABLE OF REF emp_t;   2 /

Type created.

SQL>
SQL> CREATE TABLE emptab OF emp_t;

Table created.

SQL>
SQL> CREATE TABLE dept (dno NUMBER, employees EMPS_T)   2 NESTED TABLE employees STORE AS deptemps;

Table created.

SQL> 
SQL> 
SQL> insert into emptab values ( emp_t( 1, 'tom' ) );

1 row created.

SQL> insert into emptab values ( emp_t( 2, 'sue' ) );

1 row created.

SQL>
SQL> insert into dept values ( 10,

  2                                                  cast( multiset( select
ref(e) from emptab e )
  3                                                            as emps_t ) );

1 row created.

SQL> 
SQL> 
SQL> select OBJ_ALIAS.COL_ALIAS.eno from (

  2 select deref(column_value) COL_ALIAS   3 from the ( select employees from dept where dno = 10 )   4 ) OBJ_ALIAS
  5 /

COL_ALIAS.ENO

---

            1
            2

>this doesn't work - there is error message, that eno is bad column name.
>
>Thank you.
>
>Richard
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Apr 17 1998 - 09:19:33 CDT