unclechrisblog

Closures in PL/SQL

2011-07-24T10:19:00.000-07:00

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

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

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 )

begin

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

end;

member procedure exec (

self in lambda )

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

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

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 )

l_c number := self.c;

begin

dbms_sql.close_cursor(l_c);

end;

show err

change user in pl/sql

2010-08-19T12:36:00.000-07:00

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

2010-07-16T06:18:00.000-07:00

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

2010-07-07T10:25:00.000-07:00

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

2010-05-01T11:43:00.000-07:00

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

2010-01-08T06:23:00.000-08:00

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.

Hacking Oracle APEX IR reloads

2009-11-05T10:38:00.000-08:00

Writing is hard and I am lazy. Here's another try, however.

Over time, our internal APEX framework acquired a few features that are quite nice. For some of them, we had to find workarounds for stuff that isn't directly supported. Thankfully, on the server side the APEX engine can be influenced (e.g. variables in package wwv_flow) and JavaScript/CSS can manipulate the display and behaviour on the client side. The former is totally unsupported/undocumented and the latter to a certain part, however. As long as it works we can live with that, because our applications will run on intranets where we can control the environment.

The latest hack had to do with interactive reports. To implement a feature (right-mouse popup over some columns, metadata-driven content), we had to run JavaScript code on the client that modified the IR table's HTML. It worked pretty well except after AJAX refreshes, which of course are integral to IRs. The APEX AJAX code replaced the HTML table including my code's changes with new one from the server. The older partial page refreshable reports called init_htmlPPRReport which could be overwritten to put the changes back in again (see here) but no such luck with IRs. After wading through the code of apex_ns_3_1.js I found out that the refresh code called $x_Show('apexir_REPORT') at the end. I overwrote that and now everything works.

So, what's the bottom line? I think Oracle would do developers a great favour if they included standardized and supported hooks for extension writers to plug their code in. For my example above, say, there might be a JavaScript function

apex.hooks.after_region_reload = function(i_region_id) {}

that gets called after partial page refreshes and could be overwritten. Better, make that an Array of functions, initially empty, so we can

apex.hooks.after_region_reload.push(
function(i_region_id) {
alert("do something extension specific");
});

Patrick, can you hear me? *g*

APEX: Checkboxes in tabular reports

2009-02-06T06:49:00.000-08:00

This is a response to Patrick Wolf's posting Checkboxes in Tabular Forms - The easy way!. I think I found a solution that's even easier than that of one of the APEX gurus ;-). Because Patrick's blog software ate my html comments, I'll write it here again.

First, the generic CSS and JavaScript part. In my test case I put the following code in the page header. If something like this goes production, it should be in separate files, of course:

<style type="text/css">
.js_checkbox { display:none; }
</style>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.3/jquery.min.js"></script>

<script language="JavaScript" type="text/javascript">
function set_yn(i_checkbox,i_apex_field_id) {
document.getElementById(i_apex_field_id).value =
(i_checkbox.checked? "Y" : "N");
}

$().ready(function() {
$(".js_checkbox").each(function() {
$(this).after("<input id='"+this.id+"_js' type='checkbox' "+
(this.value==="Y" ? "checked='checked' " : "") +
"onchange='javascript:set_yn(this,"+'"'+this.id+'"'+")'/>");
});
});

</script>

Second, the column definition:

* Display As: Text Field (the default)
* Element Attributes: class="js_checkbox"

Please note that this code is just a quick hack in response to Patrick's topic, not thoroughly tested etc.

An aggregate function for collection types

2008-06-11T23:43:00.000-07:00

Today I stumbled upon a blog entry where the poster needs something like Tom Kyte's stragg function, only with dynamic delimiters. Here's my solution, using a variation of stragg that accumulates data in a collection instead of appending it to a string.

First of all, this is how you use it:

SQL> r
1  with data as (
2  select lpad('x',level,'x') data
3  from dual
4  connect by level <= 5)
5  select v2_pkg.join(v2_agg(data),'-')
6* from data

 V2_PKG.JOIN(V2_AGG(DATA),'-')
 --------------------------------------------------------------------------------
 x-xx-xxx-xxxx-xxxxx

 1 Zeile wurde ausgewählt.

