Chomsky on AI

Noam Chomsky on non-political topics (despite US election days):
http://www.theatlantic.com/technology/archive/2012/11/noam-chomsky-on-where-artificial-intelligence-went-wrong/261637/1/?single_page=true

Closures in PL/SQL

Yesterday I read a few chapters of Conrad Barski's wonderful Land of Lisp. I nearly finished it months ago, but with the new job in the APEX team there was little time. While I love Lisp, PL/SQL is my main language and I sometimes miss the expressiveness of other languages - see Meta-PL/SQL. Well, if there is a will, there is a way, so I built me some closures. Compared to Lisp, the concept does not map naturally to PL/SQL and the implementation is only a toy. Anyway, here are a few examples:

declare

    l lambda := lambda('begin :r := :a + :b; end;',
                       'b',1);
begin
    dbms_output.put_line(l.exec('a',3));                                                                                                                                                                            
    dbms_output.put_line(l.exec('a',9));
    l.close;
end;
/

4
10


PL/SQL procedure successfully completed.

Map and reduce are popular patterns. Here's reduce:

declare
  type v2_tbl is table of varchar2(32767);
  function reduce (
    l in lambda,
    t in v2_tbl,
    v in varchar2)
    return varchar2
  is
    l_result varchar2(32767) := v;
  begin
    if t is not null then
        for i in 1 .. t.count loop
            l_result := l.exec(
                          'a',t(i),
                          'b',l_result);
        end loop;
    end if;
    l.close;
    return l_result;
  end;
begin
  dbms_output.put_line (
    reduce (
      lambda(q'{begin
                  dbms_output.put_line('adding '||:a||' to '||:b);
                  :r := :a+:b;
                end;}'),
      v2_tbl(1,2,3,4,5),
      0));
end;
/

adding 1 to 0

adding 2 to 1

adding 3 to 3

adding 4 to 6

adding 5 to 10

15

It is also possible to write state-saving code, for example a number generator:


declare
  l lambda := lambda(q'{
                declare
                  v_result number := :a+1;
                begin
                  :r := v_result;
                  dbms_sql.bind_variable(:c,'a',v_result,32767);
                end;}',
                'a',0);
begin
  l.bind('c',l.c);
  for i in 1 .. 10 loop
    dbms_output.put_line(l.exec);
  end loop;
end;
/
1
2
3
4
5
6
7
8
9
10                                                                                 


The code for lambda:

create or replace type lambda as object (                                                                                                                                                                           

    c number,

    constructor function lambda (

        p_sql in varchar2,

        p_n1  in varchar2 default null,

        p_v1  in varchar2 default null,

        p_n2  in varchar2 default null,

        p_v2  in varchar2 default null,

        p_n3  in varchar2 default null,

        p_v3  in varchar2 default null )

        return self as result,

    member procedure bind (

        self in lambda,

        p_n in varchar2,

        p_v in varchar2 ),

    member procedure exec (

        self in lambda ),

    member function exec (

        self     in lambda,

        p_n1     in varchar2 default null,

        p_v1     in varchar2 default null,

        p_n2     in varchar2 default null,

        p_v2     in varchar2 default null,

        p_n3     in varchar2 default null,

        p_v3     in varchar2 default null,

        p_result in varchar2 default 'r')

        return varchar2,

    member function value (

        self in lambda,

        p_n in varchar2 )

        return varchar2,

    member procedure close (

        self in lambda )

)

/

show err

create or replace type body lambda as

    constructor function lambda (

        p_sql in varchar2,

        p_n1  in varchar2 default null,

        p_v1  in varchar2 default null,

        p_n2  in varchar2 default null,

        p_v2  in varchar2 default null,

        p_n3  in varchar2 default null,

        p_v3  in varchar2 default null )

        return self as result

    is

    begin

        c := dbms_sql.open_cursor;

        dbms_sql.parse(c,p_sql,dbms_sql.native);

        if p_n1 is not null then

            self.bind(p_n1,p_v1);

        end if;

        if p_n2 is not null then

            self.bind(p_n2,p_v2);

        end if;

        if p_n3 is not null then

            self.bind(p_n3,p_v3);

        end if;

        return;

    end;

    member procedure bind (

        self in lambda,

        p_n in varchar2,

        p_v in varchar2 )

    is

    begin

        dbms_sql.bind_variable(self.c,p_n,p_v,32767);

    end;

    member procedure exec (

        self in lambda )

    is

        l_num_processed number;

    begin

        l_num_processed := dbms_sql.execute(c);

    end;

    member function exec (

        self     in lambda,

        p_n1     in varchar2 default null,

        p_v1     in varchar2 default null,

        p_n2     in varchar2 default null,

        p_v2     in varchar2 default null,

        p_n3     in varchar2 default null,

        p_v3     in varchar2 default null,

        p_result in varchar2 default 'r')

        return varchar2

    is

    begin

        self.bind(p_result,null);

        if p_n1 is not null then

            self.bind(p_n1,p_v1);

        end if;

        if p_n2 is not null then

            self.bind(p_n2,p_v2);

        end if;

        if p_n3 is not null then

            self.bind(p_n3,p_v3);

        end if;

        self.exec;

        return self.value(p_result);

    end;

    member function value (

        self in lambda,

        p_n in varchar2 )

        return varchar2

    is

        l_val varchar2(32767);

    begin

        dbms_sql.variable_value(self.c,p_n,l_val);

        return l_val;

    end;

    member procedure close (

        self in lambda )

    is

        l_c number := self.c;

    begin

        dbms_sql.close_cursor(l_c);

    end;

end;

/

show err

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.