Without Oracle, you'd need to read all the rows, rearrange them in memory and then output them. But that's not trivial.

First, I'd select all the rows in an way that meant you never saw a child before it's parent:

SELECT category_id, category_name, main_category
  FROM test
  ORDER BY main_category, category_id
[download]

Then I'd store the results in a hash keyed on category_id where each element was a reference to an array containing the category_name, and an array of child category_id's (initially empty):

  $category{$category_id} = [ $category_name, [] ];
[download]

and (as long as the main_category_id was not 0?) I'd add the category_id onto the end of the list of the parent's children:

push @{$category{$main_category}->[1]}, $category_id;
[download]

which would give a hash like this:

%category = (
  1 => [ 'One',      [5, 6, 7] ],
  2 => [ 'Two',      [8, 9] ],
  3 => [ 'Three',    [] ],
  4 => [ 'Four',     [] ],
  5 => [ 'Six',      [10, 13] ],
  6 => [ 'Seven',    [] ],
  7 => [ 'Eight',    [] ],
  ...
);
[download]

Then you have a structure you can recurse down through (but you need to know where to start - I guess that means when you fetch the query results you need to keep track of the top level categories):

  sub print_categories {
    my $level = shift;
    foreach my $id (@_) {
      print "$level: $category{$id}->[0]\n";
      print_categories($level + 1, @{$category{$id}->[0]});
    }
  }

  print_categories(0, @top_level_categories);
[download]

Of course that will look pretty much like gibberish if you don't have a solid grasp of Perl references - I did say it wasn't trivial. Also, the code snippets are not tested so there are likely bugs there (in addition to the obvious holes like actually executing the query and fetching the results).

Update: On second thoughts, that SELECT probably doesn't guarantee to give you the parents before the children. I can't think about it anymore, it's late and my head hurts :-) Good night!

Update 2: OK, I've slept on it, the SELECT definitely doesn't guarantee to give you the parents before the children (although it might happen to have that effect for the data set you quoted). Rather than trying to put the name and the children in one hash, it would be easier to use one hash for each. When processing an item, the category_id would need to be appended to the parent's list of children, you'd just need to create the list first if it didn't already exist.