HTML entity decoding with oracle DBMS_XMLGEN.convert

The rownum pseudocolumn is assigned as rows are processed. The predicate rownum = <<x>> will evaluate to false if <<x>> is anything other than 1.

Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like

1. Read a row from table1
2. Evaluate the col2 = 'val' expression
3. If col2 = 'val', evaluate the extract
4. Assign the rownum to the row that has been returned. Since no other rows have satisfied all the criteria yet, the rownum will be 1
5. Evaluate the rownum = <<x>> predicate. If <<x>> is anything other than 1, the row is rejected
6. Go back to step 1 and read the next row

In this loop, every row will be read from table1. And every row will be rejected (assuming <<x>> is something other than 1) because of the rownum = <<x>> predicate. So you'll incur the cost of reading every row from the table, evaluating the extract for every row, and you'll never return any data.

Your first query appears to be working because it is filtering out <<x>> + 1 rows in the subquery. Of course, since there is no ORDER BY, your first query always returns an arbitrary row. It would be entirely legal for Oracle to return the same row for every value of <<x>> in your first query. It probably won't, of course, but it is entirely possible that you'd get different rows over time, that some rows would never be returned, that other rows would be returned multiple times, etc. If you want a deterministic result (prior to 12.1 which has some simpler syntax options), you'd need to do two levels of nesting

SELECT *
  FROM (SELECT b.*,
               rownum rn
          FROM (SELECT a.*
                  FROM some_table a
                 ORDER BY some_column) b
         WHERE rownum <= <<upper_limit>>) c
 WHERE rn >= <<lower_limit>>

For a pretty thorough discussion of the various issues, take a look through this askTom thread.

The chinese characters are decoded correctly with utl_i18n.unescape_reference. In fact they just aren't displayed properly in your query result which might not support those special characters.

You can confirm that with this SQL Fiddle.

It is the client which is respsonsible to display the characters appropriately. If the client can not display a character it may show an upside down question mark, something else or just plain garbage.

So by now, the real question is where do you want to display this string...