Hardcoding SYSDATE

I see TRUNC(SYSDATE) in the code very often. Is this good or bad?

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

On Express Edition

If you want to start with developing free software on old technology, you can download the Oracle Database 10g Express Edition.

Actually, Oracle 10g is more than 5 years old and it is the only version available as Express Edition, 11g has not been released at the time of the writing, and no patch has been released, not even for the worst bugs or security breaches.

Most probably you will not want to run this for your sensitive data.

If you are serious about Oracle Development, you could download the Enterprise Edition for free, and get the Personal Edition on Oracle Shop for support and patches.

Last Friday 13th August, a news/rumor (not an official announcement) was posted on the opensolaris forum, apparently OpenSolaris has been cancelled in favor of an Oracle Solaris 11 Express. Not sure if it will come in the same format as the database XE, but this does not sound to please the community.

After the Oracle sues Google, it does not make Oracle very popular in the opensource community at the very moment

YMMV

to cvs or to subversion

First surprise, after migration, the size of my subversion folder is double the size of my cvs folder. With a bunch of 2Gb disks shared amoung dozens of unix persons, and regular reminders the current usage reached 100%, you will feel the pain of having each developers doublesizing its home directory…
The reason is a .svn/test-base directory containing a duplicate of your local copy.
The benefit there is that it reduces network usage, for instance when diffing with the working version.

