PL/SQL VARRAY help

[lazyme]

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

This is what I have done

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

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

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

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

[hrishy]

Hi

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


/

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

thanks for create table and insert statements

[lazyme]

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.