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