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

From: Jeff Eberhard <jeff.eberhard_at_gmail.com>
Date: Mon, 25 Jun 2007 22:49:24 -0600
Message-ID: <013c01c7b7ad$5f5cc910$aec511ac@UTOUG>

Perhaps the error is related to this:
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam .htm#11623  

"...you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement. Instead, you must embed parameters in the dynamic string, then pass the names of schema objects to those parameters."  

I know you will lose the cursor reuse but this tells me you will have to embed the parameter :x into the dynamic string.  

sql_statement := 'alter sequence '||seq_rec.column_name||'_sq increment by
'||seq_diff;

execute immediate sql_statement;

Take with grain of salt....  

--Jeff  

P.S. recreated error...  

SQL> create sequence jletest_seq;  

Sequence created.  

SQL> declare
  2 sql_statement varchar2(200);
  3 seq_diff number;
  4 begin
  5 seq_diff := 2;
  6 sql_statement := 'alter sequence jletest_seq increment by to_number(:x)';
  7 execute immediate sql_statement using seq_diff;   8 end;
  9 /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 7  

SQL> declare
  2 sql_statement varchar2(200);
  3 seq_diff integer;
  4 begin
  5 seq_diff := 2;
  6 sql_statement := 'alter sequence jletest_seq increment by
'||seq_diff;

  7 execute immediate sql_statement;
  8 end;
  9 /  

PL/SQL procedure successfully completed.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Bobak, Mark
Sent: Monday, June 25, 2007 5:23 PM
To: oracle-l
Subject: Anyone have a fresh pair of eyes....?

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
 <mailto:mark.bobak_at_il.proquest.com> mark.bobak_at_il.proquest.com
 <http://www.proquest.com/> www.proquest.com
 <http://www.csa.com/> www.csa.com

ProQuest...Start here. 



 



--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 25 2007 - 23:49:24 CDT