oracle alter sequence by shell script & sqlplus

Question

I'm working on altering sequence through sqlplus in a shell script. What I'm about to do is to get the max seq_no of the table, set the value in v_increment_num, and increase the sequence by v_increment_num. But it gets an error when alter statement runs.

The script is like below.

echo start load_my_adm_user.sh

sqlplus myId/myPassword <<EOF

set echo on
set time on
set timing on
set serveroutput on
spool TB_MY_ADM_USER.log

var v_increment_num varchar2(1000);

SELECT MAX(SEQ_NO) INTO :v_increment_num FROM TB_MY_ADM_USER;
alter sequence mydb.SQ_ADM_USER increment by v_increment_num;
select mydb.SQ_ADM_USER.nextval from dual;
alter sequence mydb.SQ_ADM_USER increment by 1;

spool off
EOF
exit

and the running result(TB_MY_ADM_USER.log) is like:

07:01:23 SQL> SELECT MAX(SEQ_NO) INTO :v_increment_num FROM TB_MY_ADM_USER;

MAX(SEQ_NO)
-----------
          4

Elapsed: 00:00:00.00
07:01:23 SQL>
07:01:23 SQL> alter sequence mydb.SQ_ADM_USER increment by v_increment_num;
alter sequence mydb.SQ_ADM_USER increment by v_increment_num
                                                  *
ERROR at line 1:
ORA-01722: invalid number


Elapsed: 00:00:00.00
07:01:23 SQL>
07:01:23 SQL> select mydb.SQ_ADM_USER.nextval from dual;

   NEXTVAL
----------
        19

Elapsed: 00:00:00.02
07:01:23 SQL> alter sequence mydb.SQ_ADM_USER increment by 1;

Sequence altered.

Elapsed: 00:00:00.02

What exactly am I doing wrong? Thanks in advance.

peter.hrasko.sk · Accepted Answer · 2017-05-25 08:57:05Z

2

You have almost everything correct, just have to remember that DDL statements, i.e. alter sequence, too, don't work with bind variables. Which, I mean a bind variable, is what your v_increment_num is.

If SQL*Plus is what you're going to use, then try it with its own substitution "variables", i.e. the "ampersand"-variables...

echo start load_my_adm_user.sh

sqlplus myId/myPassword <<EOF

set echo on
set time on
set timing on
set serveroutput on
spool TB_MY_ADM_USER.log

-- do not do...
-- var v_increment_num varchar2(1000);
-- do instead...
column max_seq_no new_val v_increment_num
-- note: this makes all MAX_SEQ_NO column values from all subsequent SELECTs to be stored in the &V_INCREMENT_NUM variable

-- do not do...
-- SELECT MAX(SEQ_NO) INTO :v_increment_num FROM TB_MY_ADM_USER;
-- do instead...
SELECT MAX(SEQ_NO) as max_seq_no FROM TB_MY_ADM_USER;
-- note: at this point, you will have your MAX(SEQ_NO) value stored in your &V_INCREMENT_NUM variable

-- do not do...
-- alter sequence mydb.SQ_ADM_USER increment by v_increment_num;
-- do instead...
alter sequence mydb.SQ_ADM_USER increment by &v_increment_num;

select mydb.SQ_ADM_USER.nextval from dual;
alter sequence mydb.SQ_ADM_USER increment by 1;

spool off
EOF
exit

Note: I did not try this particular code, just wrote it here directly, so it may not be working at the first attempt, but we'll get that eventuality sorted out later.

answered May 25, 2017 at 8:57

peter.hrasko.sk

4,2012 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3432269 Over a year ago

It perfectly works.. learned how to use new_value parameter. Thanks a lot!

Collectives™ on Stack Overflow

oracle alter sequence by shell script & sqlplus

The script is like below.

and the running result(TB_MY_ADM_USER.log) is like:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The script is like below.

and the running result(TB_MY_ADM_USER.log) is like:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related