CODEWITHSUNDEEP
phpsqloracle-database
ITProblems
ITProblems

Reputation: 37

How to query a limited number of rows from an Oracle database?

I have a large Oracle database with a large number of records that I need to process. I am validating the data within PHP and then generating XML code from the validated array of data. Due to the large number of results, the XML files become too large to be feasible to process. I am trying to break down the function to run 3 separate times and generate 3 separate XML files. I am trying to run the same query 3 times but each with a different WHILE statement at the end. I tried making use of the rownum method, but didnt realize that that you cannot do a rownum > x and rownum <y format. Anyone have any ideas? Here is a snippet of the code I tried running: 

SELECT *
FROM useradmin.VSED_UNVALIDATED_VW tbl_vsed_unvalidated
WHERE (rownum < 10001)

and then another query with:

SELECT *
FROM useradmin.VSED_UNVALIDATED_VW tbl_vsed_unvalidated
WHERE ((rownum > 10000)
AND rownum < 20001)

Upvotes: 1

Views: 183

Answers (3)

Slava Lenskyy
Slava Lenskyy

Reputation: 456

You are looking for pagination. It can be done in oracle like that.

To understand it you should read about rownum pseudocolumn (In case you just don't want to read more data than is actually needed).

Oracle Doc 

select *
  from (select *
          from (select ua.*, rownum r
                  from useradmin ua)
         where rownum < MAX_VALUE)
 where r > MIN_VALUE

Upvotes: 0

Randy
Randy

Reputation: 16677

select * from 
( select col1, col2, row_number() over (order by col1) r from tbl_vsed_unvalidated)
where r between 10000 and 20001

Upvotes: 1

AbraCadaver
AbraCadaver

Reputation: 78994

Try something like this:

SELECT * FROM
  (SELECT *, rownum r FROM useradmin.VSED_UNVALIDATED_VW tbl_vsed_unvalidated)
WHERE r > 10001 AND r < 20001

You could build a little function to reuse:

function select_limit($table, $min, $max) {
   $sql = "SELECT * FROM
            (SELECT *, rownum r FROM $table)
           WHERE r > $min AND r < $max";

   //return query result
}

Upvotes: 0

Related Questions