HTML5: Useful posts on responsive design viewport meta tag particularry

Here will be the bunch of useful posts and tutorials on developing responsive web pages.
Is the viewport meta tag really necessary?
HTML5 Boilerplate Doc

UX idea: Advanced highlighting and formatting for code editors and IDEs

It's 2014 but we still lack intellectual tools that could help us to produce high quality code.

As Steve McConnell mentions in his book 'Code Complete' naming standards are vital for any software project.

It's good for you if you have established one and if you adhere to a good naming scheme, even better if whole of your company does, but everyday developer's tasks include an amount of drilling down through code written by others. In the best case that includes looking through code libraries and legacy systems' code, in the worst case it's looking at the code written by your team member (and swearing through teeth cause he refuses to adhere to naming and formatting standards).

When you touch other's code you are on a foe's territory. Foreign naming standards rarely resemble yours so you first have to understand it and second you have to psychologically admit it (that's not always as easy as it seems).

That's where our overpowered CPUs can become handy. IDEs and code editors could know what semantic element is represented with each construct and so they could highlight it with typeface, font weight and color. An extreme example: ordinal type variables could be highlighted differently from class instances and so on. Such advanced highlighting could greatly help in understanding others' code.

There's some advance in the desired direction (see some themes for Sublime, that make use of advanced on the fly parsing), but there's still much to implement!

Another idea is to use CPU power to implement online formatting of code constructs according to your defined rules. This could be done for properly structured and compiled (or checked if we deal with interpreted language) pieces of code, so every team could look at the library code through their own "formatting lens". It seems not so convenient from original authors' point of view, as they will get bug reports with unfamiliar formatting, but they could use their own lens to reformat code back to their likes.

That's not new ideas, they are old ones (see Jeff Raskin's 'Interface', for example). Still such intellectual features are implemented at such a slow pace, that it's worth repeating ideas just not to forget them.

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text). Take two

