Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SELECT Statement on a table that contains an object

Re: SELECT Statement on a table that contains an object

From: <romeo.olympia_at_gmail.com>
Date: Mon, 18 Jun 2007 09:56:31 -0000
Message-ID: <1182160591.577719.131650@a26g2000pre.googlegroups.com>


On Jun 18, 4:03 pm, "Micha" <p..._at_gmx.li> wrote:
> 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

Just alias the table and use that in the select clause. Eg.

SELECT a.email.hostname
FROM EMAIL_QUEUE a
where ID = 1;

HTH. Received on Mon Jun 18 2007 - 04:56:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US