Re: Understanding PL/SQL tables and such

From: sandeep pande <sandy.soft80_at_gmail.com>
Date: Mon, 10 May 2010 23:58:33 -0700 (PDT)
Message-ID: <a5cd959d-d507-4c22-adbf-3e72a55c2497_at_h20g2000prn.googlegroups.com>

On May 7, 8:50 pm, The Magnet <a..._at_unsu.com> wrote:
> Hi,
>
> I've created some code that uses PL/SQL type of OBJECT. I used an
> example I found.  The code works fine, but I am trying to understand
> exactly 'how' it works.  Please see this:
>
> create TYPE article_record_type AS OBJECT
>   (username         VARCHAR2(30),
>    user_id          NUMBER,
>    article_id       NUMBER,
>    teaser_subject   VARCHAR2(2000),
>    teaser_message   CLOB,
>    total_articles   NUMBER);
>
> create TYPE article_table_type IS TABLE OF article_record_type;
>
>     FOR v_rec IN commentary_data(v_author_id, v_num_articles) LOOP
>       v_article_record.EXTEND(1);
>       v_sub := v_sub + 1;
>       v_article_record(v_sub) := article_record_type(v_rec.username,
> v_rec.user_id, v_rec.article_id,
>
> v_rec.teaser_subject, v_rec.teaser_message,
>
> v_rec.total_articles);
>     END LOOP;
>
> OPEN p_data FOR SELECT * FROM TABLE (CAST (v_article_record AS
> article_table_type));
>
> My confusion this this:  I understand the CREATE TYPE commands.  I
> understand v_article_record being a table of article_table_type which
> is in itself a record of article_record_type.
>
> But where I assign values to v_article_record(v_sub), I do not
> understand how the values in article_record_type get there?
>
>       v_article_record(v_sub) := article_record_type(v_rec.username,
> v_rec.user_id, v_rec.article_id,
>
> v_rec.teaser_subject, v_rec.teaser_message,
>
> v_rec.total_articles);
>
> article_record_type(v_rec.username.........
>
> If v_article_record is of type article_table_type which is a table of
> article_record_type, then why can't I use say:
> _article_record(v_sub).username := v_rec.username.   Why does it have
> to be inside article_record_type.
>
> I'm just trying to see how Oracle is relating all of this and how it
> works and how the values relate to the OBJECT and such.
>
> Thanks!

Hi,
Pls try this

v_article_record(v_sub) := article_record_type(v_rec.username,
                                               v_rec.user_id,
                                               v_rec.article_id,
                                               v_rec.teaser_subject,
                                               v_rec.teaser_message,
                                               v_rec.total_articles);

Here, you are passing all the record values in a single statement instead of something like this

v_article_record(v_sub).username := v_rec.username;
v_article_record(v_sub).user_id := v_rec.user_id;
v_article_record(v_sub).article_id := v_rec.article_id;
v_article_record(v_sub).teaser_subject := v_rec.teaser_subject;
v_article_record(v_sub).teaser_message := v_rec.teaser_message;
v_article_record(v_sub).total_articles := v_rec.total_articles;

Thanks
Sandy Received on Tue May 11 2010 - 01:58:33 CDT