Prev: Target and Auxiliary have different OS and version of Oracle
Next: Who is blocking the record I want to edit?
From: The Magnet on 7 May 2010 11:50
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!
From: sandeep pande on 11 May 2010 02:58
On May 7, 8:50 pm, The Magnet <a...(a)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
 | 
Pages: 1
Prev: Target and Auxiliary have different OS and version of Oracle
Next: Who is blocking the record I want to edit?