Re: INSERT with ORDER BY workaround for Oracle <= 8.1.5 ?

From: Daniel Frankowski <dfrankow_at_winternet.com>
Date: 12 Apr 2003 06:15:10 GMT
Message-ID: <b78ate$2di$1@blackice.winternet.com>

Ed Prochak <edprochak_at_adelphia.net> writes:

>Daniel Frankowski wrote:
>> The SQL below works on Oracle 8.1.7, but not Oracle 8.1.5..

>Yuck. Honestly I do not see what you think you are saving here.

In the real example, the order-by is fairly complex, involving four columns and HAVING clause with a SUM(). Thus, I don't want to sprinkle that all sorts of future queries. I want bar.id to represent the ORDER-BY clause of the SELECT.

>I look at your ID field and think about the lines numbers in an old
>BASIC program.

>this ID that matches the sort order of the TXT field seems like such
>a kludge. What happens after you get the ID's assigned and someone
>finds they need to correct the record from 'jay' to 'may' or from
>'ted' to 'wed'.

Sometimes the order of things is important. Sometimes I want to save that order in a table for future use. I don't have a problem with that, except that it's not easily (or portably) expressible in SQL.

> What's the real problem you are trying to solve?

I didn't want to go there, so I abstracted the problem to the simplest query: INSERT with ORDER BY.

Thanks anyway. I followed the advice of the following post:

  http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=380393d0.0%40news1

which was essentially to use a FOR loop:

DECLARE
commit_counter PLS_INTEGER (3) :=0;

CURSOR get_em IS
SELECT *
FROM othertable
ORDER BY desc;

BEGIN
FOR ins_rows IN get_em
LOOP

           INSERT INTO table_name VALUES (ins_rows.col1, ins_rows.col2);
           IF commit_counter > 500 THEN
                   COMMIT;
                   commit_counter :=0;
           ELSE
                   commit_counter := commit_counter +1;
           END IF;

END LOOP;
END; Since the rows are being inserted one at a time in order, the trigger properly assigns the values. Since the INSERT contains no ORDER-BY, it works on Oracle < 8.1.7.

>Ed Prochak

Dan Received on Sat Apr 12 2003 - 01:15:10 CDT