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: Micha <pen_at_gmx.li>
Date: Fri, 22 Jun 2007 10:56:44 +0200
Message-ID: <5e1h6uF36jn12U1@mid.dfncis.de>

<romeo.olympia_at_gmail.com> schrieb im Newsbeitrag news:1182160591.577719.131650_at_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.
>

Thnx, that works^^
But how can i select the first (second) row from the attribute "email_text"? SELECT a.email.email_text (1) FROM EMAIL_QUEUE a where ID = 1;

wont work?

Greets Micha Received on Fri Jun 22 2007 - 03:56:44 CDT

Original text of this message

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