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.

Hacking Oracle APEX IR reloads

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

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

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.