change user in pl/sql

Just a quick note about a feature of an internal datapump package (sys.kupp$proc in prvtbpp.plb) that I stumbled upon here, tested on 11gR1:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0    Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 Zeilen ausgewählt.

SQL> conn cneu/xxxxxxxxxxx
Connect durchgeführt.

SQL> select user from dual;

USER
------------------------------
CNEU

1 Zeile wurde ausgewählt.

SQL> exec sys.kupp$proc.change_user('SYS')

PL/SQL-Prozedur erfolgreich abgeschlossen.

SQL> select user from dual;

USER
------------------------------
SYS

1 Zeile wurde ausgewählt.

Execute is granted to EXECUTE_CATALOG_ROLE, which is available to DBA,
IMP_FULL_DATABASE, EXP_FULL_DATABASE and SYS. My CNEU user has DBA rights.

CLOB to VARCHAR2 conversion problems on UTF8 databases

Some well-known facts:

• VARCHAR2 has a max size of 32767 characters
• UTF8 is a multi-byte character set, one character uses up to 4 bytes
• 32767/4 = 8191.75

It looks like there are conversion problems when copying from a CLOB to a VARCHAR2 on UTF8 databases. If the VARCHAR2 gets assigned a CLOB (either directly or via SUBSTR(clob,...)) of more than 8191 characters, this raises VALUE_ERROR (see example below). If reading via DBMS_LOB.SUBSTR, the result gets truncated to 8191 characters. Reading via DBMS_LOB.READ works as expected.

An annoying inconsistency. DB was 11.1.0.6 on Windows.

CN@cn> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';


VALUE
----------------------------------------------------------------
WE8MSWIN1252
 
CN@cn> declare
2 c clob;
3 v varchar2(10000);
4 begin
5 for i in 8191 .. 8192 loop
6 v := rpad('abc',i);
7 c := v;
8 dbms_output.put_line('Trying length '||i||'...');
9 v := c;
10 dbms_output.put_line('...ok');
11 end loop;
12 end;
13 /
Trying length 8191...
...ok
Trying length 8192...
...ok
PL/SQL-Prozedur erfolgreich abgeschlossen.
 
CN@cn> conn cn/test@cnutf
Connect durchgef▒hrt.
CN@cnutf> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
 
VALUE
----------------------------------------------------------------
AL32UTF8
CN@cnutf> declare
2 c clob;
3 v varchar2(10000);
4 begin
5 for i in 8191 .. 8192 loop
6 v := rpad('abc',i);
7 c := v;
8 dbms_output.put_line('Trying length '||i||'...');
9 v := c;
10 dbms_output.put_line('...ok');
12 end;
13 /
Trying length 8191...
...ok
Trying length 8192...
declare
*
FEHLER in Zeile 1:
ORA-06502: PL/SQL: numerischer oder Wertefehler
ORA-06512: in Zeile 9

Writing parsers with m4o

This is a short example of my latest addition to the m4o toolchain. With m4o_lexer you can quite easily write simple LL(1) recursive descent parsers for domain-specific languages in PL/SQL. The lexer is implemented via regexp_substr and regexp_instr. This probably means that performance is not great, but for small code that's not an issue.

@package-begin sample_parser
  @plsql
    c_equal constant m4o_lexer.token_t := 1;
    c_plus  constant m4o_lexer.token_t := 2;
    c_begin constant m4o_lexer.token_t := 3;
    c_end   constant m4o_lexer.token_t := 4;
    c_if    constant m4o_lexer.token_t := 5;
    c_then  constant m4o_lexer.token_t := 6;
    c_else  constant m4o_lexer.token_t := 7;
    c_ident constant m4o_lexer.token_t := 99;
    c_num   constant m4o_lexer.token_t := 100;
    procedure parse_stmtseq;
  @end
--------------------------------------------------------------------------------
  @procedure parse_assign
  @declare
    v_var   varchar2(30);
    v_value varchar2(30);
  @begin
    -- [ident] = [num]
    v_var   := m4o_lexer.text;
    m4o_lexer.eat(c_ident);
    m4o_lexer.eat(c_equal);
    v_value := m4o_lexer.text;
    m4o_lexer.eat(c_num);
    dbms_output.put_line('assigning '
                       ||v_value
                       ||' to '
                       ||v_var);

  @end
