We help IT Professionals succeed at work.
Get Started

how to select a large numebr ofo rows in sql developer

itortu asked
Last Modified: 2012-05-09

i am querying the database for a total number of 146,407 rows.

everytime i run my query, sql developr freezes up due to the large number of records i assume.

i would like to find out a way that i can retrieve all the records that i need, avoiding sql developer freezing up, and at the same time been able to obtain the total number of rows, that is 146,407

i think i do not need to retreive the whole 146,407 rows at once, i just need to make sure i get them all, even if it in parts, and not repeating or skipping any.

please any help on how to achieve this would be awesome.

i am providing the query i am using currently.

thank you so much!
set define off;
SELECT '' AS "Internal Field Name", r.dDocName AS "System ID" , r.dDocTitle AS "Title", r.dRevLabel AS "Revision", m.XCOMMENTS AS "Description"
, m.XIDENTIFIER AS "Identifier", r.dDocType AS "Content Type" , m.XSUBTYPE AS "Content Sub Type", r.dSecurityGroup AS "Data Classification"
, r.dDocAccount AS "Security Account", m.XISCONFIDENTIAL AS "Is Confidential", m.xSupplementalMarkings AS "Confidential Group", m.xIsExternal AS "Is External"
, m.xExternalLocation AS "External Location", m.xExternalLocation2 AS "External Container", d.dOriginalName AS "Native File", '' AS "Alternate File"
, m.XLEGALENTITY AS "Legal Entity", 'Chief Financial Office & Treas' AS "Business Unit", 'Corporate Controller' As "Department", m.XCREATOR As "Creator"
, m.XCONTRIBUTOR AS "Contributor", r.dDocAuthor AS "User ID", m.xIsVital AS "Subject to Review", m.xVitalReviewer AS "Reviewer", m.xVitalPeriod AS "Review Period"
, m.xVitalPeriodUnits AS "Review Period Units", m.XSUBJECT1 AS "Subject 1", m.XSUBJECT2 AS "Subject 2", m.XSUBJECT3 AS "Subject 3", m.XSUBJECT4 AS "Subject 4", m.XSUBJECT5 AS "Subject 5"
, m.xIsRecord AS "Is Record" , m.xCategoryID AS "FRS" , TO_CHAR(m.xRecordActivationDate,'MM/DD/YYYY HH:MI AM')  AS "Record Activation Date", TO_CHAR(m.xRecordExpirationDate,'MM/DD/YYYY HH:MI AM')  AS "Record Expiration Date"
, TO_CHAR(m.xPublicationDate,'MM/DD/YYYY HH:MI AM')  AS "Creation Date", TO_CHAR(r.dInDate,'MM/DD/YYYY HH:MI:SS AM')  AS "Release Date", r.dID As "dID"
WHERE r.dID = m.dID AND r.dID = d.dID AND d.dIsPrimary = 1 AND r.dreleasestate = 'Y'
AND m.XBUSINESSUNIT = 'Chief Financial Officer' AND m.XDEPARTMENT = 'Corporate Controller'
ORDER BY r.dDocName ASC, r.dRevLabel ASC;

Open in new window

Watch Question
Information Technology Specialist
This problem has been solved!
Unlock 3 Answers and 16 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE