Saturday, December 18, 2010

Valid or not?

I ran into an SQL-statement that was valid in test running Oracle 11.2.0.1 but not valid in production also running Oracle 11.2.0.1!? Since I didn't have access to v$parameter in production it took a while to figure out... Here it is (note how using the ANSI-join syntax indirectly allows a feature that is otherwise not allowed):
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> REM Set default value optimizer features enable
SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.1';
Session er ændret.

SQL> DROP TABLE t2 PURGE;
Tabel er droppet.

SQL> DROP TABLE t1 PURGE;
Tabel er droppet.

SQL> CREATE TABLE t1(
2 t1_id INTEGER NOT NULL
3 ,t1_txt VARCHAR2(20) NOT NULL
4 ,CONSTRAINT t1_pk
5 PRIMARY KEY(t1_id)
6 );
Tabel er oprettet.

SQL> CREATE TABLE t2(
2 t2_id INTEGER NOT NULL
3 ,t1_id INTEGER NOT NULL
4 ,t2_val NUMBER(2,0) NOT NULL
5 ,CONSTRAINT t2_pk
6 PRIMARY KEY(t2_id)
7 ,CONSTRAINT t2_fk_t1
8 FOREIGN KEY(t1_id)
9 REFERENCES t1(t1_id)
10 );
Tabel er oprettet.

SQL> INSERT INTO t1 VALUES(1,'Txt1');
1 række er oprettet.

SQL> INSERT INTO t1 VALUES(2,'Txt2');
1 række er oprettet.

SQL> INSERT INTO t1 VALUES(3,'Txt3');
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(1,1,1);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(2,2,1);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(3,2,2);
1 række er oprettet.

SQL> INSERT INTO t2 VALUES(4,2,3);
1 række er oprettet.

SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 ,t2
4 WHERE t2.t1_id(+)=t1.t1_id
5 AND t2.t2_val(+) IN (1,2)
6 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3

SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 LEFT JOIN
4 t2 ON (t2.t1_id=t1.t1_id AND t2_val IN (1,2))
5 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3


SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='9.2.0';
Session er ændret.

SQL> REM Same SELECTs as above, but now...
SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 ,t2
4 WHERE t2.t1_id(+)=t1.t1_id
5 AND t2.t2_val(+) IN (1,2)
6 ;
AND t2.t2_val(+) IN (1,2)
*
FEJL i linie 5:
ORA-01719: ydre sammenkædninger (+) er ikke tilladt i operanden for OR- eller
IN-klausuler


SQL> SELECT t1.t1_id,t1.t1_txt,t2.t2_id,t2.t2_val
2 FROM t1
3 LEFT JOIN
4 t2 ON (t2.t1_id=t1.t1_id AND t2_val IN (1,2))
5 ;
T1_ID T1_TXT T2_ID T2_VAL
---------- -------------------- ---------- ----------
1 Txt1 1 1
2 Txt2 2 1
2 Txt2 3 2
3 Txt3

Saturday, October 02, 2010

I solemnly DECLARE: NOT NULL

An interesting fact about NOT NULL (declarative) constraints, see Not NULL « Oracle Scratchpad and Ask Tom "Constraints - Table and Column Level"

SQL Data Modeler is now free!

See Data Modeler Pricing FAQ (updated September 2010).

Sunday, September 05, 2010

Tasty? PL/SQL Developer in Wine!

Running Oracle XE on your Linux? Missing your favorite PL/SQL GUI tool?? Check out this article PL/SQL DEVELOPER UNDER WINE!!!

Friday, September 03, 2010

Give me a FUNCTION that...

DECLARE
i PLS_INTEGER:=0;
maxlen PLS_INTEGER:=0;
BEGIN
FOR j IN 1..str.COUNT LOOP
IF LENGTH(str(j))>maxlen THEN
maxlen:=LENGTH(str(j));
i:=j;
END IF;
END LOOP;
RETURN i;
END;
Doesn't compile (it's sort of PL/SQL if you wonder). Lacks some details (what's that str anyway). But you get the picture (it's job interview pseudocode ;o)

Sunday, August 08, 2010

Indexing Nothing... CONSTANTly

Monday, October 12, 2009

Twitter, not Not NOT


SO SIMPLE and yet SO LOUSY!

Except NullPointerException?

Saturday, May 09, 2009

Don't always AskTom

When trying to reach AskTom from my work PC - it errors out with a PL/SQL error:

... maybe there's a WHEN OTHERS too many in there? Maybe my browser string - HTTP_USER_AGENT - has gotten too long after having installed the latest version of the .NET-framework!?

HTTP_USER_AGENT=Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30; .NET CLR 3.0.04506.648; .NET CLR 1.1.4322; InfoPath.2; .NET CLR 3.5.21022; OfficeLiveConnector.1.3; OfficeLivePatch.0.0; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729)

Internet Explorer (8) will do that to you sometimes :o(

Foreign NULLs!?

Jonathan Lewis has an interesting entry about how foreign keys handle nulls - see Foreign Keys!