Second surprise, not sure if it is a generic issue or related to the one above, a full checkout take ages :( .

svn and cvs command line interfaces almost have the same options, a bit like vi and vim -vi is my favorite- but the “tagging” massively differs. The is no cvs tag command in subversion, you use a different syntax.
cvs :


$ cd /home/lsc/CVS/proj1/source/pkg
$ cvs tag MYTAG helloworld.pkb helloworld.pks

now enjoy the power of subversion! [updated as I found the --parents option]

$ cd /home/lsc/SVN/proj1/source/pkg
$ svn copy --parents helloworld.pkb http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"
$ svn copy --parents helloworld.pks http://myserver.domain.com/svn/REPOS1/proj1/tags/MYTAG/proj1/source/pkg -m "my first svn tag"

I always loved the cvs rename command

$ cvs rename pkg1.pkb pkg2.pkb
cvs [rename aborted]: Remote server does not support rename

Joke apart, in CVS I used to physically logon to the server and manually move the pkg1.pkb,v to pkg2.pkb,v
Renaming works in subversion :

$ svn move pkg1.pkb pkg2.pkb
A         pkg2.pkb
D         pkg1.pkb

Update: One more annoyance, you cannot checkout a single file :(
subversion faq

extract xml from the command line

I just discovered this morning this cool utility in my /bin directory : xmllint

You can use it to extract values from your xml files within your shell scripts


$ cat foo.xml
<emplist>
  <emp no="1">
    <ename>John</ename>
  </emp>
  <emp no="2">
    <ename>Jack</ename>
  </emp>
</emplist>
$ echo 'cat //emplist/emp[@no="1"]/ename/text()'|
  xmllint --shell foo.xml |
  sed -n 3p
John

I like this !

connect by and recursive with (part 2)

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

  1. Create a big emp
  2. 
    create table big_emp as
    with t(empno,mgr) as
    (select 1, null from dual
    union all 
    select empno+1,trunc(dbms_random.value(1+empno/10,empno))
    from t 
    where empno<100000)
    select * from t
    

  3. execution plan
  4. CONNECT BY

    
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null;
    
    E M 
    - -
    1 -  
    2 1 
    4 2 
    6 2 
    8 6 
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  185      78
    CONNECT BY WITH FILTERING
    TABLE ACCESS FULL         BIG_EMP      1    1   61      10
    HASH JOIN                              2    2  122      46
    CONNECT BY PUMP
    TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000
    

    recursive WITH

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select empno,mgr
    from e;
    
    E M
    - -
    1 -  
    2 1 
    3 1 
    4 2 
    5 3 
    ...
    
    Operation                 Object    Rows Time Cost   Bytes
    ------------------------- ------- ------ ---- ---- -------
    SELECT STATEMENT                       3    3  183      78
    VIEW                                   3    3  183      78
    UNION ALL (RECURSIVE WITH) BREADTH FIRST
    TABLE ACCESS FULL BIG_EMP              1    1   61      10
    HASH JOIN                              2    2  122      46
    RECURSIVE WITH PUMP
    TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000
    

    In this particular simple case, it seems CONNECT BY have a 1% higher cost.

  5. execution time
  6. CONNECT BY

    
    select sum(mgr)
    from 
    (
    select empno,mgr
    from big_emp
    connect by mgr = prior empno
    start with mgr is null
    )
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 0.73 seconds 
    

    recursive with

    
    with e(empno,mgr) as (
    select empno, mgr 
    from big_emp 
    where mgr is null
    union all
    select f.empno,f.mgr 
    from big_emp f, e 
    where e.empno=f.mgr)
    select sum(mgr)
    from e;
    
    SUM(MGR) 
    ------------
    2745293877 
    
    1 rows returned in 1.24 seconds 
    

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

CONNECT BY and Recursive CTE

11gR2 introduced a new mechanism to build up hierarchies.

I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on amis.nl.

Ok, here is my graph

The 10g query


with o as
(
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
  sys_connect_by_path(obj||
'->'
||link,','),
  connect_by_iscycle,
  connect_by_isleaf
from o 
connect by nocycle obj=prior link
start with obj='A';

ROOT LEVEL O L PATH                 CYCLE  LEAF
---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does not work that well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.


with o(obj,link) as
(
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
select 
  t.root,t.lev+1,o.obj,o.link,
  t.path||', '||o.obj||
    '->'
    ||o.link
from t, o 
where t.link=o.obj
)
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    case
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good :)

If you exclude the rows with cycle=1, you get the six rows for the graph.

read without Enter

A small unix tip today.

Do you want to continue ?

If you are expecting “y” or “n” but do not want to enforce the user to type y[Enter] but simply y, you can use the -n option in bash.

Within a ksh script:


yorn=$(bash -c 'read -p "Do you want to continue ? " -n 1 ans;echo "$ans"')

Within bash:


read -n 1 yorn

number series

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries ;)


with t(x) as (select 1 from dual 
union all
select x+1 from t where x<5 )
select x from t;

X
1
2
3
4
5

with t(x,y) as (select 1 x, 1 y from dual 
union all
select x+1,y*(x+1) from t where x<5 )
select x,y "X!" from t;

X  X!
1  1
2  2
3  6
4  24
5  120

with t(r,x,y) as (select 1,1,1 from dual 
union all
select r+1,y,x+y from t where r<5)
select x fib from t

FIB 
1 
1 
2 
3 
5 

with t1(x) as (select 1 from dual 
union all
select x+1 from t1 where x<4),
t2(x,y,z) as (select x, 1,x from t1
union all 
select x,y+1,x*(y+1) from t2 where y<2)
select listagg(z,';') within group (order by x) s from t2 group by y;

S 
1;2;3;4 
2;4;6;8 

where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?


SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /
create trigger u2.tr after insert on u1.t for each row
                                        *
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

 
U2@lsc01> connect / as sysdba
Connected.
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01> 
SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

cd

Do you know cd ? I thought I did until this afternoon …

OK, let’s start some basic.

I create two directories

$ echo $SHELL
/bin/ksh
$ mkdir /tmp/foo
$ mkdir /tmp/bar

create a symlink /tmp/bar/baz pointing to /tmp/foo

$ ln -s /tmp/foo /tmp/bar/baz

create a file foo1 in foo

$ touch /tmp/foo/foo1

change to the symbolic link baz

$ cd /tmp/bar/baz

Ok, so far so good. Let’s check what is in ../foo

$ ls ../foo
foo1

From the symbolic baz, .. point to /tmp/foo. This is because ls and most command line utilities use the physical path.

To print the Logical [default] and Physical working directories, use pwd -L and pwd -P

$ pwd -L
/tmp/bar/baz
$ pwd -P
/tmp/foo

to change directory relatively to the logical path, use cd -L … (default), for physical, use cd -P … !

$ pwd -L
/tmp/bar/baz
$ cd -L ../foo
ksh: ../foo:  not found

Obviously /tmp/bar/foo does not exist

$ pwd -L
/tmp/bar/baz
$ pwd -P
/tmp/foo
$ cd -P ../foo
$ pwd
/tmp/foo

Obviously /tmp/foo/../foo is /tmp/foo

So far so good, some of you may know that already.

Let’s bring some devil element in play

$ bash

Arghh!!! Ôôôôôôôôôôôôôô râge, Ôôôôôôôôôôôôôô désespoir, I switched to a non-working shell!

$ cd /tmp/bar/baz
$ cd -L ../foo
$ pwd -L
/tmp/foo

Even if I switched to a not working directory, bash cd -L weirdly decided to switch to the physical path instead of the logical path.

Let’s retry

$ cd /tmp/bar/baz
$ mkdir /tmp/bar/foo
$ cd -L ../foo
$ pwd
/tmp/bar/foo

This time bash cd -L changed to the logical path. So if you use bash and cd, you cannot possibly know where you are landing without checking first if the directory exist !

BTW, I just discovered Digger HD , unrelated to this post of course …

where c not in (:b1, :b2, … , :b9999)

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in


create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND


SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where 
c!=:b1 and
c!=:b2 and
...
c!=:b9999;

FOO
---
foo

Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

Book review: oracle sql developper

Sue Harper is the product manager for Oracle SQL developer. She is also the author of a book, Oracle SQL Developer 2.1 .

A bunch of Oracle blogger received a free online version of the book with the task to write a review.

I have read a few chapters and here are my general comments.

buy from amazon
+ The picture on the book is very nice, not related to SQL Developer, but shot by Sue :) .

