Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Guaranteed Order With a Sequence / Trigger

Re: Guaranteed Order With a Sequence / Trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 25 Oct 1999 12:36:26 -0400
Message-ID: <9YUUOAUPI0vmh4fHPVcq5R1j6a84@4ax.com>


A copy of this was sent to john_galt29_at_my-deja.com (if that email address didn't require changing) On Mon, 25 Oct 1999 03:21:53 GMT, you wrote:

>
>>Why do the rows need to be 'ordered' in insert order? Can you
>>reconsider that. Or perhaps post what you are trying to accomplish at
>>the end of the day -- not how you wish to accomplish it (eg: you are
>>asking how to sequentially number the rows to achieve your goal.
>>instead, post exactly what the goal is and ask how others might solve
>>it....)
>
>It's rather involved. Basically, after playing a game (a Shockwave
>movie) the results are sent to the database. The game consists of
>putting together pieces of a block. After constructing the block your
>block is added to a "tower." (It's a game for kids ...) The tower is
>nothing more than an HTML page, a table with three columns, each cell
>being a block. When a user clicks on a certain block in the tower, the
>info for that block is displayed - The user who made it, the color, and
>its number!
>
>Each Tower page consists of exactly 99 blocks. So I want the first
>block on the page to be 1, 2nd 2 ... 99th to be 99. No problem! Even if
>the blocks are out of order in the database, meaning the rows have some
>gaps, etc. , it doesn't matter - I just read them into a Javascript
>array. When they click on the 4th block I return the 4th array element.
>
>However, this is a calculation done at run-time. And it's "fake,"
>meaning the number in the database does not necessarily correspond to
>its position on the HTML page in the Tower.
>
>So for example ... the user wanders into the Scores section of the web
>site to see how many times he has played, etc. There he is told he
>played and placed Block #105 , the number from the database table.
>
>So he deduces that his block is on the 2nd Tower page (remember, there
>are 99 blocks to a Tower), sixth block. BUT if there was a gap in the
>database for some reason (rollback, for example) his could actually be
>the 2nd, 3rd, 4th ...
>
>Also I am using the MAX of that column to know how many previous Towers
>there are ... If the MAX is 198, for example, there are 2 pages, both
>with 99 blocks, time to create a new Tower page. But if there are
>gaps ... Do you see? It screws up my calculations... I could simply
>count the number of records but it seems like a lot of work simply to
>know how many records there are ...
>
>I guess the answer is lock the table, do the insert, so it always
>increments by exactly 1. I didn't want to do that but ... I guess it's
>just a couple miliseconds ... Just bothers me to do it ... Any other
>suggestions would be welcome!
>

I hate this solution but it would do what you ask:

create table blocking_sequence( cnt number ); insert into blocking_sequence values ( 1 );

create or replace procedure your_blocking_sequence return number as

    l_cnt;
begin

    update blocking_sequence set cnt = cnt + 1;     select cnt into l_cnt from blocking_sequence;

    return l_cnt;
end;
/

that would generate a sequential, with no gaps sequence number.

Its the same as locking the larger table.

>Thanks for your many helpful responses!
>John
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Oct 25 1999 - 11:36:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US