On Feb 9, 12:57 pm, "frank.van.bor..._at_gmail.com"
<frank.van.bor..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Thanks for your reponse. I have used SET ROLE NONE, and can still
select from user_sequences table.
Received on Fri Feb 09 2007 - 07:32:27 CST
