display the nth row
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: display the nth row

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    display the nth row

    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.
    Last edited by jennifer2007; 05-14-2007 at 09:08 PM.

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    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.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    rows in oracle anrent in any order, you get them in whatever order the database wants to give you them

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Dear,
    Does the export breaks,or the import?
    Try tracing the oracle session or generate some logs from the utility

    Cheers

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    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:
    ===========================================

    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

    Thanks
    Last edited by jennifer2007; 05-14-2007 at 09:11 PM.

  7. #7
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  8. #8
    Join Date
    May 2007
    Posts
    11
    Use the bulk collect feature, that will for sure speed the procedure

  9. #9
    Join Date
    Feb 2007
    Posts
    212
    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
    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!
    Last edited by jennifer2007; 05-12-2007 at 06:09 AM.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width