Get specified number of rows in Oracle 11g

I want to get number of rows in SQL. I use

select ROWNUM,FULL_NAME
from OSQS_INSTITUTION
order by FULL_NAME
WHERE rownum BETWEEN 10 AND 20

and try this

select ROWNUM,FULL_NAME
from OSQS_INSTITUTION
order by FULL_NAME limit 10,20

and both return the same error

SQL command not properly ended

EDIT:

    StringBuilder sqlStatement = new StringBuilder();
    sqlStatement.append("SELECT USER_NO,FULL_NAME,ACTIVE from");
    sqlStatement.append("(");
    sqlStatement.append(" SELECT USER_NO开发者_开发知识库,FULL_NAME,ACTIVE, ROW_NUMBER() OVER(ORDER BY full_name) rn");
    sqlStatement.append("FROM osqs_institution");
    sqlStatement.append(")WHERE rn BETWEEN ? AND ? ");

    PreparedStatement pstmt = null;
    pstmt = con.prepareStatement(sqlStatement.toString());
    pstmt.setInt(1, from);
    pstmt.setInt(2, to);
    rs = pstmt.executeQuery();

with required try and catch.

---

In the first version of your query you have specified the WHERE clause after the ORDER BY which is syntactically wrong.

The second version works in MySQL, but not in Oracle.

In either case you can't get any rows when you use it with a BETWEEN.

Instead try this:

WITH qry AS
(
    SELECT full_name, ROW_NUMBER() OVER(ORDER BY full_name) rn
        FROM osqs_institution
)
SELECT rn, full_name
  FROM qry
WHERE rn BETWEEN 10 AND 20 

If you don't want to use ROW_NUMBER function, then try this:

SELECT rn, full_name
  FROM
(
    SELECT full_name, ROWNUM AS rn 
        FROM (SELECT * FROM osqs_institution ORDER BY full_name)
)
WHERE  rn BETWEEN 10 AND 20