Tag Archives: ora_te

Uniqualize rows of any table using ora_te

One of the cool ways to use my small template engine for Oracle (ora_te).

procedure uniqualize_rows_no_commit( a_table_name in varchar2 ) as 
  v_clob clob;
  v_te_uniq ty_te;
begin
  v_te_uniq := ty_te.compile_named( q'#
  delete from {$table_name} 
  where rowid in ( 
    select rowid 
    from (
      select rowid
        , row_number() over ( partition by {%for cur:1|join(',')%}{$column_name}{%endfor%} order by null ) rn
      from {$table_name}
    ) 
    where 1 < rn
  )
#'
  );  
  
  select pk_te.substitute( 
    v_te_uniq
    , ty_m( ty_p('table_name', a_table_name ) )
    , cursor( select ty_m( ty_p( 'column_name', column_name ) ) from user_tab_columns where table_name like upper( a_table_name ) ) 
  )
  into v_clob
  from dual;
--  pk_utils.eat( pk_utils.dbms_output_clob( v_clob ) );

  execute immediate v_clob;
end;

ora_te: v1.0.3 is here

A simple template engine for Oracle ora_te has a new release v1.0.3.
IF and FOR constructs are implemented (the Twig way), while old syntax for loops is moved behind syntax proxy.
Road map for further development:

  • Filters for uppercase, lowercase and capital case (like in Twig).
  • Partial substitution

ora_te: On further development

Hi, we are talking about ora_te again.
I’ve finally decided to implement IF construct, but I want to do it in a slightly different way I wanted earlier. Next release of ora_te will feature new syntax for LOOP construct and a an implementation of IF construct in a form very similar to twig template engine.
It will greatly improve readability of templates.
Plus such things as concatenation by new line and carriage return could be directly archived by writing actual new line in the template.
Compare

{% for cur:1 |  , %}t1.{{column_name}} = t2.{{column_name}}
{% endfor %}

to

{% for cur:1 |, %}t1.{{column_name}} = t2.{{column_name}}{% endfor %}

First of given templates will naturally insert new line after each loop substitution, while second one will produce one line.
If construct will also be implemented the twig way.

{% if ('{{column_name}}' = 'dummy' ) %}
  gotcha
{% else %}
  nope
{% endif %}

Sure old syntax will not be deprecated.
Rewriting a merge example with a new syntax will result in something like

'merge into {{dest_table}} t1
using {{tmp_table}} t2
on (t1.{{join_by}} = t2.{{join_by}} ) 
when matched then 
  update set {% for cur:1 |  , |n|?|%}t1.?1 = t2.?1 
{% endfor %}
delete where t2.status_code = ''D''
when not matched then insert( 
  {{dwh_pk}}
  , {% for cur:2 |,%} t1.{{column_name}} = t2.{{column_name}} 
{% endfor %} 
) values ( 
  {{sequence_name}}.nextval 
  , {% for cur:2 |,%}t2.{{column_name}}
{% endfor %} 
) 
where t2.status_code <> ''D''';

ora_te: Road map

Here’s the target for next release. Handle loops and substitute values from cursors. So something like this can be handled.

'merge into {$dest_table} t1
using {$tmp_table} t2
on (t1.{$join_by} = t2.{$join_by} ) 
when matched then 
  update set {%1%t1.?1 = t2.?1%\r\n ,|n|?%}
delete where t2.status_code = ''D''
when not matched then insert( 
  {$dwh_pk}
  , {%2%t1.{{column_name}} = t2.{{column_name}} %,\r\n|w|{{|}}%} 
) values ( 
  {$sequence_name}.nextval 
  , {%2%t2.{$column_name}%,\r\n|w%} 
) 
where t2.status_code <> ''D''';

ora_te: A simple template engine for Oracle

Just started a new project ora_te at GitHub.
For now it just implements substitution of placeholders so one could write something like

select pk_te.substitute( '$1-$2-$3 $1-$2-$4', pk_te.p( 'Ob', 'la', 'di', 'dah' ) ) p
from dual;
select pk_te.substitute( 
  '{$verb} a {$subject}'
  , pk_te.m( 
    pk_te.p( 'verb', 'Take')
    , pk_te.p( 'subject', 'book' )
  )
) m
from dual;

and get

P
--------------------------------
Ob-la-di Ob-la-dah

1 row selected

M
--------------------------------
Take a book

1 row selected

But more to come. Stay in touch!