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 for update;
session 2
update lsc_t set id=3;
Session 2 waits until transaction in session 1 finishes.
But in case the update is using a subquery that access a remote table, the update will fail after 1 minute
session 1
select * from lsc_t for update;
session 2
update lsc_t set x=(select * from lsc_t@db02);
Session 2 will end with ORA-02049: timeout: distributed transaction waiting for lock
The timeout of one minute is defined by distributed_lock_timeout, and it is not modifiable in your session (chances are, you will not be able to modify this parameter).
sql
lock
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 limit it to the minimum, I noticed that, in Oracle, 1/3*3 is not 1 !
SQL> select * from dual where 1/3*3=1
no rows selected.
Let me tell you, this was unexpected. Ok, on my first pocket calculator 30 years ago, I noticed than 1/3=.3333333 and .3333333*3=.9999999. But since then, I used to expect 1.
perl :
$ perl -e 'print 1 if 1/3*3==1'
1
OK, I tried in java
public class X {
public static void main(String[] args){
if (1/3*3<1) {
System.out.println("<n");
}
}
}
$ javac X.java
$ java X
<
In java it seems different too.
So what is 1/3*3 in Oracle?
SQL> set numw 50
SQL> select 1/3*3 from dual;
1/3*3
--------------------------------------------------
.9999999999999999999999999999999999999999
Surprised?
sql
math
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) c
from (
select
'TEST' env,name,type,line,text
from
user_source
union all
select
'PROD' env,name,type,line,text
from
user_source@prod
)
)
where c>1 ;
where “PROD” is a database link to production database. My security friends will scream to hear I have a database link to production in my test schema, but you are free to do it the other way round 
I also explained TOAD method in how to compare schema a few months ago.
dba, sql
source code
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 functionality of SQL. Actually, many of those techniques allow you to write “shorter” statements than before (by avoid redundant subqueries) and also to gain performance, occasionnaly.
However the downside is that you will end up writting some code that only you can read.
In my opinion a zero-plsql approach is wrong. If you have a problem similar to Iggy Fernandez, then no no no, do not even try to solve it with SQL unless you are really doing this for fun
In my humble opinion, plsql and sql and complementary. If you want to program, use plsql. If you need to retrieve data, use sql.
And thanks a lot to Iggy for the excellent challenge !!!
fun
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 
dba, sql
immediate crash
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
select 1 from c;
Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context
My basic query was actually joining two dictionary views, and NLS_CHARSET_DECL_LEN is used in USER_TAB_COLUMNS.CHAR_COL_DECL_LENGTH.
Kind of
with c as (
select table_name, column_name, constraint_name
from user_tab_columns
join user_constraints
using (table_name)
where constraint_type='P'
)
select
constraint_name,column_name
from c
union all
select
column_name,constraint_name
from c;
Error at line 1
ORA-02070: database does not support operator NLS_CHARSET_DECL_LEN in this context
I spent some time to understand the reason for getting an ORA-2070 in a simple query that did seem to be totally unrelated to charset. The database used for this test is 9.2.0.8/Solaris.
It is quite easy to avoid the bug, for instance by not using the ANSI join 
When analyzing such a defect, my approach is to simplify the query as much as possible. Very often on forums you see users posting queries of more than 100 lines. Those users just expect the community to debug their code. Posting a tiny reproducible test case is key to receive a proper solution!
sql
error
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
create table lsc_t(x number);
insert into lsc_t(x) values (2);
commit;
Remote databases DB03
create table lsc_t(x number);
insert into lsc_t(x) values (3);
commit;
Local database DB01
create or replace type lsc_t_o as object(x number);
/
create or replace type lsc_t_c as table of lsc_t_o;
/
create or replace function lsc_f(str varchar2)
return lsc_t_c is
rc lsc_t_c;
begin
execute immediate
'select lsc_t_o(x) from '
|| dbms_assert.qualified_sql_name(str) bulk collect into rc;
return rc;
end;
/
Note the DBMS_ASSERT function. The DBMS_ASSERT just enhances the security by prevent SQL injection
Ok, let’s try
SQL> var db_link varchar2(255);
SQL> exec :db_link := 'DB02';
SQL> select x from table(lsc_f('LSC_T@'
||:db_link)) where x>0;
X
----------
2
SQL> exec :db_link := 'DB03';
SQL> select x from table(lsc_f('LSC_T@'||:db_link)) where x>0;
X
----------
3
Seems to work 
sql
db_link