venerdì 28 febbraio 2014

Compile Java in Oracle




Have you ever tried to compile a java class in Oracle? Have you understood why it was not compiling?
In order to properly compile java, you can use the "show errors option":

 ALTER JAVA CLASS "com/organization/project/MyClass" COMPILE;  
 show errors java class "com/organization/project/MyClass";  


Especially the last line - I hate how Oracle handles Java compilation, with the last line it will output the error. Having such option permits to have some more verbose output message. Like:

 0/0      com/organization/project/MyClass:19: cannot find symbol  
 0/0      symbol : class Logger  
 0/0      location: package org.apache.log4j  
 0/0      import org.apache.log4j.Logger;  
 0/0                  ^  
 0/0      com/organization/project/MyClass:29: cannot find symbol  
 0/0      symbol : class Logger  
 0/0      location: class com.organization.project.MyClass  
 0/0       private static Logger log = Logger.getLogger(MyClass.class);  
 0/0                  ^  
 0/0      com/organization/project/MyClass:29: cannot find symbol  
 0/0      symbol : variable Logger  
 0/0      location: class com.organization.project.MyClass  
 0/0       private static Logger log = Logger.getLogger(MyClass.class);  
 0/0                        ^  
 0/0      com/organization/project/MyClass:253: cannot find symbol  
 0/0      symbol : method getXmlFilePath()  
 0/0      location: class com.organization.project.MyClass  
 0/0           File xmlfile = formXmlFile(getXmlFilePath());  
 0/0                            ^  
 0/0      com/organization/project/MyClass:259: cannot find symbol  
 0/0      symbol : variable trFileobj  
 0/0      location: class com.organization.project.MyClass  
 0/0        insertXrunId(con,trFileobj);  
 0/0                 ^  
 0/0      5 errors  

Class Logger is missing, also method getXmlFilePath(), I would also say class MyClass.




CSS Checklist effect

You want to achieve in HTML such effect using only CSS. 




Thanks to www.cssflow.com ! They made a great tutorial. 
Basically you can find everything needed here: http://www.cssflow.com/snippets/simple-to-do-list



mercoledì 26 febbraio 2014

Responsive picture resizer

You are building your website and you have a pretty heavy picture. You would like to have a picture which is small for small device and large for normal laptop.
I just did on my website, for my main picture:

(1014 x 490) to be used on laptop


(792 x 400) to be used on tablets


(300 x 253) to be used on mobile devices