--------------------------------------------------------------------------------
  @procedure parse_if
  @begin

    m4o_lexer.eat(c_if);
    m4o_lexer.eat(c_ident);
    m4o_lexer.eat(c_equal);
    m4o_lexer.eat(c_num);
    m4o_lexer.eat(c_then);
    parse_stmtseq;
    if m4o_lexer.cur = c_else then
      m4o_lexer.eat;
      parse_stmtseq;
    end if;
    m4o_lexer.eat(c_end);
  @end
--------------------------------------------------------------------------------
  @procedure parse_stmtseq
  @begin
    loop
      case m4o_lexer.cur
      when c_if then
        parse_if;
      when c_ident then
        parse_assign;
      when c_begin then
        m4o_lexer.eat;
        parse_stmtseq;
        m4o_lexer.eat(c_end);
      else
        exit;
      end case;
    end loop;
  @end
--------------------------------------------------------------------------------
  @procedure parse*
    i_code in varchar2
  @begin
    m4o_lexer.begin_define_tokens;
    m4o_lexer.set_whitespace('[[:space:]]+');
    m4o_lexer.set_token(c_equal,'=');
    m4o_lexer.set_token(c_plus ,'\\+');
    m4o_lexer.set_token(c_begin,'begin');
    m4o_lexer.set_token(c_end  ,'end');
    m4o_lexer.set_token(c_if   ,'if');
    m4o_lexer.set_token(c_then ,'then');
    m4o_lexer.set_token(c_else ,'else');
    m4o_lexer.set_token(c_ident,'[a-z][a-z0-9_#$]*');
    m4o_lexer.set_token(c_num  ,'[0-9]+');

    m4o_lexer.begin_reading(i_code);

    m4o_lexer.eat(c_begin);
    parse_stmtseq;
    m4o_lexer.eat(c_end);

    m4o_lexer.end_reading;
  @end
--------------------------------------------------------------------------------
  @procedure main*
  @begin
    parse(
      'begin

        i = 3
        IF FOO = 7 THEN
          I = 5
          bar = 10
        else begin b=0 end end
      end');
  @end
@package-end

hanging an oracle process: easy as that

Because it's labour day, I did some work on meta-pl/sql again. It was refactoring time and soon I will add another language feature - macros. But that's another topic.

Due to a silly typo, I had a hanging oracle process (one of the nasties where "alter system kill session" does not work):

create or replace package hang as
  subtype t is hang.t;
end;
/

So it was time to freshen oradebug skills.

I only tested the hangs on my local development machine (11gR1 linux), but remember similar troubles due to dependencies between views and packages on other releases, too.

Hello Meta-PL/SQL, Hello M4O

Despite the temptation of playing with the APEX 4.0 preview, I used the winter holidays to clean up and finally publish an open source project. It's called M4O and implements a new programming language, Meta-PL/SQL, which is an extension of Oracle's PL/SQL. It features a simpler package syntax, aspect oriented programming and language extensibility.

Further information is available on http://code.google.com/p/m4o/.

Here's a teaser of what Meta-PL/SQL looks like:

@package-begin greeter_pkg

  @procedure hello_dbms_output*

    i_name in varchar2

  @begin

    dbms_output.put_line('Hello, ${nvl(i_name,'stranger')}!');

  @end

  @procedure hello_web*

    i_name in varchar2

  @begin

   <h1>Hello, <%=nvl(i_name,'stranger')%>!</h1>

  @end

@package-end

generates

create or replace package greeter_pkg as

  procedure hello_dbms_output(i_name in varchar2);

  procedure hello_web(i_name in varchar2);

end;

create or replace package body greeter_pkg as

  procedure hello_dbms_output(i_name in varchar2)

  is

  begin

  -- log that greeter_pkg.hello_dbms_output was called with   i_name

    dbms_output.put_line('Hello, '||nvl(i_name,'stranger')||'!');

  end;

  procedure hello_web(i_name in varchar2)

  is

  begin

  -- log that greeter_pkg.hello_web was called with i_name

    htp.p('<h1>Hello, '||nvl(i_name,'stranger')||'!</h1>');

  end;

end;

Please note that this is a very early release and a few planned features are missing. A previous version of Meta-PL/SQL has been in production use since summer 2008, however.