- There is no chapter on unit testing, which is one of the newest feature of SQL Developer. It is not very mature yet, but it is a good addition and free.

+ The book is not a marketing joke, the positioning against tool like TOAD is quite the way I see it.
page 1: The skeptics mentioned are ever concerned that Oracle is not really interested in the product,
backing up this concern with the fact that SQL Developer is free.

+ Even as a sqlplus/sh fanatic I did not feel ignored
page 6: We’re aware that you’ll never fully move from the command-line

- If you are a bit of a gray hair security admin you will be shocked by statements like page 21: create a connection for SYSTEM and page 22: Select the Save Password checkbox. This makes life easy. Hmm, do you give SYSTEM access to your developers and do your security policies recommend/allow them to save the password locally?

+ Still SQL Developer is good tool and it is driven by community. Save password is no longer the default (an old thread). You can post your own suggestions like Use Wallet for Logins to the SQL Developer Exchange.

+ There are plenty of good features in this tool and the book covers them. I like reports for instance.

= The book is still a book about click-click-click. Most of my readers here know how to drag and drop and resize windows.

PS: if it was not obvious in my comments, yes I do like this book!

on analytics and superaggregation

When I wrote my book, I did not expect having the advanced Oracle sql features available on other dabatase engine.

This week-end I downloaded for fun a try of db2, v9.7.
1) download db2 linux64bit trial on ibm.com
2) install enterprise edition (next-next-install principle)
3) create the instance
4) create the sample database (where database and instance means something different than in Oracle)

$ db2 create db test pagesize 32 k
$ db2 connect to test
$ db2 create user temporary tablespace temp
$ db2 connect reset

