On 9 feb, 13:26, "00steve" <steven_macl..._at_hotmail.com> wrote:
> Hi, I am using the code below in attempting to create a procedure
> which resets sequence values to the highest value of a column (+1).
> The basic chain of events is as follows:
>
> 1. get max() value of column name
> 2. set nextval of sequence
> 3. set increment value of sequence to difference between max() value
> and currval
> 4. nextval on sequence (with new increment value)
> 5. reset sequence to original increment value
>
> When I do this using standard SQL it works fine. When I attempt to do
> it in a PL/SQL I find that the sequence is only incremented by the
> original increment value, and not the newly altered one. Code is
> posted below, thanks for any help with this:
>
> CREATE OR REPLACE procedure resetSeq
> ( table_name IN VARCHAR2, col_name IN VARCHAR2, seq_name IN
> VARCHAR2 )
> IS
> max_val number(10);
> next_seq_value NUMBER;
> seq_increment NUMBER;
> difference NUMBER;
>
> BEGIN
> EXECUTE IMMEDIATE 'SELECT max('||col_name||'+1) FROM '||table_name
> INTO max_val;
> EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL' INTO
> next_seq_value;
> difference := max_val-next_seq_value;
>
> IF difference >=1 THEN
> SELECT Nvl(INCREMENT_BY,1) INTO seq_increment FROM
> user_sequences WHERE SEQUENCE_NAME = Upper(seq_name);
> EXECUTE IMMEDIATE 'ALTER SEQUENCE '||seq_name||' INCREMENT
> BY '||To_Char(difference)||' ';
> EXECUTE IMMEDIATE 'SELECT '||seq_name||'.NextVal FROM DUAL';
> EXECUTE IMMEDIATE 'ALTER SEQUENCE '||seq_name||' INCREMENT
> BY '||seq_increment||' ';
> COMMIT;
> END IF;
> END;
Dangerous code... no guarantee that the column you select
the max value from uses the sequence...
Do you have the right to select from user_sequences?
And no - the fact that you can do in SQL does not mean a thing,
try the SQL statement after a 'set role none', if it fails, ask
to get the object privelege, not a role
Received on Fri Feb 09 2007 - 06:57:54 CST
