display the nth row
Is there a function in Oracle 10g to display the nth row on a table in the
default select sequence? Say, if i have 1000 rows in table EMP, I want
to display the ROWID of the 500th row. In the command "select rownum,rowid from EMP";
Can you help me please.
Last edited by jennifer2007; 05-14-2007 at 08:08 PM.
There is no default sequence. It should be considere random.
Maybe where rownum=500 should do, but still that does not makes much sence to me
The reason for this is...I am exporting EMP table (oracle) to FOXPRO db
coz we still have foxpro apps on small branches which runs on win98 pentium2, which oracle can not.
So, when i export it via odbc (msssql) I hit an error on row number 198,200
date error, and i am loading 300,000+ rows. So i want to investigate what is
on this specific row and remedy it.
rows in oracle anrent in any order, you get them in whatever order the database wants to give you them
Does the export breaks,or the import?
Try tracing the oracle session or generate some logs from the utility
Oh my...you dont get me hunnies
its not export or import using oracle....
The scenario is:
1. I have EMP table in oracle witn 300,000+ rows.
2. I want to convert to to foxpro.dbf. (i have done this to other tables
and they are successful except this EMP).
The easiest way to convert is using the odbc export utility in MSSQL server.
there is an export util there (its not an oracle export). where u can export
any database to other database using the odbc DSN.
i just hit error on the 119,823rd row. and i want to display all info about this
row for investigation.
"rows in oracle anrent in any order, you get them in whatever order the database wants to give you them" - davey
Of course I only want to get the default sequence and it wont change
the order of it even if u run hundred times. the EMP is just staging table,
no other is using it.
anyway i make research in pl/sql and i made this program:
CURSOR c1 IS
SELECT rowid, rownum from EMP;
FETCH c1 INTO a_rowid, a_rownum;
if a_rownum between 119822 and 119824
INSERT INTO temp VALUES (a_rowid,a_rownum);
But this program runs very long time and eats lots of CPU. can u tune
this one please
Last edited by jennifer2007; 05-14-2007 at 08:11 PM.
I got U very well dear, have u seen me mentioning Oracle IMP? I was just asking which part fails to know should we debug Oracle or the dbf file making.
Still, I suppose the utility u use has some logs to look at.
About the procedure, what about mooving the commit out of the loop?
Use the bulk collect feature, that will for sure speed the procedure
Thanks hunnies....anyway my brilliant mind got an answer for me
Did u know that pl/sql reading 300,000 EMP rows took 2hrs? to process
But when i use this:
insert into temp select decode(rownum,119823,rowid,'') from EMP;
DELETE FROM TEMP where a_rowid is null:
just 5 seconds!
Last edited by jennifer2007; 05-12-2007 at 05:09 AM.
Did that solved your issue?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width