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 […]
Month: March 2009
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 […]
commit suicide;
Tanel Poder directed me to Miladin Modrakovic blog, I will definitely add this to my prefered feed. You have to try this : SQL> alter session set events ‘immediate crash’; alter session set events ‘immediate crash’ * ERROR at line 1: ORA-03113: end-of-file on communication channel A very decent method for killing yourself 😈
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 […]
dynamic database link
How do I select thru a database link, where the database link is not fixed? SQL> var db_link varchar2(255); SQL> exec :db_link := ‘DB02’; SQL> select x from LSC_T@:db_link where x>0; select x from LSC_T@:db_link where x>0; * ERROR at line 1: ORA-01729: database link name expected My solution using dynamic SQL Remote databases DB02 […]