How do you do that?
There is a great a very lightweight script fully supporting you (thanks to Koen Vendrik, please visit his website: http://kvendrik.com)


 /*  
 // @name: Responsive-img.js  
 // @version: 1.1  
 //   
 // Copyright 2013-2014 Koen Vendrik, http://kvendrik.com  
 // Licensed under the MIT license  
 */  
 function makeImagesResponsive() {  
   var e = window.innerWidth || document.documentElement.clientWidth || document.body.clientWidth,  
     t = document.getElementsByTagName("body")[0].getElementsByTagName("img");  
   if (t.length === 0) return;  
   var n;  
   t[0].hasAttribute ? n = function (e, t) {  
     return e.hasAttribute(t)  
   } : n = function (e, t) {  
     return e.getAttribute(t) !== null  
   };  
   var r = window.devicePixelRatio ? window.devicePixelRatio >= 1.2 ? 1 : 0 : 0;  
   for (var i = 0; i < t.length; i++) {  
     var s = t[i],  
       o = r && n(s, "data-src2x") ? "data-src2x" : "data-src",  
       u = r && n(s, "data-src-base2x") ? "data-src-base2x" : "data-src-base";  
     if (!n(s, o)) continue;  
     var a = n(s, u) ? s.getAttribute(u) : "",  
       f = s.getAttribute(o),  
       l = f.split(",");  
     for (var c = 0; c < l.length; c++) {  
       var h = l[c].replace(":", "||").split("||"),  
         p = h[0],  
         d = h[1],  
         v, m;  
       if (p.indexOf("<") !== -1) {  
         v = p.split("<");  
         if (l[c - 1]) {  
           var g = l[c - 1].split(/:(.+)/),  
             y = g[0].split("<");  
           m = e <= v[1] && e > y[1]  
         } else m = e <= v[1]  
       } else {  
         v = p.split(">");  
         if (l[c + 1]) {  
           var b = l[c + 1].split(/:(.+)/),  
             w = b[0].split(">");  
           m = e >= v[1] && e < w[1]  
         } else m = e >= v[1]  
       } if (m) {  
         var E = d.indexOf("//") !== -1 ? 1 : 0,  
           S;  
         E === 1 ? S = d : S = a + d;  
         s.src !== S && s.setAttribute("src", S);  
         break  
       }  
     }  
   }  
 }  
 if (window.addEventListener) {  
   window.addEventListener("load", makeImagesResponsive, !1);  
   window.addEventListener("resize", makeImagesResponsive, !1)  
 } else {  
   window.attachEvent("onload", makeImagesResponsive);  
   window.attachEvent("onresize", makeImagesResponsive)  
 };  


Here comes the HTML code:


 <!DOCTYPE html>  
 <html lang="en">  
  <head>  
   [...]   
  </head>  
  <body>  
   [...]  
     <figure>  
      <img width="100%"  
       src="img/Marco_andolfi_1024.png"   
       alt="Marco Andolfi"   
       data-src-base='img/'   
       data-src='<600:Marco_Andolfi_600.png,   
            <800:Marco_Andolfi_800.png,   
            >800:Marco_Andolfi_1024.png'>  
     </figure>   
   [...]   
   <script src="js/responsive-img.js"></script>  
  </body>  
 </html>  





venerdì 21 febbraio 2014

Motion scroll effect




Ok, it is one line of code, but I continuously forget it.
You place an anchor on your web site, but you do not like the immediate jump. You would prefer a motion that bring you to the anchor and let you understand that you are moving.

Well the code is extremely easy and you need just to import a jQuery library.
 
/*******  
      ***     Anchor Slider by Cedric Dugas  ***  
      *** Http://www.position-absolute.com ***  
      Never have an anchor jumping your content, slide it.  
      Don't forget to put an id to your anchor !  
      You can use and modify this script for any project you want, but please leave this comment as credit.  
      File: jquery.anchor.js
*****/  
 $(document).ready(function() {  
      $("a.anchorLink").anchorAnimate()  
 });  
 jQuery.fn.anchorAnimate = function(settings) {  
       settings = jQuery.extend({  
           speed : 1100  
      }, settings);       
      return this.each(function(){  
           var caller = this  
           $(caller).click(function (event) {       
                event.preventDefault()  
                var locationHref = window.location.href  
                var elementClick = $(caller).attr("href")  
                var destination = $(elementClick).offset().top;  
                $("html:not(:animated),body:not(:animated)").animate({ scrollTop: destination}, settings.speed, function() {  
                     window.location.hash = elementClick  
                });  
                 return false;  
           })  
      })  
 }  


And here is the HTML:


 <!doctype html>  
 <head>  
   [...]  
 </head>  
 <body>  
   [...]  
   </header>  
   [...]  
    <div>  
      <div>  
       <h2>Menu</h2>  
       <ul>  
        <li><a href="#anchor_master" class="anchorLink">Master degree</a></li>  
        <li><a href="#anchor_bachelor" class="anchorLink">Bachelor degree</a></li>  
        <li><a href="#anchor_projects" class="anchorLink">University projects</a></li>  
        <li><a href="#anchor_highschool" class="anchorLink">High School</a></li>  
       </ul>  
      </div>  
        [...]  
       <div  id="master_degree">  
       <span id="anchor_master"></span>  
        [...]  
       </div>  
       <div id="bachelor_degree">  
        <span id="anchor_bachelor"></span>  
        [...]  
       </div>  
       <div id="projects">  
        <span id="anchor_projects"></span>  
        [...]  
       </div>  
       <div id="highschool">  
        <span id="anchor_highschool"></span>  
        [...]  
       </div>  
      </div>  
  </div>  
   [...]  
   <script>window.jQuery || document.write("<script src='scripts/jquery-1.8.2.min.js'>\x3C/script>")</script>  
   <script type="text/javascript" src="scripts/jquery.anchor.js"></script>  
 </body>  
 </html>  

Notice: i
It is needed only the import of the JS file and that the link has the class="anchorLink"
For the rest is a normal HTML, you do not need any special id, class, etc...

mercoledì 19 febbraio 2014

Connect By in SAP HANA



As you know SAP HANA does not offer the feature: CONNECT BY.
"Connect by" is a feature which can be used in order to get full path over tables. 
Ex: Suppose you have a table made of arcs, you want to have all the paths inside the table. 

In Oracle exists a feature called "connect by" which makes all the computation for you. 
Probably I will not be able to make a generic code like in Oracle (like simply call a function). 
Anyway, I have got some good result on a simple test case. 


Example and Theory documentation
 create table arc (  
    node_father int,   
    node_child int  
 );   
   
 insert into arc values(1, 2);  
 insert into arc values(1, 3);  
 insert into arc values(1, 4);  
 insert into arc values(2, 5);  
 insert into arc values(2, 6);  
 insert into arc values(6, 8);  
 insert into arc values(6, 9);  
 insert into arc values(4, 7);  


The graph looks like this: 
                
              1
             /|\
           /  |  \
         2   3    4
        /\            \
      /    \            \
     5      6          7
             /\
           /    \
         8      9     


Set of Path starting from the root: 
1-3
1-4-7
1-2-5
1-2-6-8
1-2-6-9

Set of Sub path
2-5
6-8
6-9
4-7
2-6-8
2-6-9


 create table paths(  
    node_father int,   
    node_child int,   
    length int,   
    path varchar(100),   
    root int  
 );  


Expected result: 
F    = father
C    = Child
L    = Length (of the path)
R    = Root
Path = Path


The first loop is simply moving the arc table in the path. 
All the other step join the current paths p to the ars a on the condition 
     p.child = a.father and p.length = current_iteration - 1

The values for each path are given like: 
     father   = a.father
     child    = a.child
     length  = p.length + 1 (or current_iteration number)
     root     = p.root
     path     = p.path - a.node_child



F C L R   Path
--------------------
1 2 0 1   1-2         1st Loop (length 1)
1 3 0 1   1-3
1 4 0 1   1-4
2 5 0 2   2-5
2 6 0 2   2-6
6 8 0 6   6-8
6 9 0 6   6-9
4 7 0 4   4-7
-----
2 5 1 1   1-2-5       2nd Loop (length 2)
2 6 1 1   1-2-6
4 7 1 1   1-4-7
6 8 1 1   2-6-8
6 9 1 1   2-6-9
-----
6 8 2 1   1-2-6-8     3rd Loop (length 3)
6 9 2 1   1-2-6-9



To remove the sub path, remove each entry where of the previous iteration which matched in the current iteration.

This at the end of each loop. 
Following this variation then it will look like: 

F C L R   Path
--------------------
1 2 0 1   1-2    --> deleted after iteration 2     
1 3 0 1   1-3
1 4 0 1   1-4    --> deleted after iteration 2
2 5 0 2   2-5    --> deleted after iteration 2
2 6 0 2   2-6    --> deleted after iteration 2
6 8 0 6   6-8    --> deleted after iteration 2
6 9 0 6   6-9    --> deleted after iteration 2
4 7 0 4   4-7    --> deleted after iteration 2
--------------------
2 5 1 1   1-2-5       
2 6 1 1   1-2-6  --> deleted after iteration 3
4 7 1 1   1-4-7
6 8 1 1   2-6-8  --> deleted after iteration 3
6 9 1 1   2-6-9  --> deleted after iteration 3
--------------------
6 8 2 1   1-2-6-8     
6 9 2 1   1-2-6-9



WATCH OUT!!!! Oracle decided to remove the sub patch using the concept of STARTING!
I used a different approach! The algorithm try to create the longest path and remove the path included in another which is longer.
This works only if you are using trees or generically acyclic graph (an by definition a tree is a direct acyclic graph)!!!


Code: 
 create procedure connect_by_test(in p_mode varchar(10) default 'no_sub') as  
   v_length integer;  
   v_count integer;  
   v_boolean varchar(5);  
   c_procedure varchar(30) := 'connect_by_test';  
   v_sql varchar(5000);  
   
 begin  
   
   -- put a clean table on the beginning  
   v_sql := 'delete from paths';  
   write_debug_log(c_procedure, 'truncate table paths', v_sql);  
   execute immediate v_sql;  
   
   
   -- initialization of the control variables used for the loop  
   v_boolean := 'true';  
   v_length := 1;  
   
   
   while v_boolean = 'true' do  
     -- it is needed to distinguish the first iteration with the n-th generic one  
     if (v_length = 1) then  
       -- simply insert all the arcs into the final table  
       v_sql := 'insert into paths(node_father,  node_child,       length,    root,                   path)  
        select a.node_father, a.node_child, ' || v_length || ' , a.node_father, a.node_father || '' - '' || a.node_child  
        from  arc a';  
       write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
       execute immediate v_sql;  
   
     else  
       -- join the arcs with the path known till now  
       v_sql := 'insert into paths(node_father,  node_child,       length,  root,               path)  
        select a.node_father, a.node_child, ' || v_length || ' , p.root, p.path || '' - '' || a.node_child  
        from  arc a join paths p on ( a.node_father = p.node_child )  
        where  p.length = ' || v_length || '-1' ;  
       write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
       execute immediate v_sql;  
   
       if p_mode = 'no_sub' then  
         -- delete the known path joined one more time (I want to keep only the full paths (no subpath)  
         v_sql := 'delete from paths where (node_father, node_child, length) in ( ' ||  
          -- this first query is to get the arcs joined in front of the SUB path  
           ' select a.node_father, a.node_child, ' || v_length - 1 || '  
          from  arc a join paths p on ( a.node_father = p.node_child )  
          where  p.length = ' || v_length || '-1  
          union all ' ||  
           -- this second query is to get the arcs joined after the SUB path  
         ' select p.node_father, p.node_child, ' || v_length - 1 || '  
          from  arc a join paths p on ( a.node_father = p.node_child )  
          where  p.length = ' || v_length || '-1 )' ;  
         write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
         execute immediate v_sql;  
         /**/  
       end if;  
     end if;  
   
     -- check how many records have I inserted in the last iteration  
     write_debug_log(c_procedure, 'perform the count for length: ' || v_length, 'select count(*) from paths where length = ' || v_length);  
     select count(*) into v_count from paths where length = v_length;  
     write_debug_log(c_procedure, 'performed count for length: ' || v_length, v_count);  
   
     -- exit condition: if in the last iteration I did not generated any entry, then I have found already every possible path.  
     if v_count = 0 then  
       -- no other loop, change the while condition  
       v_boolean := 'false';  
     else  
       --there will be another loop  
       --increment the level to be used for the next loop  
       v_length  := v_length + 1;  
     end if;  
   
   
   
     -- this is just an emergency break: after 10 iteration just stop.  
     if (v_length > 10) then  
       v_boolean := 'false';  
     end if;  
   
   
     write_debug_log(c_procedure, 'variable loop status: v_length = ' || v_length || '; v_boolean = ' || v_boolean, '');  
   end while;  
   
   end;  
   



After performing a test, here is the output example: 

(SUB mode = with sub path)
NODE_FATHER;NODE_CHILD;LENGTH;ROOT;PATH
1;2;1;1;1 - 2
1;3;1;1;1 - 3
1;4;1;1;1 - 4
2;5;1;2;2 - 5
2;6;1;2;2 - 6
4;7;1;4;4 - 7
6;8;1;6;6 - 8
6;9;1;6;6 - 9
2;5;2;1;1 - 2 - 5
2;6;2;1;1 - 2 - 6
4;7;2;1;1 - 4 - 7
6;8;2;2;2 - 6 - 8
6;9;2;2;2 - 6 - 9
6;8;3;1;1 - 2 - 6 - 8
6;9;3;1;1 - 2 - 6 - 9


(No SUB mode = without sub path)
NODE_FATHER;NODE_CHILD;LENGTH;ROOT;PATH
1;3;1;1;1 - 3
2;5;2;1;1 - 2 - 5
4;7;2;1;1 - 4 - 7
6;8;3;1;1 - 2 - 6 - 8
6;9;3;1;1 - 2 - 6 - 9



Cool! Very nice! Now I understood!
But what does it means? Of course I am working here! I am not playing with the concept of table Arc and Paths... What can I do now in order to use on my real table?
I am a lazy guy! I want to do just a copy and paste!

Since you need to have totally dynamic code, Hana is having some issue with retrieving the result from a dynamic query.

You need to create first a table:

 table.schemaName = "My Schema";  
   
 table.tableType = COLUMNSTORE;  
 table.columns = [  
   {name = "ID"; sqlType = INTEGER; nullable = false;},  
   {name = "SQL_TEXT"; sqlType = NVARCHAR; nullable = true; length = 5000;},  
   {name = "NUM_RESULT"; sqlType = INTEGER; nullable = true; },  
   {name = "CHR_RESULT"; sqlType = NVARCHAR; nullable = true; length = 5000;}  
 ];  
 table.primaryKey.pkcolumns = ["ID"];  
   

Which simply generate the following DDL:


 CREATE COLUMN TABLE TEMP_RESULTS (  
           "ID" INTEGER CS_INT NOT NULL ,   
           "SQL_TEXT" NVARCHAR(5000),   
           "NUM_RESULT" INTEGER CS_INT,   
           "CHR_RESULT" NVARCHAR(5000),   
           PRIMARY KEY ("ID")  
 ) ;  

create sequence temp_result_id starting with 1 ;


Ok, then I can create the following code:


 PROCEDURE dynamic_connect_by (   
      in p_source_schema_name varchar(100),   
      in p_source_table_name varchar(100),   
      in p_paths_schema_name varchar(100),   
      in p_paths_table_name varchar(100),   
      in p_field_father varchar(100),   
      in p_field_child varchar(100),   
      in p_list_of_fields varchar(1000),  
      in p_where varchar(1000),  
      in p_sub_path varchar(10),   
      in p_start_with varchar(100)       
 )   
 LANGUAGE SQLSCRIPT AS  
   
      v_length integer;  
      v_count integer;  
      v_boolean varchar(5);  
      c_procedure varchar(30) := 'connect_by_test_dynamic';  
      v_sql varchar(5000);  
      v_id integer;   
      v_list_of_fields varchar(1000);  
      v_alias_list_of_fields varchar(1000);  
        
 begin   
        
      call write_debug_log(c_procedure, 'start the procedure', '');  
   
      -- put a clean table on the beginning  
      v_sql := 'delete from ' || p_paths_schema_name || '.' || p_paths_table_name;  
      call write_debug_log(c_procedure, 'truncate table paths', v_sql);       
      execute immediate v_sql;  
        
      v_list_of_fields := replace(p_list_of_fields, ' ', '');
      if v_list_of_fields != '' then 
 v_list_of_fields := v_list_of_fields || ','; 
 v_alias_list_of_fields := 'a.' || replace(p_list_of_fields,   ',',   ',a.') || ',';
      else
 v_list_of_fields := '';
 v_alias_list_of_fields := ''; 
      end if;
        

      -- initialization of the control variables used for the loop  
      v_boolean := 'true';  
      v_length := 1;  
        
      while v_boolean = 'true' do  
             
           -- it is needed to distinguish the first iteration with the n-th generic one  
           if (v_length = 1) then   
                     -- simply insert all the arcs into the final table  
                     v_sql := 'insert into ' || p_paths_schema_name || '.' || p_paths_table_name || ' (' || v_list_of_fields || p_field_father || ',  ' || p_field_child || ',       length,         root,                   path)   
                                          select ' || v_alias_list_of_fields || ' a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length || ' , a.' || p_field_father || ', a.' || p_field_father || ' || '' - '' || a.' || p_field_child || '  
                                          from  ' || p_source_schema_name || '.' || p_source_table_name || ' a';  
                       
                     if p_start_with != '' then   
                           v_sql := v_sql || ' where ' || p_field_father || ' = ''' || p_start_with || '''';  
                                 
                     if P_WHERE != '' then   
                      V_SQL := V_SQL || ' and ' || P_WHERE;  
                     end if;  
             
               else  
             
                     if P_WHERE != '' then   
                      V_SQL := V_SQL || ' where ' || P_WHERE;  
                     end if;  
                 
               end if;   
                 
                     call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                     execute immediate v_sql;  
   
           else   
                     -- join the arcs with the path known till now  
                     v_sql := 'insert into ' || p_paths_schema_name || '.' || p_paths_table_name || '(' || v_list_of_fields || p_field_father || ',  ' || p_field_child || ',       length,  root,               path)  
                                           select ' || v_alias_list_of_fields || 'a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length || ' , p.root, p.path || '' - '' || a.' || p_field_child || '  
                                          from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                          where  p.length = ' || v_length || '-1' ;  
                     call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                     execute immediate v_sql;  
   
                     if p_sub_path = 'false' then   
                          /**/  
                          -- delete the known path joined one more time (I want to keep only the full paths (no subpath)  
                          v_sql := 'delete from ' || p_paths_schema_name || '.' || p_paths_table_name || ' where (' || p_field_father || ', ' || p_field_child || ', length) in ( ' ||  
                                               -- this first query is to get the arcs joined in front of the SUB path  
                                           ' select a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length - 1 || '  
                                               from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                               where  p.length = ' || v_length || '-1   
                                                    union all ' ||  
                                                -- this second query is to get the arcs joined after the SUB path  
                                              ' select p.' || p_field_father || ', p.' || p_field_child || ', ' || v_length - 1 || '  
                                               from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                               where  p.length = ' || v_length || '-1 )' ;  
                          call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                          execute immediate v_sql;  
                          /**/  
                     end if;  
           end if;  
             
           -- check how many records have I inserted in the last iteration  
           select temp_result_id.nextval into v_id from dummy;   
           v_sql := 'insert into TEMP_RESULTS (id, num_result) select ' || v_id || ', count(*) from ' || p_paths_schema_name || '.' || p_paths_table_name || ' where length = ' || v_length;  
           call write_debug_log(c_procedure, 'perform the count for length: ' || v_length, v_sql);       
           execute immediate v_sql;  
             
           select num_result into v_count from TEMP_RESULTS where id = v_id;  
           call write_debug_log(c_procedure, 'performed count for length: ' || v_length, v_count);       
             
   
   
                  
           -- exit condition: if in the last iteration I did not generated any entry, then I have found already every possible path.   
           if v_count = 0 then   
                -- no other loop, change the while condition  
                v_boolean := 'false';  
           else   
                --there will be another loop  
                --increment the level to be used for the next loop  
                v_length  := v_length + 1;       
           end if;  
   
   
   
           -- this is just an emergency break: after 10 iteration just stop.   
           if (v_length > 10) then   
       v_boolean := 'false';  
           end if;  
             
           call write_debug_log(c_procedure, 'variable loop status: v_length = ' || v_length || '; v_boolean = ' || v_boolean, '');       
       end while;  
   
 end;  


It is almost impossible to read.
If you want to understand the code, just remain on the basic approach I put previously...
For your generic usage, you can just create this function and then invoke it in the following way:


 -- be sure you have a proper table:  
 create table ['Schema name where the output table is'].['Output table name'] (   
                ['Additional fields in the Select statement'],   
                ['Father Field'],   
                ['Chield Field'],   
                "ROOT_PROF" VARCHAR(36) ,   
                "HIERACHY" NUMBER,   
                "LENGTH" NUMBER,   
                "PATH" VARCHAR(3000),   
                "ROOT" VARCHAR(3000)   
           )  
   
   
      call dynamic_connect_by(  
           'Schema name where the input table is',   
           'Input table name',   
           'Schema name where the output table is',   
           'Output table name',   
           'Father Field',   
           'Child Field',   
           'Additional fields in the Select statement',   
           'condition for the where statement',   
           'TRUE' if you want also the sub-paths, 'FALSE' if you want only the complete paths   
           '' if you want to comunicate which is the starting point (make the algorithm faster)  
      );  
   



The equivalent in Oracle is:
 SELECT employee_id, last_name, manager_id  
 FROM employees  
 CONNECT BY PRIOR employee_id = manager_id;  
   
 ...  
   
 SELECT ['Additional fields in the Select statement'], ['Father Field'], ['Chield Field']  
 FROM ['Schema name where the output table is'].['Output table name']  
 where ['condition for the where statement']  
 CONNECT BY PRIOR employee_id = manager_id;  




giovedì 13 febbraio 2014

Retrieve result from dynamic execution in SAP HANA



In oracle I have a great tool which is retrieving result from dynamic executions.

 declare        
      v_sql varchar2(100);  
      v_count number;   
 begin   
      v_sql := 'select count(*) from dual';  
      EXECUTE IMMEDIATE v_sql INTO v_count;  
 end;  
   


In SAP HANA such feature does not exist.
What can we do?

 CREATE COLUMN TABLE TEMP_RESULTS (  
           "ID" INTEGER CS_INT NOT NULL ,   
           "SQL_TEXT" NVARCHAR(5000),   
           "NUM_RESULT" INTEGER CS_INT,   
           "CHR_RESULT" NVARCHAR(5000),   
           PRIMARY KEY ("ID")  
 ) ;  

create sequence temp_result_id starting with 1 ;



Then the usage code
   
 begin   
      declare v_id integer;   
      declare v_sql varchar(100);  
        
      select temp_result_id.nextval into v_id from dummy;   
      v_sql := 'insert into TEMP_RESULTS(id, num_result) select ' || v_id || ', count(*) from dummy';  
      execute immediate v_sql;  
             
      select num_result into v_count from TEMP_RESULTS where id = v_id;  
 end;  

martedì 11 febbraio 2014

Chart JS

You need to have a easy a quick chart for your website. 
Here there is a free JS library, fast, light and easy to use. 


It can cover the most common needs, it is free and it is very easy to use and also well documented.
It is missing just the legend: there is no legend you can add...
But actually I found on internet a fix for it.
In particular there is:



I used it for my website as well. Here a short tutorial.
In order to explain my language skills, I have used a bit of iconographic and this is the result 




I choose the following chart and then I have included the correspondent legend.


You need first of all Chart.js, you can find it here: 

Then you need to add the JS file for the legend. You can find it here: 



I think that the code of the legend is amazing because (currently) it is just that: 
 function legend(parent, data) {  
   parent.className = 'legend';  
   var datas = data.hasOwnProperty('datasets') ? data.datasets : data;  
   datas.forEach(function(d) {  
     var title = document.createElement('span');  
     title.className = 'title';  
     title.style.borderColor = d.hasOwnProperty('strokeColor') ? d.strokeColor : d.color;  
     title.style.borderStyle = 'solid';  
     parent.appendChild(title);  
     var text = document.createTextNode(d.title);  
     title.appendChild(text);  
   });  
 }  




Well now, into the HTML page:



 <!DOCTYPE html>  
 <html lang="en">  
  <head>  
   [...]  
  </head>  
  <body>  
   [...]  
    <div class="row featurette">  
     <div class="col-md-7">  
      <div class="text_container">  
       <h2 class="featurette-heading">Language Skills<span class="text-muted"> - A mean of trasfer</span></h2>  
       <p class="lead">  
        To travel, to meet new culture, to understand the other point of view, to be sure that the message you drop is perceived in the proper way. Comunication is an amazing science.   
        <br>"Take advantage of every opportunity to practice your communication skills so that when important occasions arise, you will have the gift, the style, the sharpness, the clarity, and the emotions to affect other people." Jim Rohn</p>  
       <div class="legend" id="lang_legend"></div>  
      </div>  
     </div>  
     <div class="col-md-5">  
      <canvas id="lang_canvas" class="chart" width="600" height="450"></canvas>  
     </div>  
    </div>  
   [...]  
   <script src="js/Chart.js"></script>  
   <script src="js/legend.js"></script>  
   <script>  
   [...]  
   var chartData = [  
     {  
      value : 0.99999,  
      color : "#00FF00",   
      title : 'Italian - Native'  
     },  
     {  
      value : 0.95,  
      color : "#FF0000",   
      title : 'English - Fluent'  
     },  
     {  
      value : 0.9,  
      color : "#000000",   
      title : 'German - Fluent'  
     },  
     {  
      value : 0.7,  
      color : "#0000FF",   
      title : 'French - Good'  
     },  
     {  
      value : 0.4999,  
      color : "#FFFF00",   
      title : 'Spanish - Basics'  
     }  
    ];  
   var myPolarArea = new Chart(document.getElementById("lang_canvas").getContext("2d")).PolarArea(chartData);  
   legend(document.getElementById("lang_legend"), chartData);  
   </script>  
  </body>  
 </html>  



Please, note that I did not use fix numbers for the extreme values.
I mean that I did not give to english a value of 1 or to spanish a value of 0.5.

I think I found a small bug: giving such values, the library did not work properly (it was hiding spanish) and it was having such problem not only with such chart but also with other.

Anyway, not a big issue ;)
Globally the libraries are both very good!!!!

giovedì 6 febbraio 2014

Orcle: parameters for PLSQL dynamic statement




Reference: http://docs.oracle.com/cd/B10500_01/appdev.920/a96624/11_dynam.htm

How to pass input and output parameter to a dynamic PLSQL statement?
Generic code syntax is:

 EXECUTE IMMEDIATE dynamic_string  
 [INTO {define_variable[, define_variable]... | record}]  
 [USING [IN | OUT | IN OUT] bind_argument  
   [, [IN | OUT | IN OUT] bind_argument]...]  
 [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];  


And to have a practical example:

 DECLARE  
   sql_stmt VARCHAR2(200);  
   my_empno NUMBER(4) := 7902;  
   my_ename VARCHAR2(10);  
   my_job  VARCHAR2(9);  
   my_sal  NUMBER(7,2) := 3250.00;  
 BEGIN  
   sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2  
    RETURNING ename, job INTO :3, :4';  
   /* Bind returned values through USING clause. */  
   EXECUTE IMMEDIATE sql_stmt  
    USING my_sal, my_empno, OUT my_ename, OUT my_job;  
   /* Bind returned values through RETURNING INTO clause. */  
   EXECUTE IMMEDIATE sql_stmt  
    USING my_sal, my_empno RETURNING INTO my_ename, my_job;  

 END;  




mercoledì 5 febbraio 2014

Easy DDL Extractor



Easy DDL extractor: 

  CREATE TABLE "my_ddl"  
   (  "OBJECT_TYPE" VARCHAR2(30 CHAR),  
     "OBJECT_NAME" VARCHAR2(30 CHAR),  
     "EXTRACTED_ON" DATE,  
     "DDL" CLOB,  
     "OSUSER" VARCHAR2(100 CHAR)  
   );  
  CREATE TABLE "my_ddl_CONF_TABLE_DATA_EXTRACT"  
   (  "TABLE_NAME" VARCHAR2(30 CHAR)  
   )  
 /  
 create or replace  
 PACKAGE "DDL_EXTRACTOR" AS   
  /*------------------  
  Package Version: 0.01  
  -------------------*/  
  c_object_order varchar(30000 char) := 'select ''DATABASE LINK'' as object_type, ''01'' as order_id from dual union all  
            select ''SYNONYM'' as object_type, ''02'' as order_id from dual union all  
            select ''FUNCTION'' as object_type, ''09'' as order_id from dual union all  
            select ''INDEX'' as object_type, ''06'' as order_id from dual union all  
            select ''PACKAGE'' as object_type, ''11'' as order_id from dual union all  
            select ''PACKAGE BODY'' as object_type, ''12'' as order_id from dual union all  
            select ''PROCEDURE'' as object_type, ''10'' as order_id from dual union all  
            select ''SEQUENCE'' as object_type, ''03'' as order_id from dual union all  
            select ''TABLE'' as object_type, ''05'' as order_id from dual union all  
            select ''TRIGGER'' as object_type, ''07'' as order_id from dual union all  
            select ''TYPE'' as object_type, ''04'' as order_id from dual union all  
            select ''VIEW'' as object_type, ''08'' as order_id from dual ';   
  c_list_objects varchar(30000 char) := 'select object_name, object_type from user_objects where  
    (object_type in (''TABLE'', ''DATABASE LINK'', ''FUNCTION'', ''PROCEDURE'', ''PACKAGE'', ''SEQUENCE'', ''VIEW''))  
    and  
    (object_type != ''TABLE'' or (object_name not like ''STA%'' and object_name not like ''TMP%''))  
    union all  
    SELECT object_name, object_type FROM all_objects WHERE object_type = ''DIRECTORY''  
    ';             
  procedure export_object(p_object_type in varchar2, p_object_name in varchar2);  
 procedure INSERT_SCRIPT(p_table_name varchar2) ;  
 function get_insert_script_select(p_table_name varchar2) return varchar2 ;  
 PROCEDURE Main;  
  PROCEDURE writelog(  
            P_PROCEDURE_NAME IN VARCHAR2,  
            P_ACTIVITY IN VARCHAR2,  
            P_LOGTEXT IN VARCHAR2,  
            P_LOG_LEVEL IN NUMBER DEFAULT 0,  
            P_SQL_TEXT IN VARCHAR2 DEFAULT NULL,  
            P_ERROR_CODE IN VARCHAR2 DEFAULT NULL,  
            P_EXTRA_INFO IN VARCHAR2 DEFAULT NULL  
           );  
 end DDL_EXTRACTOR;  
 /  
 create or replace  
 PACKAGE BODY "DDL_EXTRACTOR" AS  
  /*------------------  
  Package Version: 0.01  
  -------------------*/  
 /* This package will mainly need these two tables:  
 --This table contains the generated DDL and data  
 create table my_ddl(  
  object_type varchar2(30 char),  
  object_name varchar2(30 char),  
  extracted_on date,  
  ddl clob  
 );  
 --This table contains the configuration of which tables to extract data from it.  
 create table my_ddl_conf_table_data_extract(  
  table_name varchar2(30 char)  
 );  
 */  
  PROCEDURE  Main AS  
   V_Procedure_Name Varchar2(100) := 'Main';  
   V_Parameters   Varchar2(32000);  
   v_return number;  
   v_message varchar2(4000 char);  
   rec_object_type varchar2(30 char);  
   rec_object_name varchar2(30 char);  
   v_sql_loop varchar2(32000 char);  
   TYPE t_refcrs IS REF CURSOR;  
   c_exttable t_refcrs;  
  BEGIN  
   --get immediately the min log level (this will reduce automatically the number of entries in the log)  
   g_min_log_level := 10; -- TODO 13-11-2012: calculate the log_level from the config_variables table  
   execute immediate 'truncate table my_ddl drop all storage'; commit;  
   v_sql_loop := 'with extraction_order as ( ' || c_object_order || ' )  
          select list.object_type, list.object_name  
          from ( ' || c_list_objects || ' ) list join extraction_order on (extraction_order.object_type = list.object_type)  
          order by order_id, object_name  
          ';      
   Writelog(V_Procedure_Name, 'main loop query', null, c_log_level_debug, v_sql_loop);  
   OPEN c_exttable FOR v_sql_loop;  
       loop  
        FETCH c_exttable INTO rec_object_type, rec_object_name;  
        exit when c_exttable%notfound;  
     export_object(rec_object_type, rec_object_name);  
   end loop;  
  end;  
 procedure export_object(p_object_type in varchar2, p_object_name in varchar2) as  
   v_procedure_name VARCHAR2(30) := 'export_object' ;  
   v_sql       varchar2(32000);  
   V_Parameters   Varchar2(32000);  
   v_object_type varchar2(30);  
   V_PRESENT NUMBER;  
   V_OSUSER VARCHAR2(100 CHAR);  
  begin  
    SELECT OSUSER  
    INTO V_OSUSER  
    FROM V$SESSION  
    where audsid=userenv('SESSIONID');  
    if p_object_type = 'DATABASE LINK' then  
     v_object_type := 'DB_LINK';  
    else  
     v_object_type := p_object_type;  
    END IF;   
    insert into my_ddl(osuser, object_type, object_name, extracted_on, ddl)  
        values(V_OSUSER, p_object_type, p_object_name, sysdate, dbms_metadata.get_ddl(v_object_type, p_object_name));  
    commit;  
    if v_object_type = 'TABLE' then  
      for rec in (select * from user_indexes where table_name = p_object_name) loop  
       export_object ('INDEX', rec.index_name);       
      end loop;  
      for rec in (select * from user_triggers where table_name = p_object_name) loop  
       export_object ('TRIGGER', rec.trigger_name);       
      end loop;  
      v_present := 0;  
      v_sql := 'select case when count(*) > 0 then 1 else 0 end as vpresent from my_ddl_conf_table_data_extract where table_name = ''' || p_object_name || '''';  
      execute immediate v_sql into v_present;  
      if v_present = 1 then  
       INSERT_SCRIPT(p_object_name);  
      end if;  
    END IF;  
    if v_object_type = 'PACKAGE' then  
     export_object ('PACKAGE_BODY', p_object_name);       
    end if;  
 end;  
  procedure INSERT_SCRIPT(P_TABLE_NAME VARCHAR2) AS  
    v_procedure_name VARCHAR2(30) := 'INSERT_SCRIPT' ;  
    v_sql       varchar2(32000);  
    v_sql_loop varchar2(32000 char);  
    TYPE t_refcrs IS REF CURSOR;  
    c_exttable t_refcrs;  
    v_parameters   varchar2(32000);  
    rec_script varchar2(32000);  
    v_object_type varchar2(30);  
    V_NON_EMPTY NUMBER;  
    v_date date;  
    V_OSUSER VARCHAR2(100 CHAR);  
 begin  
   v_sql := 'select case when count(*) > 0 then 1 else 0 end from ' || p_table_name;  
   execute immediate v_sql into v_non_empty;  
   v_sql := ' ';  
   if v_non_empty = 1 then  
     SELECT OSUSER  
     INTO V_OSUSER  
     FROM V$SESSION  
     WHERE AUDSID=USERENV('SESSIONID');  
     v_date := sysdate;  
     insert into my_ddl(osuser, object_type, object_name, extracted_on) values( V_OSUSER, 'TABLE_DATA', p_table_name , v_date);  
     v_sql_loop := get_insert_script_select(p_table_name);  
     OPEN c_exttable FOR v_sql_loop;  
         loop  
          FETCH c_exttable INTO rec_script;  
          EXIT WHEN C_EXTTABLE%NOTFOUND;  
         IF LENGTH(V_SQL) + LENGTH(REC_SCRIPT) > 32000 THEN  
          UPDATE my_ddl  
          SET DDL = DDL || V_SQL  
          WHERE EXTRACTED_ON = V_DATE AND OBJECT_TYPE = 'TABLE_DATA' AND OBJECT_NAME = P_TABLE_NAME;  
          V_SQL := ' ';  
         ELSE  
          V_SQL := V_SQL || REC_SCRIPT || CHR(10);  
         end if;  
     end loop;  
   end if;  
   commit;  
 END;  
 FUNCTION GET_INSERT_SCRIPT_SELECT(P_TABLE_NAME VARCHAR2) RETURN VARCHAR2 AS  
    B_FOUND BOOLEAN := FALSE;  
    V_TEMPA VARCHAR2 (8000);  
    V_TEMPB VARCHAR2 (8000);  
    V_TEMPC VARCHAR2 (255);  
    v_procedure_name VARCHAR2(30) := 'GET_INSERT_SCRIPT_SELECT' ;  
    v_sql       varchar2(32000);  
    V_Parameters   Varchar2(32000);  
    v_object_type varchar2(30);  
  begin  
   v_parameters :=         ' P_TABLE_NAME: ' || p_table_name;  
   writelog(v_procedure_name, 'START', 'Parameters: ' || v_parameters, c_log_level_info);  
    FOR TAB_REC IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = UPPER (P_TABLE_NAME)) LOOP  
       b_found := true;  
       V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';  
       FOR COL_REC IN (  
                select *  
                FROM user_tab_cols  
                WHERE TABLE_NAME = TAB_REC.TABLE_NAME  
                order by column_id  
               ) LOOP  
         if col_rec.column_id = 1 then  
           V_TEMPA := V_TEMPA;  
         else  
           v_tempa := v_tempa || ', ';  
           V_TEMPB := V_TEMPB || ', ';  
         END IF;  
         V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;  
         if instr (col_rec.data_type, 'VARCHAR2') > 0 then  
           V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';  
         ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN  
           V_TEMPC := '''to_date(''''''||to_char(' || COL_REC.COLUMN_NAME || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';  
         ELSE  
           V_TEMPC := COL_REC.COLUMN_NAME;  
         end if;  
         V_TEMPB := V_TEMPB || '''||decode(' || COL_REC.COLUMN_NAME || ',Null,''Null'',' || V_TEMPC || ')||''';  
       END LOOP;  
       V_TEMPA := V_TEMPA || ') values (' || V_TEMPB || ');'' as dml from ' || TAB_REC.TABLE_NAME || '';  
    END LOOP;  
    return v_tempa;  
 end;  
 END DDL_EXTRACTOR;  
 /  



martedì 4 febbraio 2014

Create and check DBMS jobs in Oracle




Create an automatic job can be difficult in Oracle. 
If you use the package DBMS_SCHEDULER it can offer you a better and easy support. 
Here is the generic approach: 

 BEGIN  
  dbms_scheduler.create_job(  
   job_name    => [Unique identifier],  
   job_type    => 'PLSQL_BLOCK',  
   job_action   => 'BEGIN [code to run ]END;',  
   start_date   => [a date],  
   repeat_interval => 'freq=secondly;',  
   end_date    => [a date],  
   enabled     => TRUE,  
   auto_drop    => TRUE  
  );  
  dbms_scheduler.set_attribute (  
   NAME   => [Unique identifier],  
   ATTRIBUTE => 'max_failures',  
   VALUE   => [your deisred value] --> how many times it can fail before to stop the execution  
  );  
  dbms_scheduler.set_attribute (  
   NAME   => [Unique identifier],  
   ATTRIBUTE => 'max_runs',  
   VALUE   => [your deisred value] --> how many times it will be executed (independently of the result) before to stop the execution  
  );  
 END;  
 /  


An example:


 BEGIN  
  dbms_scheduler.create_job(  
   job_name    => 'CREATE_NEW_RUN_' || sysdate,  
   job_type    => 'PLSQL_BLOCK',  
   job_action   => 'BEGIN READER_PACKAGE.MAIN(to_date(''' || sysdate || ''', ''dd-mm-yyyy'')); END;',  
   start_date   => SYSTIMESTAMP,  
   repeat_interval => 'freq=secondly;',  
   end_date    => NULL,  
   enabled     => TRUE,  
   auto_drop    => TRUE  
  );  
  dbms_scheduler.set_attribute (  
   NAME   => 'CREATE_NEW_RUN_' || sysdate,  
   ATTRIBUTE => 'max_failures',  
   VALUE   => 1);  
  dbms_scheduler.set_attribute (  
   NAME   => 'CREATE_NEW_RUN_' || sysdate,  
   ATTRIBUTE => 'max_runs',  
   VALUE   => 1);  
 END;  
 /  


How to check the status of the jobs?
 SELECT  
   job_name,  
   enabled,  
   run_count,  
   max_runs,  
   failure_count,  
   max_failures  
 FROM dba_scheduler_jobs  
 WHERE job_name = decode(upper('&1'), 'ALL', job_name, upper('&1'));  



Reference:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm

Oracle Array und Hash table



I was thinking that in Oracle PLSQL the concept of Array is not implemented at his best!
Then I saw this implementation. Basically they decided to implement the concept of Array and of Hash table with ALMOST the same concept, simply distinguishing on the way you (developer) index it.


HASH TABLE:

 set serveroutput on  
 DECLARE  
  TYPE assoc_array IS TABLE OF VARCHAR2(30)  
  INDEX BY VARCHAR2(30);  
  state_array assoc_array;  
 BEGIN  
  state_array('Alaska') := 'Juneau';  
  state_array('California') := 'Sacramento';  
  state_array('Oregon') := 'Salem';  
  state_array('Washington') := 'Olympia';  
  dbms_output.put_line(state_array('Alaska'));  
  dbms_output.put_line(state_array('California'));  
  dbms_output.put_line(state_array('Oregon'));  
  dbms_output.put_line(state_array('Alaska'));  
 END;  
 /  



ARRAY:

 set serveroutput on  
 DECLARE  
  TYPE bin_array IS TABLE OF VARCHAR2(30)  
  INDEX BY BINARY_INTEGER;  
  state_array bin_array;   
 BEGIN  
  state_array(1) := 'Alaska';  
  state_array(2) := 'California';  
  state_array(3) := 'Oregon';  
  state_array(4) := 'Washington';  
  FOR i IN 1 .. state_array.COUNT LOOP  
   dbms_output.put_line(state_array(i));  
  END LOOP;  
 END;  
 /  
 CREATE TABLE t (  
 resultcol VARCHAR2(20));  
 DECLARE  
  TYPE bin_array IS TABLE OF VARCHAR2(30)  
  INDEX BY BINARY_INTEGER;  
  state_array bin_array;   
 BEGIN  
  state_array(1) := 'Alaska';  
  state_array(2) := 'California';  
  state_array(3) := 'Oregon';  
  state_array(4) := 'Washington';  
  FORALL i IN 1 .. state_array.COUNT  
  INSERT INTO t VALUES (state_array(i));  
  COMMIT;  
 END;  
 /  
 SELECT * FROM t;  


Well note that this is not really an array, but is an hash where the indexes are integers.

Source:
http://psoug.org/reference/arrays.html