I posted a Gotcha in Oracle Commit some time back. This is one of the posts that gets visited a lot. Also, at work, I saw some SQL scripts that showed a complete lack of understanding of how transactions or commits work in Oracle (or any database for that matter). Hence this post.
Dictionary.com defines Transact as “To carry or conduct business to a conclusion or settlement”. And a Transaction is an act of Transacting! BusinessDictionary.com goes a bit further to explain it as a contract or agreement. It even goes further to define it as an event in accounting, such as entries that go into a cash register. A Transaction then is a self-contained unit of work, that is completed (either with success or failure). This is what is adapted in Database terminology. A transaction must be complete, reliable and independent of other actions.
In Database parlance, we call it an ACID – atomic, consistent, isolated and durable. See Microsoft’s post about ACID here or this Wikipedia article. When a DB transaction succeeds, we COMMIT it and when it fails, we ROLLBACK it.
Transactions in real life
A transaction typically happens between 2 parties and it typically concludes to the satisfaction of both the parties involved. The parties involved either agree to go all the way, or cancel/settle somehow. Think about it. Even if there are multiple parties involved, you can always break it down to smaller and smaller transaction until you can no longer split and that will among 2 parties.
A typical transaction we all can relate is the cash transaction at a bank (at a Teller counter the ATM): You give your card. The teller (or the machine) validates it and asks you to enter your pin number. Once you have done that successfully, then the teller (or the machine) asks you the type of transaction you would like to perform – deposit or withdraw. If you are withdrawing, you tell the amount and the teller gives you the money and deducts it from the account and finally gives you a receipt.
For example, imagine you are at an ATM depositing money into your account. Say you had $1000 in the account before you deposited another $100. This is your transaction with the bank at the moment. (You may have other transactions at the same bank and elsewhere, but each is an independent unit of work). If everything goes well, at the end of the “transaction”, your account will have $1100. What if all is not well? What if Power goes down in the middle of this transaction? Will your account now have $1000 or $1100 or even $900 (incorrect)? What happened to your deposit? The answer in all these cases should be, your deposit is safely completed irrespective of external issues. That’s where the concept of transactions help.
For another e.g., slightly longer transaction, let’s assume your transaction is building a house. To do this, you go to the bank and get the loan, go to the builder and decide the plan, builder cashes his check upon which his bank transacts with yours, builder goes to construction company and gets the building built and so on. And if any of these individual transactions fail, there are repercussions in others. For .e.g., if your transaction with the bank failed, your transaction with the builder does not happen, even if you previously agreed.
Imagine such transactions – they have to be atomic – a collection of small steps each of which is completed and the teller or the system will take you to the next step. For e.g., when you are at the ATM, if the password step doesn’t complete, you don’t stand a chance of seeing the money (worse case, may even trigger a new transaction with the security guard!). Your transaction with the teller is isolated or independent, we don’t want it to be mixed with your other transactions or someone else’s! And it is durable – once done, the results are set in stone (or computer). We certainly expect the house to be durable at the end of that transaction!! And these concepts are typically applied to the Database, since that’s where your results of your transactions are stored (persisted).
Transactions in a DB
The concept of transactions is not new to Databases. Mainframe databases had them for a long time. Modern Relational Databases (and SQL) are all transactional by nature. See this about.com post for a description of database transactions in general. Every major SQL database vendor support transactions. Some may support Autocommit by default. Some, like Oracle, may leave it to the SQL developer (you!).
In a relational database, every transaction may contain one or more database operations. In RDBMS these operations are grouped as Data Manipulation Language (DML), Query and Data Declaration Language (DDL). As I mentioned in my previous post, Commits work differently in DMLs and DDLs in Oracle.
The DML statements are the atomic operations that perform a single database operation like INSERT, UPDATE and DELETE. These operations can be performed individually or combined in a group of statements. Together these make a “Transaction”. At the end of such a transaction, you will have to COMMIT or ROLLBACK to make the effect of transaction permanent or ROLLBACK to revert back to before the transaction block, so the database is left unaffected. See here for more on COMMIT etc. Again, COMMIT can be automatic or manual.
I would like to point out one gotcha here: Since DDL automatically commits, you don’t really need to use an explicit COMMIT after a DDL statement (CREATE, ALTER etc). I am somewhat puzzled to see even experienced developers and DBAs sometimes make this mistake. This is true for even CTAS (Create Table As) statements in Oracle.
CREATE TABLE dup_employee AS SELECT * FROM employee; -- You don't really need this COMMIT; it's already committed above. <span style="text-decoration: line-through;">COMMIT;</span>
We say that this is in an implicitly committed transaction. If we expanded the above statement, it may look like this:
COMMIT; CREATE TABLE dup_employee AS SELECT * FROM employee; COMMIT;
Just you or the database don’t have to show it explicitly, as a matter of convenience. But, this definitely
When a bunch of SQL statements participate in a single transaction, they are in one single Transactional block. To indicate beginning and end Transaction block, SQL standard talks about transaction control statements. For e.g, a START TRANSACTION (MySQL), BEGIN TRANSACTION (SQL Server) or BEGIN WORK(Informix) marks the beginning of a transaction block. Typically a COMMIT or ROLLBACK would mark the end. To be able commit or rollback to a specific section in the transactional block, databases offer sort of bookmarks, the SAVE POINTS. When you start a transaction block, make sure there is no other transaction block is already open, meaning the last block was committed or rolled back. Otherwise, you will get an error from the database.
In Oracle SQL, you don’t need an explicit “START TRANSACTION” or “BEGIN WORK” statement to mark the beginning of a transaction. Typically, the first non-committed executable SQL statement, can be DML or DDL, (implicitly) marks the beginning of a new transaction block. (otherwise, each statement joins the previously open transaction block). A commit or Rollback will end the transaction.
<<implicit beginning of transaction>>
INSERT INTO department (dept_id, dept_name) VALUES (10, 'HR'); INSERT INTO employee (emp_id, employee_name, dept_id) VALUES (100, 'Employee', 10); INSERT INTO payroll(emp_id, emp_type, pay_rate, pay_rate_type) VALUES(100, 'TEMP', 100, 'HOURLY');
INSERT INTO lotto_pool (pool_id, emp_id) VALUES (1, 100);
These imaginary inserts are done for each new employee that joins a company. The employee gets added to the employee table and also cross-referenced in a department, payroll tables. Come to think about it, each SQL statement above depends on the previous one to succeed. Thus, they can be part of a single transaction block. If the department does not exist, he cannot be added and if the employee doesn’t exist, he cannot be added to payroll etc.
The above example is just for the illustration of a transaction. Really speaking, the department could have been added long before 100th employee arrives. An employee record could be added well before, his payroll information is setup).
To add little more control to the transaction handling, Databases also provides a marker. In Oracle this is called a SAVEPOINT.
In the above example, if you assume a save point after each DML statement, then you will be able to rollback or commit to a specific save point.
INSERT INTO department (dept_id, dept_name) VALUES (10, 'HR'); SAVEPOINT dept_save; INSERT INTO employee (emp_id, employee_name, dept_id) VALUES (100, 'Employee', 10); SAVEPOINT emp_save; INSERT INTO payroll(emp_id, emp_type, pay_rate, pay_rate_type) VALUES(100, 'TEMP', 100, 'HOURLY'); SAVEPOINT payroll_save;
In this example, we could simply save the department, even if there is an error in employee or payroll.
A friend asked me why his stored procedure wasn’t working. He was calling it from a program, so there was no way to test it, other than saying the update didn’t happen.
I looked at this procedure, it had one UPDATE statement. The meat of the code is shown below (I am paraphrasing):
CREATE OR REPLACE PROCEDURE upd_employee
SET employee_name = p_employee_name,
address = p_address,
created_dt = SYSDATE
employee_nbr = p_employee_nbr
WHEN OTHERS THEN
When I saw this, the first thing I noticed was that there is no proper error checking there. I see the EXCEPTION handling this. It’s natural to think that it will take care of all errors. Not so!
UPDATE Statement is an anamoly. When UPDATE statement is executed, it simply sets a variable SQL%ROWCOUNT to the # of rows updated and moves on. When UPDATE “fails” find any record to update, the ROWCOUNT will be 0. It is simply not an error as far as the database is concerned.
(This is unlike the case of a SELECT..INTO, where you get a NO_DATA_FOUND exception. Actually, not many developers do that. That’s for another story later).
Coming back to the issue, I suspected that the UPDATE did not happen and since there is no error handling, it “failed” silently.
First thing I did was to prove this theory. Looking at the procedure code, since it’s not checking for anything specific, I can write a crude test like so:
‘123 State St, Los Angeles’
I am essentially testing it as a blackbox like his program did. But, I am in an interactive session, where I can print using dbms_output.put_line to print debug messages. Now, you can see the real story. I am printing SQL%ROWCOUNT and it was 0 as expected.
For a quick fix, I just added the below line above the EXCEPTION and it did the job.
IF (SQL%ROWCOUNT = 0) THEN
But, in reality I will add lot more checks – including the types of the field etc. For application, instead of just RAISE, I would have added Raise_Application_Error function. Raise_Application_Error allows us to use -20000 .. -20999 as user error codes.
(I saw one program where they used -30000 which caused a brand new error to be generated. I will reserve that for another day!!). I may even add the below lines to be complete:
SHOW ERRORS PROCEDURE upd_employee
These are particularly helpful in SQL*Plus. First line makes sure the errors are printed and the EXIT leaves no ambiguity. (If you don’t have it, you will have to find ways to exit from OS level. See my earlier post for this).
And from a standards point of view, I would name the procedure sp_upd_employee to indicate it’s a stored procedure. I have a full standards document I made for a client years ago. I will post it some other time.
Before closing, I would like to mention couple of pet peeves of mine. One, a PL/SQL block must have the below syntax:
COMMIT should be part of the <body> and ROLLBACK should be in the EXCEPTION section. Having a COMMIT after the exception block is accepted syntactically, but it is WRONG! Because, he has a RAISE statement, it is never reached here, in case of any errors. But, the actual UPDATE statement is not COMMITted either!! (Not exactly true – it will actually COMMIT, but we are now leaving it to the mercy of SQL*Plus or any tool, which COMMITs when exiting the script, by default).
The other pet peeve of mine is the blank line inside SQL statements. I agree, it looks more readable, but wrong. PL/SQL ignores this, but if you run the SQL by itself, SQL*Plus will throw an error. I try to be consistent between SQL and PL/SQL to avoid surprises. If I really need a spacer, I add a comment line
— this is a comment
<rest of the SQL>;
We are currently in the process of upgrading our Oracle database from version 11g to 12c. There are lot of challenges. One of the challenge we have is of course space. We have one test (Unix) machine that is running several Oracle instances. During the Oracle 12c, the DBA decided to upgrade only one of the instances to 12c. This meant that, whenever they refresh the database, their automatic refresh from a production standby would no longer work, because of the version differences. They will have to manually export and import from production (or standby) using datapump (expdb on the source and impdb on the target database).
While testing this new database (DB12c), we ran into a strange problem – the user started getting a lot of “Primary key violations”. After some checking, found out that the sequences were out of whack. Essentially, the sequences were not part of the export or import and thus they retained the old values before we did the refresh. This seems to be a problem Oracle is aware of. See here.
We were in the middle of a comparison testing (DB11g is a test instance in Oracle 11g and DB12c in 12c) and we needed a quick solution. I decided to attack the issue as a programming problem. A bit of Googling gave solutions to reset the sequence to 0. This won’t work for us, as we had live data in the database on which we were running tests. I considered different solutions.
- What if we simply bump up every sequence in the database by some big number, so they will not run into trouble? Say +100,000 to each sequence?
This seemed to work OK, until someone ran a huge batch program on DB11g and 100,000 wasn’t good enough. We started seeing Primary key violations sooner. So, what # can we increase it by?
- The second solution I considered, was to simply bump up the sequences to match those in DB11g instance by using dblink.
This worked ok too, but we hit the same snag as above. This worked as long as the DB11g was not refreshed through datapump (which means it’s sequences were not correct as well!!)
- The 3rd option was simply a programmer’s way of thinking:
Why not just create a table, insert the sequence name and its value into this table and bring that table to target database instance and then run a script to update the sequences with the values from the table we just brought in. This works in any situation. This is what we currently use.
Here is the script for the first approach:
DECLARE v_seq_val NUMBER; v_max_id NUMBER; v_adjust_value NUMBER; v_sql VARCHAR2(4000); BEGIN Dbms_Output.put_line('spool sequences_fixes.log'); -- Increment by constant; change as needed v_adjust_value := 200000; FOR seq IN ( SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1' ) LOOP BEGIN Dbms_Output.put_line('-- ' || seq.sequence_name); v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_adjust_value || ';'; Dbms_Output.put_line(v_sql); v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;'; Dbms_Output.put_line(v_sql); -- reset "increment_by" back to 1 v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;'; Dbms_Output.put_line(v_sql); Dbms_Output.put_line('--'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END LOOP; Dbms_Output.put_line('spool off'); END; /
That didn’t work out for us, especially when production, source and target databases were all in a flux.
The second approach using dblink was a little more reliable, as long as the source itself was not refreshed using datapump.
FOR seq IN ( SELECT sequence_name, last_number FROM all_sequences@LTXSYT15 WHERE sequence_owner = 'SCHEMA1' ) LOOP BEGIN Dbms_Output.put_line('-- ' || seq.sequence_name); SELECT last_number INTO v_seq_val FROM all_sequences WHERE sequence_owner = 'SCHEMA1' AND sequence_name = seq.sequence_name; v_diff := seq.last_number - v_seq_val; IF (v_diff > 0) THEN v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_diff || ';'; Dbms_Output.put_line(v_sql); v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;'; Dbms_Output.put_line(v_sql); -- reset "increment_by" back to 1 v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;'; Dbms_Output.put_line(v_sql); END IF; Dbms_Output.put_line('--'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END LOOP; Dbms_Output.put_line('spool off'); END; /
And finally, the approach that worked was to bring the latest sequence values from the production instance (original source from which both DB1 and DB2 were refreshed):
On the production database (or source):
-- Run this in the Source database = production? DROP TABLE schema1.latax_sequence_values; CREATE TABLE schema1.latax_sequence_values (sequence_name, seq_value) AS SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1'; -- export this to a file, so it can be imported to the target database.
Then export the table just created and import into target database.
DECLARE v_seq_new_value NUMBER; v_max_id NUMBER; v_diff NUMBER; v_sql VARCHAR2(4000); BEGIN Dbms_Output.put_line('spool sequences_fixes_using_another_db.log'); FOR seq IN ( SELECT sequence_name, last_number FROM all_sequences WHERE sequence_owner = 'SCHEMA1' ) LOOP BEGIN --Dbms_Output.put_line('-- ' || seq.sequence_name); SELECT seq_value INTO v_seq_new_value FROM schema1.latax_sequence_values WHERE sequence_name = seq.sequence_name; v_diff := v_seq_new_value - seq.last_number; Dbms_Output.put_line('-- ' || seq.sequence_name || ' , curr val=' || seq.last_number || ', new val=' || v_seq_new_value || ', ' || ', diff=' || v_diff); IF (v_diff > 0) THEN v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY ' || v_diff || ';'; Dbms_Output.put_line(v_sql); v_sql := 'SELECT ' || seq.sequence_name || '.nextval FROM dual;'; Dbms_Output.put_line(v_sql); -- reset "increment_by" back to 1 v_sql := 'ALTER SEQUENCE ' || seq.sequence_name || ' INCREMENT BY 1;'; Dbms_Output.put_line(v_sql); END IF; Dbms_Output.put_line('--'); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END LOOP; Dbms_Output.put_line('spool off'); END; /
- In all the above scripts, I have left out the SQL (spooling etc) portion and show only the meat of the solution in PL/SQL.
- The core logic is to use ALTER SEQUENCE to bump up the sequence. Remember, Sequence only moves forward, typically by an increment of 1. In these scripts, we change the increment value to be the difference of the current value in the sequence and what it should be (diff new value of sequence – old value of sequence).
But, don’t forget to reset the “increment by” value back to 1. Otherwise, you will be surprised by the next value you get in the database!! See my earlier post here for more about resetting the sequence.
- Also, note that the above values I got (either through dblink or through a table) are approximate. I am reading the data dictionary (user_sequences) to get the last value. The value I got may be much larger than the Sequence.nextval, but that didn’t matter. This is probably because of caching (cache_size column in all_sequences), but this was OK for us. I just wanted to get the sequence.nextval out of “primary key” above the danger zone.
- Like I mentioned above, this was a programmer’s solution. The DBA solution would have been something they mentioned at the bottom of the link I mentioned earlier). There a DBA, I assume, is suggesting to drop all the sequences and reload them using the DDL package. That may be more efficient from a DBA point of view, but mine is poorman DBA’s solution!
- And finally, this is one case where a proper naming convention would have alleviated the problem. Suppose you have tables named A through Z. If we named every sequence as A_primary_Seq, B_primary_Seq etc to mean the sequence for the primary of table A etc, then we could have easily addressed it.
In fact, this was my first attempt at fixing this issue. Soon, I found out that our sequence naming was all over the place.
Since the primary key violation happens because the sequence value is below the max(id) in the table, we could have simply,
sequence new value = max(id) + 1
A_primary_seq value = Max(primary_key_field) from Table A;
To get the primary key field for the table, I used something like:
SELECT i.table_name, c.column_name FROM dba_indexes i, dba_ind_columns c WHERE i.owner = 'SCHEMA1' AND c.table_owner = 'SCHEMA1' AND uniqueness = 'UNIQUE' AND i.index_name = c.index_name AND i.table_name = c.table_name AND c.char_length = 0;
While working on complex reports, we occasionally have a need to transform Columns to Rows and vice versa. Converting Rows to Columns is typically called Pivot and the opposite, converting Columns to Rows is called Unpivot. Concept of Pivot, and the need for it, has been around for a long time. Excel for example, has a Pivot table to do these type of data transformations. It’s been available in SQL Server for a while. Pivoting is sometimes called Crosstab. PowerBuilder has a datawindow style like that. These are very useful in building data warehouses, reports etc.
Coming back to the topic, Oracle introduced the operators recently (in 11g). Before that you would have to do a round about way. There are several ways to do this, but here is a simple way, when # of columns are small.
CREATE TABLE payment_list(pay_type VARCHAR2(3), description VARCHAR2(30), amount NUMBER); INSERT INTO payment_list VALUES('CHK', 'Check', 200); INSERT INTO payment_list VALUES('CRE', 'Credit Card', 500); INSERT INTO payment_list VALUES('DEB', 'Debit Card', 210); INSERT INTO payment_list VALUES('ACH', 'ACH payments', 2000); INSERT INTO payment_list VALUES('CHK', 'Check', 700);
Here is what we get, with a normal SELECT:
Each row has a payment type and the amount paid. Sometimes, you will want to see these in a column:
SELECT Decode(pay_type, 'CHK', amount, NULL) AS check_pmt, Decode(pay_type, 'CRE', amount, NULL) AS cc_pmt, Decode(pay_type, 'DEB', amount, NULL) AS debit_pmt, Decode(pay_type, 'ACH', amount, NULL) AS ach_pmt FROM payment_list;
By using Oracle Decode function, we separated the amount for each type into separate columns. Of course, in reality, we will have lot more rows for each type and we may want to see the Total for each type:
SELECT Decode(pay_type, 'CHK', Sum(amount), NULL) AS check_pmt, <span style="color: #0000ff;"> Decode(pay_type, 'CRE', Sum(amount), NULL) AS cc_pmt, Decode(pay_type, 'DEB', Sum(amount), NULL) AS debit_pmt, Decode(pay_type, 'ACH', Sum(amount), NULL) AS ach_pmt FROM payment_list GROUP BY pay_type;</span>
We can do the same thing with the new Pivot operator:
SELECT * FROM ( SELECT pay_type, amount FROM payment_list) PIVOT (Sum(amount) FOR pay_type IN ('CHK', 'CRE', 'DEB', 'ACH') );
By the way, if you wanted to collapse your DECODE SQL to return single row result like above, you would use:
SELECT Sum(check_pmt) CHK, Sum(cc_pmt) CC, Sum(debit_pmt) DEB, Sum(ach_pmt) ACH FROM SELECT Decode(pay_type, 'CHK', Sum(amount), NULL) AS check_pmt, Decode(pay_type, 'CRE', Sum(amount), NULL) AS cc_pmt, Decode(pay_type, 'DEB', Sum(amount), NULL) AS debit_pmt, Decode(pay_type, 'ACH', Sum(amount), NULL) AS ach_pmt FROM payment_list GROUP BY pay_type )
Pivot operator is available in many modern databases (and Excel too). I couldn’t find a site that shows Oracle syntax for PIVOT operation in a simple format yet. However, Oracle’s site has great content on the operator (and unpivot) here. For sake of completeness, I am including the SQL syntax Pivot operator in SQL Server. The only difference, I could see here is SQL Server’s usage of square brackets instead of quotes.
SELECT <non-pivoted column>, [ pivoted column] AS <column name>, [ pivoted column] AS <column name>, ... [ pivoted column] AS <column name> FROM(<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(column) FOR [<column that contains the values that will become column headers>] IN ( [pivoted column], [pivoted column], ... [pivoted column]) AS <alias for the pivot table> [ORDER BY clause];
That’s all folks. I know, it has a weird syntax, but The inner most SQL (SELECT query that produces the data), is what you are pivoting. The row(s) for each value in the FOR .. IN list will be converted to a column. So, when the inner SQL finishes, you will get the columns CHK, CRE, DEB, ACH. SELECT * in the outer SQL, makes sure all these columns are picked up.
To be contd…
I just posted an error some developers got while inserting records into an Oracle table. I assumed there was an issue with Sequence used and replied back. I included that in my last post here.
I did receive a feedback after a week or 2. What I saw told me the developer did not have a clear understanding of the Sequence usage in Oracle. I will try to explain it here, so other developers may benefit from it.
As I mentioned in my original post about Oracle Sequences, it’s just a counter – sort of a global object, that is available anywhere within a SQL or PL/SQL script in Oracle. It’s an independent object by itself and to bump up the counter, you use NextVal function, which returns the new Value the counter is set to. You use this new value in your INSERT statement (why else would you get a new Sequence (counter) value, unless you are using to create a new record with the unique ID?). By using this method, you are keeping the Sequence and the last ID in the Table in sync.
Now, consider this. Some program uses this and another one uses Max(ID) + 1 approach. You can immediately that Sequence and the last ID in the table can fall out of synch right?
I heard user was inserting records in a batch (BULK?). Today, I asked to look at the program. As I guessed it, the developer was inserting records in a Loop (CFLOOP in Coldfusion).
Now, to get the ID, the developer used the Sequence once before the loop and set the value to a local variable. This variable is then incremented (correctly) inside the loop for each INSERT. Do you see how the Sequence has fallen way behind last ID used in the table??
See the problem there? The developer got NextVal from the Sequence correctly, but only at the top and loaded it to a local counter variable. After that, he incremented and used a local counter to load the table. At the end of the program,
the Sequence is w..a..y.. behind the actual last ID used in the table!!!!!! This, my friend, caused the Unique Constraint violation, next time the program ran. Since, this patter continues, the Sequence never catches with the last ID in the table and the program is forever banished to get this error!!
The key is to get NextVal every time you INSERT the Sequence value into the table. Here is a simple code that gets NextVal from a Sequence and inserts that into a table.
<cfquery name="getID" datasource="inquiries"> SELECT S_employee_id.NEXTVAL AS newID FROM dual </cfquery> .... ....<cfquery name="qrySubmitForm" datasource="inquiries"> INSERT INTO EMPLOYEE(employee_id) VALUES (#GetID.NewID#) </cfquery>
This defines a CF Query GetID. When used as shown the Query gets executed on each INSERT statement. This way, the Sequence and the last ID in table will be in synch. This will work, even when it goes into a Loop.
I just posted about Oracle Sequences here. The very reason I posted was because, I’ve seen several experienced developers seem to fumble on it. We had one at work today.
I recently got a request from our DBA to look at an error message from another group. (I’ve changed the table name etc to employee)
Message: Error Executing Database Query. [Macromedia][Oracle JDBC Driver][Oracle]ORA-00001: unique constraint (EMPLOYEE) violated
The error occurred on line 31.
Query String: fuseaction=<program name>l&CFID=&CFTOKEN=<token>
Browser: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/36.0.1985.143 Safari/537.36
SQL Error: insert into employee ( employee_id, …) values ( 10000, ….)
The message looked something like above. I immediately noticed the CFID etc. That gave me the clue that it was a Coldfusion program (we still use it). And it tells me, there is a unique constraint violation – meaning you try to insert a value of ID field that already exists in the table.
I’ve seen this before. I took a guess and assumed it was using a Sequence. This kind of error can happen, if Sequence sort of falls behind the actual values for the ID in the table. This is my reply to the DBA:
Here is a little more detail from the Error message you sent me:
- As you know, the error message is basically saying that the ID value is already there in the table.
- Index mentioned in the Unique Constraint violation message is EMPLOYEE. This is the primary key and the column in this index is EMPLOYEE_ID.
- The value mentioned for this field in the INSERT statement is 10000. There must already be a record for this key in the table.
- Based on a list of sequences I found in the database, I guess, it may be using a Sequence named S_EMPLOYEE_ID.
If there is no other issue with the code and the records, this problem could typically happen when the sequence used for getting the ID value for the column falls out of sync with the actual value in the column. Mixing Sequence and Non-Sequence insert could result in this too. Chances are the program may be using Sequence to insert into the table in some places and some other (possibly Max(ID) + 1) approach in other places.
For e.g., in the development database Victoria gave me, I saw the following:
Max(EMPLOYEE_ID) in the table, EMPLOYEE = 10000
Current Value of the sequence S_EMPLOYEE_ID = 9900
There are rows already inserted in this table with IDs > 900. This means, if someone or program uses the Sequence to insert, it is going to get this error until the sequence is bumped up to the Max(ID) + 1.
Solution (for the DEV example above)
For the above example in DEV,
Immediate solution may be to simply bump up the Sequence to match the ID maximum.
ALTER SEQUENCE s_employee_id INCREMENT BY 100;
This will bring the Sequence next value to 10000, which is means NextVal will be than the last ID we saw, so it will not give primary key violation any more.
As you see, ALTER SEQUENCE statement only takes increment. I arrived at the increment by using below formula:
[Max(ID) in the table] Minus (-) [Current value for the Sequence]. For the above example, this is,
10000 – 9900 = 100
To obtain the current Max(ID) from the table, use below query:
SELECT Max(employee_id) FROM employee;
Note: The increment will be different for production.
A more permanent solution may be to look at the code behind (ColdFusion???) to see if there are any INSERTs into this table that uses Sequence in some place and no sequence approach in another. Otherwise, this can happen again.
Oracle Sequences are a unique animal. This seems to get a lot of developers. We had one issue at work today. I will post that separately.
If you come from other databases, you are probably used to Auto Incrementing columns. Oracle does not have that. Short of that, it has a Sequence.
A Sequence is just a counter – a separate object that exists in the schema. It has no connection to any specific column, but by naming convention, we tend to associate it to a particular index column in a table. All it is, is a counter that gets bumped up each time you call a
function (NextVal) to increment it. In a typical production Oracle database, you will find a lot of Sequence objects, one for each ID (primary index) column in the tables.
Usage of sequence is somewhat like shown in the diagram below.
A sequence is an Oracle Schema object that has the following:
A Start or Initial Value is the value the Sequence will have initially. Current Value is the counter’s value at any time. A Sequence also has a max. Value that it can increment to, after which it can be made to recycle back to 0 (or 1).
So, you can see how it works – all you have to do is to get the current value and bump it up each time. That’s exactly what the NextVal function does. As a safe guard, you cannot use CurrentVal until you call NextVal first, so you don’t end up using a previously used value, even by accident.
Here are some examples:
CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
See here for a full explanation. When you don’t need it any longer, just DROP it.
As for using the Sequence, you just have to do Sequence.NextVal. To do this in SQL, you can use DUAL table,
SELECT customers_seq.NextVal FROM dual;
Or you can simply embed in an INSERT statement, where it’s typically needed (when you are creating a new record in the table).
INSERT INTO customers(customer_id, customer_name) VALUES(customers_seq.NextVal, 'Test');
This site talks about using it in various scenarios of PL/SQL.
Sometimes, you may want to change a sequence – to do this, you use ALTER Sequence. ALTER is used even for incrementing the Sequence counter. See here.