Addressing large dataset issue that was mentioned for previously developed cursor to clob conversion functions (see previous post) a new version of a function that converts any query into delimited text was born.
Not so elegant nevertheless it handles nulls (thanks to this StackOverflow topic) and large datasets (that still needs approval).
If you fill able, you can try to convert this procedure into highly optimized pipelined function making use of bulk operations, though I failed due to some bug producing ORA-24374 while working with xml and multisets (maybe I'll cover this bug in the following posts).
So by now the following procedure just inserts clob values into given table

Here are prerequisites:

/* ty_int_to_VCHAR2_row ty_int_to_VCHAR2_tbl type declarations ...*/
create or replace
type TY_INT_TO_VCHAR2_ROW force as object (
 int ingeger
 , vchar varchar2( 4000 char )
 , CONSTRUCTOR FUNCTION TY_INT_TO_VCHAR2_ROW( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW, a_int in pls_integer, a_vchar in varchar2 ) 
  RETURN SELF AS RESULT
);
/

create or replace
type body TY_INT_TO_VCHAR2_ROW as

constructor function TY_INT_TO_VCHAR2_ROW ( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW , a_int in pls_integer, a_vchar in varchar2 ) 
 return self as result
as
begin
 int := a_int;
 vchar := a_vchar;
   return;
end TY_INT_TO_VCHAR2_ROW ;

end;
/

create or replace type TY_INT_TO_VCHAR2_TBL force as table of TY_INT_TO_VCHAR2_ROW;
/

create global temporary table tmp_pk_utils_xml_result_set( 
  row_num integer, name_ varchar2( 30 char ), value_ varchar2( 4000 char )
) on commit preserve rows;
/

And here's the code:

procedure query_to_flat( 
  a_query in clob, a_delimiter in varchar2, a_line_break in varchar2, a_target_table in varchar2 
)
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
  v_cur_desc sys_refcursor;
  v_desc_tab dbms_sql.desc_tab;
  v_count number;
  v_cur_handle NUMBER;
  
  v_cur sys_refcursor;
  
  v_column_names_tbl TY_INT_TO_VCHAR2_TBL := TY_INT_TO_VCHAR2_TBL();
  v_truncate_result pls_integer;
  v_truncate_message varchar2( 32767 char );
begin
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.enable( null );
$end  
  -- read column description into v_desc_tab
  open v_cur_desc for 'select * from ( ' || a_query || ' ) where 1=0';
  
  v_cur_handle := dbms_sql.to_cursor_number( v_cur_desc );
  -- we'll get all columns and their names
  dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
  
  if v_cur_desc%ISOPEN then
    close v_cur_desc;
  end if;
-- move column descripition data into sql-level table type, so we can use it in queries
  v_column_names_tbl.extend( v_desc_tab.count );
  for v_column_idx in 1 .. v_desc_tab.count loop
    v_column_names_tbl( v_column_idx ) := 
      ty_int_to_vchar2_row( v_column_idx, v_desc_tab( v_column_idx ).col_name )
    ;
  end loop;
  
  open v_cur for a_query;
  
  -- just a truncate that retries if resource is busy, not necessary here, but it's an attitude
  pk_safe_ddl.safe_truncate( 'MY_SCHEMA', 'TMP_PK_UTILS_XML_RESULT_SET' );
  
  -- insert resultset into temporary table, here we'll got only not null columns
  insert into tmp_pk_utils_xml_result_set( row_num, name_, value_ )
  SELECT t.rn /*+ NO_XML_QUERY_REWRITE */
    , t2.COLUMN_VALUE.getrootelement() NAME
    , EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
  FROM ( select /*+ NO_XML_QUERY_REWRITE */ rownum rn, column_value from TABLE ( XMLSEQUENCE ( v_cur ) ) ) t
    , TABLE ( XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
  ;
  -- first cross join to get all column names for every row in resulting dataset
  -- then join with temporary table that stores actual values
  for cur_val in (
    select a.row_num
      , a.name_
      , q.value_
    from ( 
      select r.row_num
        , c.int as col_idx
        , c.vchar as name_
      from ( 
        select row_num
        from tmp_pk_utils_xml_result_set
        group by row_num
      ) r
      cross join table( cast( v_column_names_tbl as TY_INT_TO_VCHAR2_TBL ) ) c
    ) a
    left outer join ( 
      select row_num
        , name_
        , value_
      from tmp_pk_utils_xml_result_set
      where name_ is not null
    ) q
      on a.row_num = q.row_num
      and a.name_ = q.name_
    order by a.row_num
      , a.col_idx
  ) loop
    -- some kind of listagg functionality but for potentially long lines (clobs)
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.value_; 
    else
      if ( v_prev_rn != cur_val.row_num ) then
        v_clob := v_clob || a_line_break;
        execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
        using v_clob
        ;
$if ( pk_utils_cc.dbg = 1 ) $then
        dbms_output.put_line( v_clob );
$end
        v_clob := cur_val.value_;
      else
        v_clob := v_clob || a_delimiter || cur_val.value_;
      end if;
    end if;
    v_prev_rn := cur_val.row_num;
  END LOOP;
  
  execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
  using v_clob
  ;
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.put_line( v_clob );
$end
  if v_cur%ISOPEN then
    close v_cur;
  end if;
  commit;
end;

Perl: perserve case while replacing

According to this stackoverflow topic it's not quite easy to make a replacement preserving case with simple pattern unless we have perl6's 'ii' modifier, but for practical purposes it mentions great solution like the following one:

$ perl -pi -e 's/(S|s)licing/{$1 eq 'S' ? 'Excel' : 'excel'}/ge' *.pas

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text)

Thanks to a couple of posts (namely TO_CHAR of an Oracle PL/SQL TABLE type and SQL*Plus tips. #2) I was able to develop very convenient and what's more important generic function for converting arbitrary cursor to clob lines or say any result set to clob lines.
Such function will be very useful for unloading results of an arbitrary query to delimited flat files.

function cursor_to_flat( a_cur in sys_refcursor, a_delimiter in varchar2, a_line_break in varchar2 ) 
return ty_clob_tbl pipelined 
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
begin
  FOR cur_val IN (
    SELECT t.rn
      , EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
    FROM ( select rownum rn, column_value from TABLE ( XMLSEQUENCE ( a_cur ) ) ) t
      , TABLE (XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
    order by 1
  ) LOOP
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.value;
    else
      if ( v_prev_rn != cur_val.rn ) then
        v_clob := v_clob || a_line_break;
        pipe row ( v_clob );
        v_clob := cur_val.value;
      else
        v_clob := v_clob || a_delimiter || cur_val.value;
      end if;
    end if;
    v_prev_rn := cur_val.rn;
  END LOOP;
  pipe row ( v_clob );
end;

Unit test code:

select *
from table( 
  pk_utils.cursor_to_flat( 
    cursor( 
      select 1, 2, 3 from dual 
      union all 
      select 4, 5, 6 from dual 
    ) 
  ) 
);

NB! One problem with this approach is that if you have NULLs in your result set, then they will be completely skipped (not wrapped with delimiters). Now I'm trying to solve it.

Edit
The author of SQL*Plus tips. #2 was totally awesome pointing out how to handle NULLs. Resulting function takes query as clob, but one can easily rollout version with sys_refcursor, as dbms_xmlgen.newcontext will accept it.

function query_to_flat( a_query in clob, a_delimiter in varchar2, a_line_break in varchar2 ) 
return ty_clob_tbl pipelined 
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
  v_cur sys_refcursor;
  v_xml clob;
  v_context dbms_xmlgen.ctxtype;
begin
  v_context := dbms_xmlgen.newcontext( a_query );
  dbms_xmlgen.setnullhandling( v_context, dbms_xmlgen.empty_tag );
  v_xml := dbms_xmlgen.getxml( v_context );
  dbms_xmlgen.closecontext( v_context );
  
  for cur_val in ( 
    select row_num
      , col_value
    from xmltable( --<<<ML
'(#ora:view_on_null empty #){
for $a at $i in /ROWSET/ROW 
  , $r in $a/*
    return element ROW{
      element ROW_NUM{$i}
      , element COL_VALUE{$r/text()}
    }
}'
--ML;
      passing xmltype(v_xml)
      columns
        row_num   int
        , col_value varchar2(100)
    )
  ) loop
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.col_value; 
    else
      if ( v_prev_rn != cur_val.row_num ) then
        v_clob := v_clob || a_line_break;
        pipe row ( v_clob );
        v_clob := cur_val.col_value;
      else
        v_clob := v_clob || a_delimiter || cur_val.col_value;
      end if;
    end if;
    v_prev_rn := cur_val.row_num;
  END LOOP;
  pipe row ( v_clob );
end;

NB!This solution is still has some drawbacks for large dataset as all data is placed into variable, consuming PGA.
I'll try to address this issue in the following post.
Edit
And here's the followup post!