oracle中的顺序问题

发布于 2024-10-31 02:11:17 字数 564 浏览 8 评论 0原文

首先，我在 perl 脚本中的 oracle 中创建了一个序列。我得到一个错误，说明序列已创建错误。有没有办法重置序列（创建或替换不起作用）

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);

perl 脚本中的上述语句打印：“test_seqnextVal”顺序。所以出现错误

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

请建议我问题是什么？谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

柒夜笙歌凉 2024-11-07 02:11:17

对于 ORA-01722，您的 sprintf 在 %s 周围有单引号，因此它将尝试插入字符串 "seq_name.nextVal" 进入数字字段而不是序列中的值；该字符串无法转换为数字，因此会出现错误。您可以删除单引号：

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);

或者直接嵌入序列，因为动态构建它似乎没有任何意义：

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);

或者更好的是，使用绑定变量并将其设置为 $dd 等。还使用 sysdate 而不是传入 time()，但不知道这是否真的有什么区别。

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.

回复收藏 0 原文