SELECT Statement on a table that contains an object

Im working on an SELECT Statement to get values from a table.

The table is:
CREATE TABLE EMAIL_QUEUE
(ID NUMBER,SEND VARCHAR2(1 BYTE),
EMAIL TYPE_EMAIL_OBJECT, TIME_TO_SEND TIMESTAMP(3) WITH LOCAL TIME ZONE,RETRY_ATTEMPTS NUMBER,INSERT_TS TIMESTAMP(3) WITH LOCAL TIME ZONE) Email Object is:

CREATE OR REPLACE TYPE CP_DEVELOP.TYPE_EMAIL_OBJECT AS OBJECT ( hostname VARCHAR2(4000),

port number,sender_address varchar2(4000),sender_name varchar2(4000),subject varchar2(4000),

recipients TYPE_recipients_list,

email_text varchar2_table,char_set varchar2(100),language varchar2(100)

);

i have left the nested table declarations (here).

So a dataset of the table EMAIL_QUEUE has an id, send (flag) and an email object (and some more, but not interesting here).

I want to make a SELECT Statement to select eg the hostname from an email_object.

Something like that:

SELECT email.hostname FROM EMAIL_QUEUE where ID = 1.

This wont work.

What is the correct Syntax to select membervalues of an object in a SELECT Statement.

(The Solution with an SELECT ... INTO email_object works,but i must get it
direct (without declaring an object variable first).

Greets

Micha Received on Mon Jun 18 2007 - 03:03:42 CDT