Problem with sequence in oracle

Question

Firstly i have created a sequence in oracle in perl script.I get a error statiing sequence already created error.Is there any means to reset the sequence(Create or replace doesnt work)

create or replace sequence test_seq start with 1900 increment by 1 nomaxvalue

sprintf("INSERT INTO Custodian_New (Loginid,Type, Id, User, TimeCreated, RcNumber) values ('%s',1, %d, '%s', %d, '%s')", seq_name.nextVal,$dd, $Name, time(), $rc_num); 

The above statement in perl script prints:'test_seqnextVal' for the sequence. So an error

DBD::Oracle::db do failed: ORA-01722: invalid number (DBD ERROR: error possibly near <*>seq_name )occurs.

Please suggest me on what is the problem?Thanks.

Answer 1

For the ORA-01722, your sprintf is has single quotes around the %s, so it will try to insert the string "seq_name.nextVal" into the number field instead of a value from the sequence; that string can't be converted to a number, hence the error. You can remove the single quotes:

sprintf("INSERT INTO Custodian_New (Loginid, Type, Id, User, TimeCreated, RcNumber) values (%s, 1, %d, '%s', %d, '%s')", "seq_name.nextVal", $dd, $Name, time(), $rc_num);

Or just embed the sequence directly as there doesn't seem to be any point building it dynamically:

sprintf("INSERT INTO Custodian_New (Loginid, Type, Id, User, TimeCreated, RcNumber) values (seq_name.nextVal, 1, %d, '%s', %d, '%s')", $dd, $Name, time(), $rc_num);

Or even better, use bind variables and set those to $dd etc. I'd also use sysdate instead of passing in time(), but don't know if that really makes any difference.