Printable View
Hi Friends,
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. :)
Jenn,
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
Regards
Boris
Thanks honey,
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
Dear,
Does the export breaks,or the import?
Try tracing the oracle session or generate some logs from the utility
Cheers
Oh my...you dont get me hunnies:rolleyes:
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:
===========================================
DECLARE
a_rowid varchar2(18);
a_rownum number;
CURSOR c1 IS
SELECT rowid, rownum from EMP;
BEGIN
open c1;
LOOP
FETCH c1 INTO a_rowid, a_rownum;
if a_rownum between 119822 and 119824
then
INSERT INTO temp VALUES (a_rowid,a_rownum);
end if;
COMMIT;
END LOOP;
close c1;
END;
/
But this program runs very long time and eats lots of CPU. can u tune
this one please:confused:
Thanks
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?
Regards
Use the bulk collect feature, that will for sure speed the procedure
Thanks hunnies....anyway my brilliant mind got an answer for me:D
Did u know that pl/sql reading 300,000 EMP rows took 2hrs? to process
simple select?
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!:p
Did that solved your issue?
yeahh dear....by the way I put the wrong code and edited it.Quote:
Originally Posted by PAVB
Jefff, why cant i open ur link? it keeps on telling me "This domain is no longer parked." .....maybe i got virus in my pc:confused:
Can any one print marist link solution pls. thanks
dunno, works for me.Quote:
Originally Posted by jennifer2007
That's not cool, I wouldn't be happy about that.Quote:
Can any one print marist link solution pls. thanks
:rolleyes: weird!Quote:
Originally Posted by jennifer2007
Sorry dear I dont want to embarrass Jeff ;)
ok its displaying now....but.... it is using ORDER BY:confused: i think i dont
understand it well or it doesnt fit to my requirement? my solution works
fine but I dont wanna insert lots of null values to a temp table:rolleyes: