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

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 11 Apr 2003 04:48:21 GMT
Message-ID: <3E964D02.4080104@adelphia.net>

Daniel Frankowski wrote:
> The SQL below works on Oracle 8.1.7, but not Oracle 8.1.5. The
> problem is the "ORDER BY" clause of the last line. If I remove that
> line, it works in both cases, but with the wrong meaning for me.
[]
> My current plan is to change to some sort of PL/SQL FOR loop, but I
> fear that will be deadly slow.

[]
> I also looked around in Oracle docs some without success.
>
> Does Oracle document some workaround for earlier versions?
> Other ideas?
>
> Dan
>
>
>
> create table foo (txt varchar(80))
> create table bar (id int, txt varchar(80))
> insert into foo (txt) values ('m')
> insert into foo (txt) values ('a')
> insert into foo (txt) values ('z')
>

[Please set a wrap margin. Your trigger came out all on one line]

> create sequence foo_seq start with 1 increment by 1 cache 200 order
> CREATE TRIGGER AUTONUMBER_BAR BEFORE INSERT ON BAR for each row

 > declare pkvalue number;
 > begin select foo_SEQ.nextval into pkvalue from dual;
 > :NEW.id := pkvalue; end ;

>
> update bar set id = null
> insert into bar (txt)
> select txt
> from foo
> order by txt
>

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

  You'll have to do updates like this after every insert if the table is dynamic (ie, rows are added or removed in production). If this is a static table (seldom are rows inserted or deleted), then why worry about a potentially slow update.

Alternative: make the primary key (wince) a CHAR field and use something like the first 10-20 characters of the text, or maybe a SOUNDEX() index. Maybe still not unique, try making the primary key the TXT field itself?

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'.

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

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Thu Apr 10 2003 - 23:48:21 CDT