Oracle update sequence script can result in deleted sequences
-------------------------------------------------------------
Key: DS-961
URL: https://jira.duraspace.org/browse/DS-961
Project: DSpace
Issue Type: Bug
Affects Versions: 1.7.2, 1.7.1, 1.7.0, 1.6.2, 1.6.1, 1.6.0, 1.5.2, 1.5.1,
1.5.0, 1.8.0
Reporter: Kevin Van de Velde
Priority: Major
When updating the sequences using oracle if the table is empty the sequence for
this table will be dropped, but not recreated.
Imagine the following situation:
DECLARE
curr NUMBER := 0;
BEGIN
-- If the table is empty, curr will be equal to Null
SELECT max(workflowitem_id) INTO curr FROM workflowitem;
-- If curr is equal to Null, curr + 1 will be equal to Null as well
curr := curr + 1;
-- The sequence will be dropped
EXECUTE IMMEDIATE 'DROP SEQUENCE workflowitem_seq';
-- The sequence will not be recreated because of an 'Invalid Number' error
since, curr equals Null
EXECUTE IMMEDIATE 'CREATE SEQUENCE workflowitem START WITH ' || curr;
END;
I therefore suggest to replace the CREATE statement with the following line:
EXECUTE IMMEDIATE 'CREATE SEQUENCE &1 START WITH ' || NVL(curr,1);
By using the NVL() function, 1 will be used as the starting value for the
sequence instead of the invalid Null value.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://jira.duraspace.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
10 Tips for Better Web Security
Learn 10 ways to better secure your business today. Topics covered include:
Web security, SSL, hacker attacks & Denial of Service (DoS), private keys,
security Microsoft Exchange, secure Instant Messaging, and much more.
http://www.accelacomm.com/jaw/sfnl/114/51426210/
_______________________________________________
Dspace-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/dspace-devel
