Re: order by not consistent

bgt0990 wrote:

> Oracle entreprise 8.1.7.4
>
> I am trying to load output into tables for use by other programs (crystal
> reports etc)
>
> If I truncate my destination table prior to running the procedure the output
> 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,
> but the first few (varies greatly) records show up at the end of the table.
> Most of my routines are useing ref cursors I wonder if that is part of the
> answer.
>
> 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;
> 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;
>
> SQLString := 'SELECT distinct labno FROM RESULT_MASTER WHERE BATCHID =
> :1';
> 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 =-----

[Quoted] Can't answer the question about the ORDER BY but I can tell you that you have [Quoted] chosen one of the least performance and scalability friendly methods of accomplishing your goal I can imagine. Perhaps you thing that by passing in [Quoted] o1Order you are altering something. But since you never use vSort for anything it [Quoted] is a dead-end parameter.

Either way ... based on your code all you really need is the following:

BEGIN
   DELETE FROM RPT_RESULT_MASTER t
   WHERE UPPER(t.KEY) = UPPER(aKey);

   INSERT INTO RPT_RESULT_MASTER
   SELECT akey, C0BatchID, labno
   FROM result_master
   WHERE batchid = C0BatchID
   AND testseq < 99
   ORDER BY LABNO;
   COMMIT;
END; And I am still left wondering why you don't just run your report from the RESULT_MASTER table instead of even doing this.

Daniel Morgan Received on Thu Jul 25 2002 - 21:59:39 CEST