PL/SQL updates in Oracle 11g

On the surface, the changes to PL/SQL look rather minor, but there is some seriously cool stuff in there. I originally intended to write a single article, but some sections got too big so I thought it was worth sectioning them out into separate articles.

As usual, I’ve tried to keep the explanations brief and include cut & paste examples.

There are a couple more things I might add, but I’m not sure if they are too niche. I’ll see what I think over the next couple of days.

Cheers

Tim…

Author: Tim...

DBA, Developer, Author, Trainer.

One thought on “PL/SQL updates in Oracle 11g”

  1. I raised an enhancement request during the beta phase to be able to use mixed notation with standard functions too

    select regexp_substr(‘a-b-c-d-e’,'(.)-(.)-(.)-(.)-(.)’,1,1,null,3) from dual;
    R

    c

    unfortunately, the following does not work

    select regexp_substr(‘a-b-c-d-e’,'(.)-(.)-(.)-(.)-(.)’,subexpression=>3) from dual;
    select regexp_substr(‘a-b-c-d-e’,'(.)-(.)-(.)-(.)-(.)’,subexpression=>3) from dual
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis

    and the workaround to use full qualification have the side effect of not recognizing the max length of

    select sys.standard.regexp_substr(‘a-b-c-d-e’,'(.)-(.)-(.)-(.)-(.)’,subexpression=>3) from dual;
    SYS.STANDARD.REGEXP_SUBSTR(‘A-B-C-D-E’,'(.)-(.)-(.)-(.)-(.)’,SUBEXPRESSION=>3)
    —————————————————————————————————————————————
    c

    Cheers,
    Laurent

Comments are closed.