PL/SQL VARRAY help

I have created a VARRAY of a type and i'm trying to fire SQL queries on them.

This is what I have done

Code:

---

CREATE TYPE blog_post AS OBJECT
(
blogname varchar(20)
);
/

CREATE TYPE blog_posts_arr
AS VARRAY(20) OF blog_post;
/

CREATE TABLE topic
(
topic_id VARCHAR(20),
name VARCHAR(20),
time TIMESTAMP,
datetop DATE,
blog_posts blog_posts_arr,
PRIMARY KEY (topic_id)
);

---

Now, I have inserted some values in this table as

Code:

---

INSERT INTO topic VALUES( 't3', 'football', NULL, NULL, blog_posts_arr(
                                                                        blog_post('p25')
                                                                        ));

INSERT INTO topic VALUES( 't4', 'golf', NULL, NULL, blog_posts_arr( blog_post('p24'),
                                                                    blog_post('p8')
                                                                    ));

INSERT INTO topic VALUES( 't5', 'tennis', NULL, NULL, blog_posts_arr(  blog_post('p1'),
                                                                        blog_post('p18'),
                                                                        blog_post('p19'),
                                                                        blog_post('p20'),
                                                                        blog_post('p21')
                                                                        ));

---

I am now trying to fire an SQL query on this table which will return me the topic name and the number of blog_posts associated with the topic in the decreasing order of the blog posts.

Can someone please help me write this query?

I tried getting the count of blogs in a topic using

Code:

---

select
  count(t.blog_posts)
from
  topic t,
  table(t.blog_posts);

---

But this doesnt work. How should my sql look like?

Hi

Code:

---

select count(var.blogname)
from topic t,table(t.blog_posts) var


/

COUNT(VAR.BLOGNAME)
-------------------
                  8

---

thanks for create table and insert statements

Ok. Thanks Hrishy.

Could you suggest me one good SQL tutorial please. I know there are a lot available only but want to know if there is one you think is particularly good.