locking and database link

Last week-end a new release went to production. This week we got plenty of ORA-2049. What in the hell went wrong? And why did not we find out this locking before? We just did not have that concurrency effect in test. How does locking work ? With regular tables: session 1 select * from lsc_t […]

1!=1

In the process of validating some calculation, I noticed some strange differences from one run to another run, with exactly the same data and exactly the same function. I suppose the only difference was the order in which the transactions were processed (and since I had no ORDER BY, the order was not deterministic). To […]

package version control

Oracle does not offer any kind of version control like CVS or subversion in the database. CVS and subversion could be used in the filesystem, then the code could be deployed with sqlplus. To quickly compare packages in Test and Production I used : select env,name,type,line,text from ( select env,name,type,line,text, count(distinct text) over(partition by name,type,line) […]

to SQL or to PLSQL

Iggy Fernandez posted a riddle to be solved without PLSQL http://www.amazon.com/gp/blog/post/PLNKI2MYB0YCYAUL I tend to second Steven Feuerstein argument : Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. In my book I mentionned techniques that enhance the basis […]

ORA-2070

Today I received an error which was totally new to me. And totally unexpected… For the purpose of this post, I reduced it to a minimum CREATE TABLE LSC_T1 ( x number); CREATE TABLE LSC_T2 ( x number); with c as (select nls_charset_decl_len(1, x) from lsc_t1 join lsc_t2 using (x)) select 1 from c union […]