Re: Strange PB !?

[Quoted] In article <7jv0j6$l84$1_at_nnrp1.deja.com>, Wassim <net2000_at_francemel.com> wrote:
>I've in many of my PL/SQL procedures to create a new record in a table
>just with Key code = Last key + 1, (key_code is the table primary key)
>
>to do this i've written the following PL/SQL code :
>
>------------------------------------------------------------------------
>last_key_code := 0;
>begin
>select max(key_code) into last_key_code from myTable;
>exception when OTHERS then last_key_code:=0; -- when myTable is empty !!
>end;
>
>htp.print(last_key_code);
>
>insert into myTable values (last_key_code+1,...);
>------------------------------------------------------------------------
>
>When myTable is empty this algorithm never works, when attemping to
>print value to 'last_key_code' in such situation (myTable is empty)
>nothing is printed, and 'insert into' procedure return error !!! i add
>an 'if' statment to make it work but doesn't solve the pb :
>
>----
>if last_key_code=null then last_key_code:=0; end if
>----
>
>I think that we can automate such operations by creating a 'sequence' ?
>how ?

[Quoted] Yes, a sequence is the accepted way to insert a new key value. But your way would work, as well. It is just that when there are no rows in your table, the max function returns null. It does not produce an exception as you are trying to trap.

So try this:

select NVL(max(key_code),0) into last_key_code from myTable;

and leave out the exception.
Steve Cosner Received on Sun Jun 13 1999 - 07:09:51 CEST