Anyone have a fresh pair of eyes....?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 25 Jun 2007 19:23:05 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF270C9B72FF@AABO-EXCHANGE02.bos.il.pqe>

This has got to be something stupid, but I'm just not seeing it, and I'd like to go home at some point this evening....;-)  

Any clues?
declare

        cursor list_of_sequences  is select table_name,column_name
                                       from user_tab_columns
                                      where column_name in(select
substr(sequence_name,1,instr(sequence_name,'_',-1)-1)
                                                             from
user_sequences)
                                                              and
column_id = 1
                                                              and
table_name not like 'LOAD_%'
                                                              and
table_name not like 'TEMP_%';
        incr_by number;
        max_value number;
        curr_seq number;
        seq_diff number;
        dummy number;
        sql_statement varchar2(200);
begin
        for seq_rec in list_of_sequences
        loop
                select increment_by into incr_by from user_sequences
where sequence_name = seq_rec.column_name||'_SQ';
                sql_statement := 'select max('||seq_rec.column_name||')
from '||seq_rec.table_name;
                execute immediate sql_statement into max_value;
                sql_statement := 'select
'||seq_rec.column_name||'_SQ.NEXTVAL from dual';
                execute immediate sql_statement into curr_seq;
                seq_diff := max_value - curr_seq;
                sql_statement := 'alter sequence
'||seq_rec.column_name||'_sq increment by :x';


--->                execute immediate sql_statement using seq_diff;

                sql_statement := 'select
'||seq_rec.column_name||'_sq.nextval from dual';
                execute immediate sql_statement into dummy;
                sql_statement := 'alter sequence
'||seq_rec.column_name||'_sq increment by :x';
                execute immediate sql_statement using incr_by;
        end loop;

end;
/

The line w/ the '--->' pointing at it consistently hits ORA-1722 "invalid number"......  

I think I've been staring for too long...can someone throw me a rope here?? ;-)  

Thanks!  

-Mark
 

--

Mark J. Bobak
Senior Database Administrator, System & Product Technologies ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346 Ann Arbor MI 48106-1346
734.997.4059 or 800.521.0600 x 4059
mark.bobak_at_il.proquest.com <mailto:mark.bobak_at_il.proquest.com> www.proquest.com <http://www.proquest.com/> www.csa.com <http://www.csa.com/>

ProQuest...Start here.  

--

http://www.freelists.org/webpage/oracle-l Received on Mon Jun 25 2007 - 18:23:05 CDT