Re: order by not consistent

On Thu, 25 Jul 2002 15:37:17 -0400, "bgt0990" <btighe_at_neometrics.com> 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 =-----

Reading your code it is quite obvious you seem to use the word 'truncate' in a special meaning:
you never TRUNCATE the table you DELETE only PART of it. So what do you think is going to happen with the data you INSERT? The normal free list mechanisme applies, so your record can end up ANYWHERE. The ORDER BY you are using is completely useless, as the table is not EMPTY prior to the INSERT.
So, in conclusion: it is NOT a problem with the ORDER BY. This is how you could expect it to be working, given the table is not empty. There is only one true solution: You need to add an ORDER BY in your reports, as you NEVER will be able to guarantee the physical order of your records in your table.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jul 25 2002 - 22:54:37 CEST