Understanding PL/SQL tables and such

From: The Magnet <art_at_unsu.com>
Date: Fri, 7 May 2010 08:50:02 -0700 (PDT)
Message-ID: <607256c6-74e4-4972-9865-94b94a62afc8_at_i9g2000yqi.googlegroups.com>

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! Received on Fri May 07 2010 - 10:50:02 CDT