The v2_agg function (and it's underlying object type) append strings to a collection. The v2_pkg.join concatenates the elements of it's first argument (the collection) with the second argument in between. See below for the source code. It's part of my everyday toolkit, v2_pkg contains lots more, for example.

The collection type

create type v2_tbl as table of varchar2(4000);

The aggregate object type spec

create or replace type v2_agg_type as object (                                  
  elements v2_tbl,
  static function
    ODCIAggregateInitialize(sctx IN OUT v2_agg_type )
    return number,
  member function
    ODCIAggregateIterate(self IN OUT v2_agg_type ,
                         value IN varchar2 )
    return number,
  member function
    ODCIAggregateTerminate(self IN v2_agg_type,
                           returnValue OUT  v2_tbl,
                           flags IN number)
    return number,
  member function
    ODCIAggregateMerge(self IN OUT v2_agg_type,
                       ctx2 IN v2_agg_type)
    return number
);                                       
/

The aggregate function

create or replace function v2_agg(input varchar2)
return v2_tbl                                                                 
parallel_enable aggregate using v2_agg_type;
/

The utility package spec

create or replace package v2_pkg as
  function join(i_tbl in v2_tbl,
                i_glue in varchar2 := ',')
  return varchar2;
end;
/

The object type body

create or replace type body v2_agg_type  is
  static function ODCIAggregateInitialize(sctx IN OUT v2_agg_type)
  return number
  is
  begin
    sctx := v2_agg_type( null );
    return ODCIConst.Success;
  end;
  member function ODCIAggregateIterate(self IN OUT v2_agg_type,
                                       value IN varchar2 )
  return number
  is
  begin
    if self.elements is null then
      self.elements := v2_tbl();
    end if;
    self.elements.extend;
    self.elements(self.elements.count) := value;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateTerminate(self IN v2_agg_type,
                                         returnValue OUT v2_tbl,
                                         flags IN number)
  return number
  is
  begin
    returnValue := self.elements;
    return ODCIConst.Success;
  end;
  member function ODCIAggregateMerge(self IN OUT v2_agg_type,
                                     ctx2 IN v2_agg_type)
  return number
  is
  begin
    if ctx2.elements is not null then
      if self.elements is null then
        self.elements := v2_tbl();
      end if;
      for i in 1 .. ctx2.elements.count loop
        self.elements.extend;
        self.elements(self.elements.count) := ctx2.elements(i);
      end loop;
    end if;
    return ODCIConst.Success;
  end;
end;
/

The utility package body

create or replace package body v2_pkg as
   function join(i_tbl  in v2_tbl,
                 i_glue in varchar2 := ',')
  return varchar2
  is
    v_str varchar2(32767);
  begin
    IF i_tbl is not null THEN
      FOR i in 1 .. i_tbl.count LOOP
        v_str := v_str || i_glue || i_tbl(i);
      END LOOP;
    END IF;
    return substr(v_str,length(i_glue)+1);
  end;
end;
/

By the way, Oracle's COLLECT function might be more appropriate than v2_agg for large datasets. This post explains why.

Forms 6i certified with Oracle 11g?

2008-03-28T04:32:00.000-07:00

Today I found this entry on Steven Chan's Oracle E-Business Suite Technology blog. It's titled "Oracle Database 11g Release 1 (11.1.0.6.0) Certified With E-Business Suite Release 11i".

EBS 11i uses the Developer 6i techstack, for which extended support ended last january. I think it's great (and highly amusing) that Oracle now blessed this configuration. There was probably a lot of customer pressure, so Oracle realized that reality does not conform with their product support plans ;-).

I also wonder if this certification only applies to EBS 11i or Dev6i in general (i.e. also good old client/server deployment). Probably not, but one can hope.

Comfortable javascript editing in APEX

2008-02-22T05:07:00.000-08:00

This week, I built a small extension to Patrick Wolf's APEX Builder Plugin. It generates separate text fields for javascript events. Makes writing event code quite a bit easier.

Maybe it's time to re-think what web development should be about

2008-01-29T03:13:00.000-08:00

I just came about this link and am currently half-way through Dan Ingall's Google TechTalk. Wow!

Btw, there is also Smalltalk in Flash.

Object oriented database management systems

2008-01-19T10:56:00.000-08:00

Recently, there were several blog posts about ODBMSs. It all started with this interview of Michael Stonebraker.
Dan Weinreb (ex ObjectStore) responded and today I read a blog post of Oracle's John Russell.

In the early/mid 90ies, when I was still on university and hooked on OO, ODBMSs had a big fascination for me. I read every book and paper about them I could get and had uni accounts on machines to play with Gemstone and ObjectStore. Hell, I even wrote one myself, in c++ and tcl (chaos - chris' alternative object store;-)).

Accidently, my first job was with Oracle 7 and Forms/Reports, "boring" technology or so I thought. This changed quickly, when I realized how efficient development in this environment was. It turned out that I didn't need all that fancy OO stuff I thought indispensable to get things done and have fun working. In some way, programming pl/sql even reminds me of the Gemstone/Smalltalk days. My interface seems minimalistic in comparison (sql*plus and vi), but it's only the interface to that powerful, persistent multiuser sql and pl/sql machine.

Javascript

2007-12-31T10:58:00.000-08:00

This is a fun language. It contains pretty good stuff and annoying incompatibilities. However, it's one of the most important languages for software developers and just now, I'm very lately trying to get a better grip, because of this current project. I've hacked an odd XMLHttpRequest before it was called AJAX, but now we've got additional problems. Our SW has to run on hand-helds, so we've got special keyboard and performance requirements, e.g. we can't use the framework of my former colleague Patrick Wolf. According to Doug Crockford, there are no acceptable books. The available Javascript source code for Oracle APEX, the platform we'll of course use as die-hard Oracleheads, is ugly, especially compared to what Crockford and others preach.

Rob Pike on video, Plan 9 flashback

2007-06-09T11:16:00.000-07:00

Today I watched a very recent video of Rob Pike talking about his 20 year old language Newsqueak.

I used his text editor "sam" on linux in the mid-90ies for a while, when Plan 9 first came on my radar. What first impressed me was the paper accompanying the source code. The code itself is very elegant and minimalistic. Though it didn't take off (probably just because of those attributes, baroque featuritis wins in the end), there are some great ideas in Plan 9 that can provide inspiration. The plumbing paper for example gave me the idea for a generic navigation concept in an Oracle Forms application, in 2000. We called it "hyperlinks", though...

After thinking about all that again, this post wouldn't be complete without mentioning Boyd Roberts. The link's dead, of course.

2007-03-19T12:00:00.000-07:00

Well the first post is the hardest post. My stupid blog title was selected while listening to this song by the Grateful Dead. Fnord.