1

How can I page results from a table with this schema?

CREATE TABLE "MY_TABLE"
(
   "NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
   "STATUS" VARCHAR2(20 BYTE),
   "DESCRIPTION" VARCHAR2(4000 BYTE)
   CONSTRAINT "MY_TABLE_PK" PRIMARY KEY ("NAME")
)
James P.
  • 19,313
  • 27
  • 97
  • 155
  • 3
    Check http://stackoverflow.com/questions/241622/paging-with-oracle . Is it what you are looking for ? – a1ex07 Nov 20 '11 at 17:22
  • What query do you want to add paging to? – Adam Wenger Nov 20 '11 at 17:23
  • @a1ex07 Perhaps, but it's not clear to me how rownum works. – James P. Nov 20 '11 at 17:26
  • @AdamWenger It's a simple "select *" to map rows to domain objects so they can be displayed in a GUI. – James P. Nov 20 '11 at 17:28
  • 1
    @AdamWenger: Take a look :http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm . Briefly, `rownum` returns a number in which a row appears in result. So, to get first 10 rows you just append `WHERE rownum <11`. Keep in mind that you cannot do `WHERE rownum>10` to get all but first 10 results (rownum > postive number is always false), so you need to use derived tables. – a1ex07 Nov 20 '11 at 17:38
  • Thanks @a1ex07 I took a look at the links in your comments. I'm not very familiar with Oracle, but I'll surely reference these as I move forward. – Adam Wenger Nov 20 '11 at 17:44
  • @AdamWenger If you're up for some experimenting, it's possible to download an express version of Oracle. There is also a great front-end called Sql Developer which saves having to go through the console or web interface. – James P. Nov 20 '11 at 17:47
  • Thanks, I'll look into that. Currently, I can only be so confident with some of my answers for other SQL versions here when I am testing against `SQL Server 2012 CTP3` – Adam Wenger Nov 20 '11 at 17:52

2 Answers2

3

Basing my answer off the suggestion in the question comments, this should work for your table.

SELECT r.Name, r.Status. r.Description
FROM
(
    SELECT t.Name, t.Status. t.Description, rownum RowNumber
    FROM
    (
        SELECT Name, Status, Description
        FROM YourTable
        ORDER BY Name ASC
    ) AS t
    WHERE rownum < (pageNumber * pageSize) + 1
) AS r
WHERE RowNumber >= ((pageNumber - 1) * pageSize) + 1

A good explanation of what rownum does can be found on AskTom.Oracle.com

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • Thanks. Do you know what rownum does exactly? Running `SELECT * FROM MY_TABLE WHERE rownum = 1` gives a result but any numbers higher than 1 turn up empty. So it's apparently not an index. – James P. Nov 20 '11 at 17:38
  • 1
    I've added a link to a good explanation to your question in my answer. It does a better job explaining it than I could here. – Adam Wenger Nov 20 '11 at 17:42
  • Thanks. It works great and works flawlessly on the application side :) – James P. Nov 24 '11 at 11:30
0

Perhaps something in your select-statement, 

SELECT * FROM MY_TABLE ORDER BY NAME LIMIT ($curpage * 10), 10

$curpage being the current page, and the 10 multiplied by that being how many records you want to show per page.

lfxgroove
  • 3,778
  • 2
  • 23
  • 33
  • Good suggestion. Oracle 11g doesn't seem to accept LIMITs though: `00933. 00000 - "SQL command not properly ended"`. In fact, Oracle seems to be missing a few things that are taken for granted on other databases :) – James P. Nov 20 '11 at 17:30