Home » Databases
Category Archives: Databases
DB – So, what’s a Transaction?
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.
Courtesy life2point1 on toondoo.com
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
TRANSACTION Block
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.
For e.g,
<<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');
COMMIT;
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.
References
http://www.oracle-dba-online.com/sql/commit_rollback_savepoint.htm
https://www-304.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.sqls.doc/ids_sqs_0329.htm
http://dev.mysql.com/doc/refman/5.7/en/commit.html
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4010.htm
Gotcha: UPDATE in Oracle PL/SQL
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
AS
BEGIN
(
p_employee_nbr VARCHAR2,
p_employee_name VARCHAR2,
p_address VARCHAR2
…)
AS
UPDATE employee
SET employee_name = p_employee_name,
address = p_address,
created_dt = SYSDATE
…WHERE
employee_nbr = p_employee_nbr
;
EXCEPTION
WHEN OTHERS THEN
RAISE;
COMMIT;
END test;
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:
BEGIN
test (
‘019189097’,
‘Scott Employee’,
‘123 State St, Los Angeles’
…
);
Dbms_Output.put_line(SQL%ROWCOUNT);
END;
/
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
RAISE NO_DATA_FOUND;
END IF;
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
EXIT
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:
BEGIN
<body>
EXCEPTION …
END;
/
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
UPDATE <table
— this is a comment
<rest of the SQL>;
Oracle Sequences contd…
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;
/
Notes:
- 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
For e.g.,
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;
Oracle Pivot Operator
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:
DESCRIPTIONAMOUNT
Check
200
Credit Card
500
Debit Card
210
ACH payments
2000
Check
700
| PAY_TYPE | ||||
|---|---|---|---|---|
| CHK | CRE | DEB | ACH | CHK |
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;
| CHECK_PMT | CC_PMT | DEBIT_PMT | ACH_PMT |
|---|---|---|---|
| 200 | |||
| 500 | |||
| 210 | |||
| 2000 | |||
| 700 |
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>
| CHECK_PMT | CC_PMT | DEBIT_PMT | ACH_PMT |
|---|---|---|---|
| 900 | |||
| 210 | |||
| 2000 | |||
| 500 |
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')
);
| ‘CHK’ | ‘CRE’ | ‘DEB’ | ‘ACH’ |
|---|---|---|---|
| 900 | 500 | 210 | 2000 |
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…
Gotcha: Oracle Sequences – 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.
Gotcha: Oracle Sequences – 1
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 = 9900There 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,
DBA:
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.
DEV
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.
Contd…
Oracle Sequences
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.
Troubleshooting ODBC (Kofax)
A friend in our desktop support team is trying to install Kofax software on a Windows 2008 machine. He is using Kofax capture 10.2. Apparently, our earlier version (6.x) is working fine in production. I really didn’t know what Kofax was before. I only went to help him with the database connection issue. I often try to learn something new, by helping others with something I know.
From what I understood, the software, called Kofax Capture (previously Ascent Capture), is used to batch scan images and by OCR or even looking up in a database, it can classify the batches automatically. Here is a snippet from their own description:
Kofax Capture™ automates and accelerates business processes by capturing all types of paper and electronic documents and forms, transforming them into accurate and actionable information, and delivering it all into core business applications, processes and workflows.
Kofax with Oracle (through ODBC)
My friend was using the database look up process to be able to identify each batch. Apparently, a document with a barcode is scanned along with the batch of documents. The Bar code contains one of the keys in our database. This is what the process uses to look up the meta data in the database. To do this, he was setting up Kofax to connect to our Oracle database to pick up some meta data from our table to be attached to the image file he picked up. The software is interesting in that, you can attach a query to a document set and that will attach the corresponding meta data automatically. And all these are scriptable.
He had first set up with Oracle 11g driver. This seemed to have failed initially. When he talked to Kofax, they had suggested to downgrade to 10.2. and he installed Oracle client 10.2.3. This broke it completely, in the sense, even the ODBC datasource setup for Oracle kept giving unsupported method error. I looked at this setup a few days ago and thought there was some incompatibility with Oracle drivers on Windows 2008. In any case, I felt we had to make it to work with the latest version of Oracle client (our Server is Oracle 11g). So, I asked him to re-install the latest 11g client.
I came back again with some preparation and tools. We tried the new combination (Kofax Capture 10.2 with Oracle 11.2 driver). It kept failing – Interactive Query in the Kofax GUI client (called index.exe) against Oracle kept failing silently while the script seemed to succeed intermittently.
I really wanted to see the SQLs he was sending to the database. I didn’t know if there was any tool to capture the SQLs from ODBC. I resorted to Oracle v$ tables on the database side. I was able to see see his session and the SQLs. This was comforting. At least we know, it was actually connecting to the database. Using other tools (like sqlplus), I am now certain that Oracle client was working fine. Now, it was time to debug the ODBC/Oracle bridge.
Windows ODBC Setup
To connect to any database (or file) using ODBC, you need to setup Data Sources. To do this, you can go into Control Panel -> Administrative Tools -> ODBC Data Sources (32-bit*) option. It shows you several tabs, including File DSN, User/Machine level Data sources, Available drivers etc. One of the options is to enable trace. This comes in handy immensely while trying to troubleshoot the ODBC driver/connection. Note that the trace file is for all ODBC data sources, so make sure you are using onlyone data source at a time, to be able to debug properly.
“User DSN” refers to datasources that are available only to the current user. “Machine DSN” means it’s visible to all users logging into this machine. File DSN is a file that contains the settings saved in a file. This comes in handy to use the same setup on different machines. Drivers tab contains list of the database drivers available on this machine. There is also an option to trace entire machine. Again use with caution, if there are multiple users logged on to the machine.
Way back, ODBC settings used to be stored in a couple of ini files – ODBC.ini and ODBCinst.ini. These are now stored in registry in the following location:
HKEY_CURRENT_USER\Software\ODBC\ODBC.INI
This contains a list of drivers and their settings. HKCU contains all the datasources for the currently logged in user where as, HKLM contains the settings for the entire machine. This link shows this information for a Sybase driver.
ODBCINST contains information about the drivers installed on this machine/user.
*Our operating system was Windows 2008 32-bit. This made things a bit easier. If this was a 64-bit OS, things will be slightly different. The program that’s tied to the Control Panel item is typically odbcad32.exe. This is actually the 64-bit version of the software. If you are trying to get to the 32-bit ODBC administrator on a 64-bit machine, you will need to run the program in SysWOW64. You cannot mix the bitness. If your database client is 32-bit, then you will have to use 32-bit ODBC admin and 64-bit admin, otherwise. See here for more information. The drivers tab will only list drivers with the same bitness as the admin software. See this Microsoft note for more info.
Troubleshooting ODBC driver
First, my goal was to find out how compatible was the Oracle driver with the new ODBC drivers on Windows 2008. This way, I can find out if the driver would even work for the current setup. One of the tools I found earlier, called ConfTool from Microsoft (?), which shows the conformance/compatibility of a certain database driver to ODBC levels. Unfortunately, this only tests ODBC Levels 1 and 2.
The tool showed, while Oracle driver seems to support most of the functions, there were some features and attributes that were failing.
Next, I really wanted to see if the data source used by Kofax was working. To do this, I used was SQLRun from SQLEdit.This is a command line utility that lets you test any SQL against a specific ODBC Data Source. This is a real cool tool. I set it up, so it will use the ODBC data source we setup earlier for use with Kofax. I also used the query coming from Kofax that I captured in the database above. The goal was to see the result of issuing this query worked OK through the data source outside of Kofax. I had already made sure this query worked in Oracle SQL*Plus. Now, I am trying the same query through ODBC. This will guarantee that the ODBC to Oracle connection is OK.
You can setup all the required parameters for a test run in a file called SQLRun.ini. These parameters can also passed on commandline. SQLRun has a lot commandline options.
Here is what SQLRun.ini looks like for my test:
SQLRun.ini
[Connection]
DSN=testDB1
User=<user>
Password=<pwd>
[Settings]
Output=out.txt
Errors=error.log
Delimiter=t
Header=1
OverwriteOutput=0
OverwriteLog=0
NullIsEmptyString=0
[Script]
;File=source.sql
Statement=Select * from latax.sysparm where rownum < 2;
When it completes, if all goes well, the record(s) fetched will be placed in Out.txt file. Errors.txt file contains any errors.
I turned on ODBC Trace just before this step. I turned it off right after, so the trace file now contains all the ODBC calls generated for transaction in SQLRun. Since this worked fine, I will keep this trace as my baseline to compare against.
Troubleshooting Kofax ODBC/Oracle connection
After confirming that the ODBC setup worked fine outside of Kofax, I returned to verifying the connection from Kofax. I turned on a fresh ODBC Trace with a new trace file. Then I asked my friend to run Kofax query in the Index tool.
At the end of this test, I have 2 trace files for the same SQL run through ODBC connection to Oracle – one from command line and another through Kofax. I diffed the 2 files using Winmerge. There was the difference I was looking for.
The Kofax software was sending several additional ODBC calls. One of them was to SQLSetStmtAttr function to set SQL_ATTR_QUERY_TIMEOUT to 0 (see here for more on this). This seemed to cause the query to fail immediately. Now, why they would set the timeout to 0, I don’t know.
I wanted to increase the timeout value. I couldn’t figure out if Kofax has a setting to change this. I tried the next best thing – can this feature be disabled? I looked around and found Enable Timeout Checkbox on the data source details in ODBC Admin screen. I unchecked this and ran Kofax again with new ODBC Trace restarted. Bingo!! That seemed to have done the trick!!!! The screen got populated this time!!
When I diffed the new trace file with the one from the previous run, I noticed this time, it did try to set the Query Timeout to which the Oracle driver returned a -1, saying it’s an unsupported optional feature. From then on, Kofax seemed to have stopped setting the timeout setting before issuing a query!!!! After we did this, the query passed each time, we tried. (Oracle driver didn’t seem to support the function anyway. But, according to this page, it can be used to avoid infinite wait time to resolve locks).
When we did the same exercise with the scripted (automated) run he had in Kofax, it seemed to go through fine. The Trace output more or less matched my trace file from running the same query in SQLRun.
I really don’t know how the Kofax script was different from running the query interactively in the GUI tool. Whatever it was, the tool was creating a problem and we found a solution. But, I know, this is just a workaround. What if a query really needed timeout? I’ve asked my friend to check with Kofax as to why the discrepancy. That’s for another day 🙂
Gotcha: “BEGIN” in Oracle
If you are coming to Oracle from another database, you are in for some surprises. One of them is the key word BEGIN. They don’t mean the same in Oracle. Read on.
A TRANSACTION block
In SQL Standards they list Transaction control statements such as START TRANSACTION, END TRANSACTION and SAVE TRANSACTION. Different database vendors implement this as they see fit. For e.g., Informix always had a “BEGIN [WORK]” statement to mark the beginning of a transaction block. The block will end when an explicit COMMIT or ROLLBACK is issued or SQL ends abnormally (in which case it’s rolled back). See here.
So in non-ORACLE database (Informix for e.g) you may have,
BEGIN [WORK] --(1) INSERT.... UPDATE... DELETE... .. COMMIT [WORK]; -- **commit marks the end of the current transaction**
In Oracle there is no BEGIN [WORK] equivalent. It’s just “understood” when the first executable statement makes some changes to the database. This includes DML and DDL statements. So, the above statement block in Oracle will be written as,
-- **implicit Transaction block begins?** --(1) INSERT.... UPDATE... DELETE... .. COMMIT [WORK]; -- **commit marks the end of the current transaction**
So, there you have it. In Oracle, an implicit transaction block is started when the first INSERT statement is executed and it is not started with a BEGIN or START.
BEGIN…END in Oracle
But then, you have surely seen a BEGIN (and END) in Oracle? Yes, you are correct about that statement being there, only it’s a statement in PL/SQL not plain SQL. PL/SQL is a separate procedural programming language in Oracle, just like C or Java, only PL/SQL is specialized to handle SQL more efficiently1.
This BEGIN statement in PL/SQL can be easily confused with BEGIN [WORK] in other databases, if you are new to Oracle. A BEGIN..END block in PL/SQL is merely a syntactical collection of statements (SQL, non-SQL). They don’t relate to transactions. However, since a bunch of SQL statements inside the BEGIN..END block mark a single PL/SQL statement, a COMMIT (or ROLLBACK) after a PL/SQL block could commit (or rollback) entire list of SQL statements within the PL/SQL block, thus implying BEGIN had to do with a new transaction. Not so! Suppose you ran the below block in SQL*Plus. Let’s say you updated a table. Try SELECTing the record you just updated, in another session. You won’t see the change yet, because we have not committed the below statement. That also shows END doesn’t have anything to do with Transaction.
-- **PLSQL BLock 1** BEGIN -- **SQL statements** END; / And as another example, if you had couple of PL/SQL block (BEGIN...END) before a COMMIT is issued, statements in both those blocks are committed. -- **PLSQL BLock 1** BEGIN --**SQL statements** END; / -- **PLSQL BLock 2** BEGIN --**SQL Statements** END; / COMMIT;
Here COMMIT applies to all the statements PLSQL blocks 1 and 2, showing us that PL/SQL block does not correspond to a transaction block.
[1] I think, differentiating between SQL and PL/SQL is the biggest challenge a newcomer to Oracle faces. PL/SQL is a procedural language with control structures like if, loops etc. You can build stored programs like Oracle Procedures, Triggers, Functions and Packages only in PL/SQL. It’s tightly coupled with Oracle SQL Engine causing some confusion. I will post more on this later.
Manga Guide to what? Databases?
I
noticed this book in our local library. Manga Guide to Databases!! You know, Manga, right? Those Japanese cartoon books. According to Wikipedia,
“In Japan, people of all ages read manga. The medium includes works in a broa
d range of genres: action-adventure, romance, sports and games, historical drama, comedy, science fiction and fantasy, mystery, suspense, detective, horror, sexuality, and business/commerce, among others”.
Now, add Science and Technology to it! nostarch.com claims “finest in geek entertainment”!!!. Seems, we can get teens pay more attention to programming and databases through this. What a novel idea? This book even talked about normalization and all. Cool! I wish I had a book like that, when I was learning Computer Science!
Apparently, they have several other titles like Physics, Relativity, Statistics etc. Check it out here:
http://www.nostarch.com/catalog/manga
So, next time your kid is reading a cartoon book, don’t be fooled. Look at the title. chances are she may be actually reading Calculus!!!
Power to Build 