Re: SELECT Statement on a table that contains an object

On Jun 22, 4:56 pm, "Micha" <p..._at_gmx.li> wrote:
> <romeo.olym..._at_gmail.com> schrieb im Newsbeitragnews: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

EMAIL_TEXT is a nested table so you deal with it as such.

select *
from table (

   select a.email.email_text
   from email_queue a
);

As for the specific ordering, you're gonna need some other way to order by than just subscripts (maybe, a timestamp column or something).

Please check the PL/SQL and Object-Relational docs; all these are there.

And design-wise, do you really need to store all these as objects in the database instead of just implementing them as relational tables? Received on Sun Jun 24 2007 - 22:09:54 CDT