order by not consistent

From: bgt0990 <btighe_at_neometrics.com>
Date: Thu, 25 Jul 2002 15:37:17 -0400
Message-ID: <3d40517a$1_4_at_corp-goliath.newsgroups.com>



[Quoted] Oracle entreprise 8.1.7.4

[Quoted] [Quoted] I am trying to load output into tables for use by other programs (crystal [Quoted] reports etc)

If I truncate my destination table prior to running the procedure the output [Quoted] always seems ordered properly 1, 2, 3, 4, 5, etc. However on successive runs the order invariably gets messed up 3,4,5,1,2 it's still ordered, [Quoted] but the first few (varies greatly) records show up at the end of the table. [Quoted] Most of my routines are useing ref cursors I wonder if that is part of the answer.

[Quoted] I include some sample code, but I've got some 30 procedures where this all acts the same.

PROCEDURE P_PUNCH_LIST (
 aKey IN VARCHAR2,
 C0BatchID IN CHAR,
 o1Order IN CHAR )
AS

  TYPE rc_type IS REF CURSOR;
[Quoted]   l_cursor rc_type;

  CURSOR l_template IS SELECT labno FROM RESULT_MASTER;

  c1rec l_template%ROWTYPE;

  SQLSTRING     VARCHAR2 (2000);
  vSort       VARCHAR2(10):= 'LABNO';


  BEGIN
    DELETE FROM RPT_RESULT_MASTER t WHERE UPPER(t.KEY) = UPPER(aKey);  commit;
 IF o1Order IS NOT NULL THEN
   vSort:= trim(UPPER(o1Order));
 END IF; [Quoted]      SQLString := 'SELECT distinct labno FROM RESULT_MASTER WHERE BATCHID = :1';
[Quoted] [Quoted]  SQLSTRING := sqlstring ||' AND TESTSEQ < 99';  SQLSTRING := sqlstring ||' ORDER BY LABNO';

 OPEN l_cursor FOR SQLString
   using C0BatchID;

    LOOP
    FETCH l_cursor INTO c1rec;
    EXIT WHEN l_cursor%NOTFOUND;

    INSERT INTO RPT_RESULT_MASTER
(KEY, batchid, labno)

  VALUES
(akey, C0BatchID, c1rec.labno );

    END LOOP;
    CLOSE l_cursor;
 commit;
 END; Any help would be appreciated
Barry

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Thu Jul 25 2002 - 21:37:17 CEST

Original text of this message