5) connect with clpplus, which looks similar to sqlplus, even cooler at first look
$ clpplus db2inst1/***@vaio:50001/TEST

I tried a few examples from my book. Left, full and cross join works, but not partitioned outer join.

Scalar, Inline and nested query works too.

Surprisely many function that I thought Oracle specific work too, like sysdate and to_char.

In aggegation, the KEEP FIRST/LAST does not work,
In Super aggregations, all GROUPING SETS, CUBE, ROLLUP do work.
No Pivot or Unpivot.

No dual, but a powerful TABLE function

SQL> select * from table(values(1),(2));       
          1
-----------
          1
          2

Analytics works, With range, rows, order by partition, all the fancy stuff.

I have not checked at XML, but it sounds to be differently implemented.
Some things will work however

SQL> select xmlquery('1 to 10') from dual;

1                   
--------------------
1 2 3 4 5 6 7 8 9 10

No CONNECT BY, but as in Oracle 11gR2, recursive query factoring does build the hierarchy

No Model, obviously.

Oracle – Sun, what has changed ?

Did you go to http://www.sun.com recently? You will land on oracle.com !

But more will come.

The conference JavaOne will be in SanFrancisco with Oracle OpenWorld.

The Sun Developer Network and Bigadmin will be integrated in OTN.

Welcome Sun Developers Ensuring community continuity

MySql and java.sun.com have the Oracle logo.

That was fast! Well, Oracle is quite used to acquisition, only this week they bought AmberPoint and Convergin.

Still MySQL strategy will probably change, I cannot believe Oracle will encourage the users to save millions on Oracle License by migrating to MySQL

http://www.mysql.com/products/enterprise/unlimited.html

what is the type of NULL

I was a bit surprised to see a VARCHAR2(0) column in my schema


select column_name, data_type, data_length 
from user_tab_columns 
where table_name='V';
COLUMN_NAME     DATA_TYPE            DATA_LENGTH
--------------- -------------------- -----------
X               VARCHAR2                       0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
VIEW v
 Name              Null?    Type        
 ----------------- -------- ------------
 X                          VARCHAR2(0) 

How many decimals do you need?

Do you user NUMBER or NUMBER(p,s) for your datatypes?

I posted last year about 1!=1

Today I realized this could be solved with the scale :)

SQL> drop table lsc_t
Table dropped.
SQL> create table lsc_t(x number, y number(*,6))
Table created.
SQL> insert into lsc_t values (1/3*3,1/3*3)
1 row created.
SQL> commit
Commit complete.
SQL> select * from lsc_t where x=1
no rows selected.
SQL> select * from lsc_t where y=1

         X          Y
---------- ----------
1.00000000          1

So if you have amounts in US$ or GB£ or CHF, do not use NUMBER, use NUMBER(*,6) or NUMBER(*,2) or whatever is relevant to your business!

dealing with support

It is quite a long time I did shot the pianist in my blog… probably patience and perseverance are better than aggressive behavior, but today I have been transfixed by an answer from metalink.

Sometimes the issues I submit are a bit exotic but there it was a join with one view using one function and dual returning wrong results. Reproducible at will on any db 10.2.0.4 and below.

After 2 weeks the helpful engineer told me it is fixed with patch 6471020 and in 11g. I asked if he tried it but he said no. As the patch is not available on my platform I asked if could try it but then he came back with an amazing workaround he became from an ARE (advanced resolution engineer) :

Remove the function from the view.

Unreal

.plz dump file

What are those .plz dump files in my user dump directory ?


-rw-r--r--   1 oracle   dba        15168 Oct  6 14:34 _anon__3ca8c5e38__AB.plz
-rw-r-----   1 oracle   dba        15883 Oct  6 14:45 db01_ora_10061.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:45 _anon__3c929b088__AB.plz
-rw-r-----   1 oracle   dba        15895 Oct  6 14:47 db01_ora_10666.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:47 _anon__3c8651198__AB.plz

let’s check one, briefly :


*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]

== Dump of OPT Context Object ffffffff7c519ec8. ==
  Tue Oct  6 16:31:11 2009
  Event 10944 = 0
  plsp          = ffffffff7fff67a8
  lu            = 3c9c18210
  Diana root    = 0x20014 = 131092
  Diana proc    = 0x20012 = 131090
  Graph         = ffffffff7d774d70
  Dump file     = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
  CG            = 0
...

It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code

create or replace type t1 as object (x number)
/
create or replace type t2 as table of t1
/
exec if t1(1)member of t2()then null;end if

BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]

jdbc hello world

I am in a java mood today, let’s check how to print hello world with jdbc :)

import java.sql.*;
public class HelloWorld {
  public  static void main(String[] args) throws SQLException {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    ResultSet res = DriverManager.
      getConnection("jdbc:oracle:thin:@srv1:1521:DB01", "scott", "tiger").
      prepareCall("select 'Hello World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

let’s compile
javac -classpath $ORACLE_HOME/jdbc/lib/classes12.jar HelloWorld.java

and run

$ java -classpath $ORACLE_HOME/jdbc/lib/classes12.jar:. HelloWorld
Hello World

that’s all folks!

11.1.0.7 is the latest patchset for 11gR1

You will get CPU and even PSU (Patch Set Updates are proactive cumulative patches comprised of recommended bug fixes that are released on a regular and predictable schedule)

But no more patchset. This the first time I see a release with only 1 pachset.

Well, if you use Linux, you can go to 11gR2. Otherwise you may better stick to 10.2.0.4 for a few more months

Ref: 742060.1
11.1.0.7 is the last patch set for Release 11.1

cd OLD NEW

Something I like in ksh is to change from /my/old/directory/path to /my/new/directory/path by typing cd old new. This does not work in bash

So I had to find a workaround ;)


$ ksh
$ cd /app/oracle/product/11.1.0.6/database/rdbms/admin
$ cd 6 7
$ pwd 
/app/oracle/product/11.1.0.7/database/rdbms/admin
$ bash
$ cd 7 6
bash: cd: 7: No such file or directory
$ c() { cd ${PWD/$1/$2}; }
$ c 7 6
$ pwd
/app/oracle/product/11.1.0.6/database/rdbms/admin

CONNECT no longer has CREATE VIEW, what’s next?

In my current project we are going to move from 9i to 10g really soon. One of the typical issue is to get the right privileges, because the schema owners typically had only CONNECT and RESOURCE, and that does no longer include CREATE VIEW in 10gR2.

I was just reading the 11gR2 readme today :
7.2 UNLIMITED TABLESPACE Privilege Changes
The UNLIMITED TABLESPACE system privilege will be removed from the RESOURCE role in a future Oracle Database release (reference Bug 7614645).

So, probably I should ask for TABLESPACE quotas before we go to 12g ;)

stragg in 11gR2

This will be a killer in the Oracle forums ;)

LISTAGG (measure_expr [, 'delimiter_expr'])
  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

le compte est bon

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :

SQL> /
Enter value for n1: 1
old  14:      (SELECT &n1 n
new  14:      (SELECT 1 n
Enter value for n2: 3
old  17:       SELECT &n2 n
new  17:       SELECT 3 n
Enter value for n3: 4
old  20:       SELECT &n3 n
new  20:       SELECT 4 n
Enter value for n4: 6
old  23:       SELECT &n4 n
new  23:       SELECT 6 n
Enter value for result: 49
old 143:              ) = &result
new 143:              ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple :-) at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
   RETURN NUMBER
IS
   x   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
               USING OUT x;
   RETURN x;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

I will post the rest of the code as a comment later ;)

Oracle Certified Master