- Oracle PL/SQL
- Oracle Database
- Query Syntax
- SQL
how to select a large numebr ofo rows in sql developer
hi,
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!
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"
FROM REVISIONS r, DOCMETA m, DOCUMENTS d
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;
You could get 10000 rows at a time, e.g.
FROM REVISIONS r, DOCMETA m, DOCUMENTS d
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'
AND ROWNUM BETWEEN 1 and 10000;
ORDER BY r.dDocName ASC, r.dRevLabel ASC;
Now this assumes the data is static from when you start 1-10000, 10001-20000 all the way until the last record.
FROM REVISIONS r, DOCMETA m, DOCUMENTS d
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'
AND ROWNUM BETWEEN 1 and 10000;
ORDER BY r.dDocName ASC, r.dRevLabel ASC;
Now this assumes the data is static from when you start 1-10000, 10001-20000 all the way until the last record.
What do mean by freezing up? Do you check processes in task manager and you're sure it has frozen and not just taking a while to retrieve the rows?
How long do you allow before giving up?
R.
How long do you allow before giving up?
R.
i will give that a try cyberkiwi, thank you.
rolutola, i have waited about an hour to retrieve the records, and they do come up after a long time waiting. then the issue comes again when i try to export the records to xls, or text. it freezes again, i have waited more than an hour and is just goes all blank. I have also checked all the processes, and killed most of them to free up so memory. thank you.
rolutola, i have waited about an hour to retrieve the records, and they do come up after a long time waiting. then the issue comes again when i try to export the records to xls, or text. it freezes again, i have waited more than an hour and is just goes all blank. I have also checked all the processes, and killed most of them to free up so memory. thank you.
This could be due to lack (or use) of indexes. It appears that SQL Developer is accessing the server again during the export (to excel) process. Have you tried running this in SQL*Plus for comparison? Set timing on for the test.
R.
R.
i was able to run AND ROWNUM BETWEEN 1 and 25000;
but when i run
AND ROWNUM BETWEEN 25001 and 50001;
i get nothing.
but when i run
AND ROWNUM BETWEEN 25001 and 50001;
i get nothing.
You have to first alias the rownum in the inner query before you can filter it.
If the base query is
select a,b,c,d
from e,f,g
where e.i=f.j and f.k=g.l
order by e.d asc
The the paging works like this
select a,b,c,d from (
select a,b,c,d, rownum as rn
from e,f,g
where e.i=f.j and f.k=g.l
and rownum <= 50000
order by e.d asc
) sq
where rn between 25001 and 50000
order by d asc
Rownum is interesting because you can limit it, say <=50000, but if is try to skip the first row, say rownum=2, it does not work because without the first row, there is no 2nd row.
If the base query is
select a,b,c,d
from e,f,g
where e.i=f.j and f.k=g.l
order by e.d asc
The the paging works like this
select a,b,c,d from (
select a,b,c,d, rownum as rn
from e,f,g
where e.i=f.j and f.k=g.l
and rownum <= 50000
order by e.d asc
) sq
where rn between 25001 and 50000
order by d asc
Rownum is interesting because you can limit it, say <=50000, but if is try to skip the first row, say rownum=2, it does not work because without the first row, there is no 2nd row.
What version of SQL Dev are you using?
You have to realize that returning 150k records can put a severe strain on your system, which is why it freezes up. However, you can still change the number of rows returned in this manner:
Tools / Preferences / Database / Advanced / SQL Array Fetch Size
If you need to export to Excel, why not just run the query directly from Excel instead of copy/paste from SQL Dev?
You have to realize that returning 150k records can put a severe strain on your system, which is why it freezes up. However, you can still change the number of rows returned in this manner:
Tools / Preferences / Database / Advanced / SQL Array Fetch Size
If you need to export to Excel, why not just run the query directly from Excel instead of copy/paste from SQL Dev?
I agree with gatorvip, there are are better ways to get data into Excel than by querying all of the rows in SQL Developer first. Having Excel use an ODBC connection to query Oracle directly is one option. Another option is to use the supplied PL\SQL package: utl_file, to have a procedure write a comma-delimitted or fixed-length ASCII file that Excel can then easily import. (Note that utl_file can only write to a disk on the server, so you may need to copy the ASCII file from the database server to a client before Excel can open it, if you use this option.)
I would like to query the records directly from excel, the problem is that these are over 100,000 records, and I think excel has a limit in the number of rows, isn't?
Older versions of Excel (like the version with Windows95) had a much smaller limit (I think it was 16,000 records). I don't know which version of Excel you have now, and i don't know what the current Excel limits are.
If you use a PL\SQL procedure to create an ASCII file, you could have it limit the number of records to whatever number you want, then continue in a second file, etc. if the database has more records than Excel can handle in a single file.
If you use a PL\SQL procedure to create an ASCII file, you could have it limit the number of records to whatever number you want, then continue in a second file, etc. if the database has more records than Excel can handle in a single file.
the problem is that i am not sure how to change the current query i use to retrieve the records, to a stored procedure and how to write the ascii file. is there any way i could get an example on how to do this? thank you.
Excel 2007
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
Worksheet size 1,048,576 rows by 16,384 columns
Prior versions (through 97) could only handle 64k rows
http://support.microsoft.com/kb/120596
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
Worksheet size 1,048,576 rows by 16,384 columns
Prior versions (through 97) could only handle 64k rows
http://support.microsoft.com/kb/120596
Using rownum with a subquery is way to force an Oracle database to return a "page" of records, but this approach can cause performance problems, since it forces a full-table scan of the underlying records.
Here are two examples PL\SQL procedures, first one that uses that uses utl_file, then one that uses a cursor loop.
create or replace procedure sample (path_nm in varchar2, file_nm in varchar2,
lines_to_read in number default 3, chars_per_record in number default 999,
output_path in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
-- and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
-- be opened and browsed more easily in a text editor.
in_file utl_file.file_type;
out_file utl_file.file_type;
text_str varchar2(4000);
lines pls_integer;
err_text varchar2(200);
begin
lines := 0;
out_file := utl_file.fopen(output_path
in_file := utl_file.fopen(path_nm,fil
while lines < lines_to_read loop
begin
utl_file.get_line(in_file,
utl_file.put_line(out_file
lines := lines +1;
exception
when others then
err_text := substr(sqlerrm,1,200);
lines := lines +1;
utl_file.put_line(out_file
end;
end loop;
utl_file.fclose(in_file);
utl_file.fclose(out_file);
end;
/
--replace the square brackets [] and the text between them with meaningful values
create or replace procedure [procedure_name] as
cursor c1 is select [column(s)]
from ...
where ...
-- set up variables here to hold values to be selected
begin
open c1;
loop
fetch c1 into [variable(s)];
exit when c1%notfound;
counter := counter +1;
total := total +1;
-- put main logic here
end loop;
close c1;
end;
/
Here are two examples PL\SQL procedures, first one that uses that uses utl_file, then one that uses a cursor loop.
create or replace procedure sample (path_nm in varchar2, file_nm in varchar2,
lines_to_read in number default 3, chars_per_record in number default 999,
output_path in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
-- and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
-- be opened and browsed more easily in a text editor.
in_file utl_file.file_type;
out_file utl_file.file_type;
text_str varchar2(4000);
lines pls_integer;
err_text varchar2(200);
begin
lines := 0;
out_file := utl_file.fopen(output_path
in_file := utl_file.fopen(path_nm,fil
while lines < lines_to_read loop
begin
utl_file.get_line(in_file,
utl_file.put_line(out_file
lines := lines +1;
exception
when others then
err_text := substr(sqlerrm,1,200);
lines := lines +1;
utl_file.put_line(out_file
end;
end loop;
utl_file.fclose(in_file);
utl_file.fclose(out_file);
end;
/
--replace the square brackets [] and the text between them with meaningful values
create or replace procedure [procedure_name] as
cursor c1 is select [column(s)]
from ...
where ...
-- set up variables here to hold values to be selected
begin
open c1;
loop
fetch c1 into [variable(s)];
exit when c1%notfound;
counter := counter +1;
total := total +1;
-- put main logic here
end loop;
close c1;
end;
/
-
![]()
received a Solution
PL/SQL Limit number of rows returned by query -
![]()
wrote an Article
![]()
How to fix OLE DB connection error while connecting Oracle database using SSIS
-
![]()
created a Video
![]()
Query Current Session Information via SQL
-
Explore Cloud Class® ![]()
Introduction to Oracle SQL
Premium Content
You need an Expert Office subscription to comment.Start Free Trial