Oracle:Linear regression through a SQL Query

I want to have a linear approximation directly in an SQL query.

To make it easy: having a generic function f, I want to approximate it with the function:

Being sure that I found the A and B which minimize the global error between f and f' (where f' is the linear approximation of f). 

Let's generate some data: 

 create table zzz_some_data as   
  SELECT    
    'X' groupid,   
    level x,    
    exp(level/10) + sin(level/10 *3.14) + dbms_random.value(0,1) y   
   FROM DUAL   
   CONNECT BY LEVEL <= 20   
 union all   
  SELECT    
    'Y' groupid,   
    level x,    
    exp(level/10) + Cos(level/10 *3.14) + dbms_random.value(0,1) y  
   FROM DUAL   
   CONNECT BY LEVEL <= 20;  

As you can see there is a random factor. At the bottom of the tutorial, you can find the exact data generated. 

Plotting these numbers the functions look like this: 

Running the following query, I can get the value of A and B for the two functions:

 WITH mean_estimates AS  
 (  SELECT GroupID  
      ,AVG(x) AS xmean  
      ,AVG(y) AS ymean  
      ,min(y) as first_seq  
      ,max(y) as last_seq  
   FROM zzz_some_data  
   GROUP BY GroupID  
 ),  
 stdev_estimates AS  
 (  SELECT pd.GroupID  
      ,CASE     
        WHEN SUM(POWER(x - xmean, 2)) = 0 THEN 1   
        when COUNT(*) = 1 then 10000000  
        ELSE SQRT(SUM(POWER(x - xmean, 2)) / (COUNT(*) - 1))   
       END AS xstdev  
      ,CASE  
        when count(*) = 1 then 10000000   
        else SQRT(SUM(POWER(y - ymean, 2)) / (COUNT(*) - 1))     
       end AS ystdev  
   FROM zzz_some_data pd  
   INNER JOIN mean_estimates pm ON pm.GroupID = pd.GroupID  
   GROUP BY pd.GroupID, pm.xmean, pm.ymean  
 ),  
 standardized_data AS          -- increases numerical stability  
 (  SELECT pd.GroupID  
      ,(x - xmean) / xstdev                  AS xstd  
      ,CASE ystdev WHEN 0 THEN 0 ELSE (y - ymean) / ystdev END AS ystd  
   FROM zzz_some_data pd  
   INNER JOIN stdev_estimates ps ON ps.GroupID = pd.GroupID  
   INNER JOIN mean_estimates pm ON pm.GroupID = pd.GroupID  
 ),  
 standardized_beta_estimates AS  
 (  SELECT GroupID  
      ,CASE WHEN SUM(xstd * xstd) = 0 THEN 0  
         ELSE SUM(xstd * ystd) / (COUNT(*) - 1) END     AS betastd  
   FROM standardized_data pd  
   GROUP BY GroupID  
 ),   
 linear_model as   
 (  
 SELECT   
    pb.GroupID,     
    ymean - xmean * betastd * ystdev / xstdev AS BETA,   
    betastd * ystdev / xstdev         AS ALPHA,   
    first_seq,   
    last_seq  
 FROM standardized_beta_estimates pb  
 INNER JOIN stdev_estimates ps ON ps.GroupID = pb.GroupID  
 INNER JOIN mean_estimates pm ON pm.GroupID = pb.GroupID  
 )  
 select   
  GroupID,   
  alpha,   
  Beta  
 from linear_model  
 order by GroupID desc;  

This query generate the following output: 

 GROUPID   ALPHA    BETA  
 ------- ---------- ----------  
 Y    ,341603277 ,27926809   
 X    ,221725267 1,66190364   

Let's see now the results: 

Here the plot of the approximation of X:

Here the plot of the approximation of Y:

 CREATE TABLE "ZZZ_SOME_DATA"  
  (  
   "GROUPID" CHAR(1 BYTE),  
   "X"    NUMBER,  
   "Y"    NUMBER  
  );  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','1','1,61932317662496064883408392432113402523');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','2','2,77694913213341470441940320202765814948');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','3','2,91090102987016066482064043800309804071');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','4','3,13678897398740391101470575963142792902');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','5','3,31605237538417176477155850638842190865');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','6','3,13667075089211953539069065645455345878');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','7','3,47488861159608290867160313703997232386');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','8','3,42251149078741711848240969510483689103');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','9','3,70603465587716541507204559098228342981');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','10','3,49314419776404379007856528151793653642');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','11','3,24172315449369167895615169724235970383');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','12','2,96881260394832309096782780931487864967');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','13','3,07513520161764733445750922412894102533');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','14','3,81479688098856311171457385615269516843');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','15','4,32475565056326473828436305142365381565');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','16','4,85233459222270714560038164481370508461');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','17','5,61989845355259006300115338181868398089');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','18','6,31700004318003808435939347557223237057');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','19','6,76689263318576069778969371993663187878');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('X','20','7,82576523994149918219695417536412769495');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','1','2,53714598427679136859871205905510695503');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','2','2,3110817502573158903868891890087776345');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','3','1,94494243937675278618005281210530984129');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','4','2,25982980116506374957923410951751088925');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','5','1,86603427812480463944739874768807857366');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','6','2,05505938632236899572636979590661767446');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','7','2,3258999982985244735180126549118494278');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','8','1,83188925742276377791489099175111599133');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','9','2,41166969667358720655946060775919042819');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','10','1,8104604598253185583746180090281519982');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','11','2,87738150798966416515834453685956594055');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','12','3,43069799388754748665140656518089681805');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','13','3,54673238603787598200769925226968142282');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','14','4,43614682007291136740320048898865794443');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','15','4,87577441430844397983546298110499857896');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','16','6,00804958270558010522626027655493241713');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','17','6,3755271699704708032463599263767041073');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','18','7,12510967938699587673395044064905861434');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','19','8,17916759972655812255130821868093121265');  
 Insert into ZZZ_SOME_DATA (GROUPID,X,Y) values ('Y','20','9,11344975307640059368329002839752407251');  
 COMMIT;  

Oracle: SQL for list of numbers or list of dates

For numbers:

I want to have a query which returns the list of the dates from today, for the next 10 years:

   SELECT   
     level seq,   
     _starting_ + _delta_ * level as ddate,   
   FROM DUAL  
   CONNECT BY LEVEL <= _occurrences_  

The "parameters" needed by this query are:

• Starting: the value where to start
• Delta: the number of days between each occurrence of the list
• Occurrences: How many occurrences in the list (how long is the list)

For example, given the following query:

SELECT
   level seq,
   100 + 5 * level as value
FROM DUAL
CONNECT BY LEVEL <= 10

I will have this output

     SEQ   VALUE  
 ---------- ----------  
      1    105   
      2    110   
      3    115   
      4    120   
      5    125   
      6    130   
      7    135   
      8    140   
      9    145   
     10    150   
  10 Zeilen gewählt  

For dates:

I want to have a query which returns the list of the dates from today, for the next 10 years:

   SELECT   
     level seq,   
     to_date(_starting_date_, 'yyyymmdd') + _delta_ * level as ddate,   
     to_char(to_date(_starting_date_, 'yyyymmdd') + _delta_ * level, 'yyyy-ww') as week,   
     to_char(to_date(_starting_date_, 'yyyymmdd') + _delta_ * level, 'yyyy-mm') as month  
   FROM DUAL  
   CONNECT BY LEVEL <= _occurrences_  

The "parameters" needed by this query are:

• Starting_date: the date where to start
• Delta: the number of days between each occurrence of the list
• Occurrences: How many occurrences in the list (how long is the list)

For example, given the following query:

   SELECT   
     level seq,   
     to_date('20140101', 'yyyymmdd') + 7 * level as ddate,   
     to_char(to_date('20140101', 'yyyymmdd') + 7 * level, 'yyyy-ww') as week,   
     to_char(to_date('20140101', 'yyyymmdd') + 7 * level, 'yyyy-mm') as month  
   FROM DUAL  
   CONNECT BY LEVEL <= 10  

I will have this output

     SEQ DDATE  WEEK  MONTH   
 ---------- -------- ------- -------  
      1 08.01.14 2014-02 2014-01   
      2 15.01.14 2014-03 2014-01   
      3 22.01.14 2014-04 2014-01   
      4 29.01.14 2014-05 2014-01   
      5 05.02.14 2014-06 2014-02   
      6 12.02.14 2014-07 2014-02   
      7 19.02.14 2014-08 2014-02   
      8 26.02.14 2014-09 2014-02   
      9 05.03.14 2014-10 2014-03   
     10 12.03.14 2014-11 2014-03   
  10 Zeilen gewählt  

Extract DB as XML

I want to have a single query which is extracting the complete structure of the database into a simple XML.


For example having:

 create table my_test   
 (  
  f11 number,   
  f12 number,   
  f13 varchar2(300),   
  CONSTRAINT my_test_pk PRIMARY KEY (f11)  
 );  
 create table my_test_2   
 (  
  f21 number,   
  f22 number,   
  f23 varchar2(300),   
  CONSTRAINT my_test_pk_2 PRIMARY KEY (f21, f22),   
   CONSTRAINT fk_column_2  
   FOREIGN KEY (f22)  
   REFERENCES my_test (f11)  
 );  
 create table my_test_3  
 (  
  f31 number,   
  f32 number,   
  f33 varchar2(300),   
  CONSTRAINT my_test_pk_3 PRIMARY KEY (f31),   
   CONSTRAINT fk_column_3  
   FOREIGN KEY (f32)  
   REFERENCES my_test (f11)  
 );  
 comment on table my_test_2 is 'this is my test_2';  
 comment on table my_test is 'this is my test';  
 comment on column my_test.f11 is 'my_test.f11';  
 comment on column my_test.f12 is 'my_test.f12';  
 comment on column my_test.f13 is 'my_test.f13';  
 comment on column my_test_2.f21 is 'my_test.f21';  
 comment on column my_test_2.f22 is 'my_test.f22';  
 comment on column my_test_2.f23 is 'my_test.f23';   

I want to have as output:

 <?xml version="1.0" encoding="UTF-8"?>  
 <schema name="MARCOA">  
   <table name="MY_TEST" pk_column_name="F11" pk_name="MY_TEST_PK" pk_owner="MARCOA">  
    <comment>this is my test</comment>  
    <column name="F11" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f11</comment>  
      <referenced_by rk_ref_column_name="F22" rk_ref_table_name="MY_TEST_2" />  
      <referenced_by rk_ref_column_name="F32" rk_ref_table_name="MY_TEST_3" />  
    </column>  
    <column name="F12" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">  
      <comment>my_test.f12</comment>  
    </column>  
    <column name="F13" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment>my_test.f13</comment>  
    </column>  
   </table>  
   <table name="MY_TEST_2">  
    <comment>this is my test_2</comment>  
    <column name="F21" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f21</comment>  
    </column>  
    <column name="F22" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment>my_test.f22</comment>  
      <referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />  
    </column>  
    <column name="F23" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment>my_test.f23</comment>  
    </column>  
   </table>  
   <table name="MY_TEST_3">  
    <comment />  
    <column name="F31" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">  
      <comment />  
    </column>  
    <column name="F32" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">  
      <comment />  
      <referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />  
    </column>  
    <column name="F33" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">  
      <comment />  
    </column>  
   </table>  
 </schema>  

And here is the query to do it:

 with csv_constraint as (  
   SELECT distinct owner, constraint_name, table_name, SUBSTR (SYS_CONNECT_BY_PATH (column_name , ','), 2) csv  
      FROM (SELECT   
            acc.owner, acc.constraint_name, acc.table_name, acc.column_name,   
            acc.position rn,  
            COUNT (*) OVER (partition by cc.constraint_name) cnt  
         FROM   
            all_cons_columns acc left outer join all_constraints cc  
              on (acc.owner = cc.owner and acc.table_name = cc.table_name and cc.constraint_type = 'P')  
         where (cc.owner, cc.table_name) in (select t.owner, t.table_name from all_tables t)  
         /* to be changed */ and cc.owner = 'MARCOA'  
         )  
      WHERE rn = cnt  
   START WITH rn = 1  
   CONNECT BY constraint_name = prior constraint_name and rn = PRIOR rn + 1  
 ),   
 xml_builder as(  
  select 'STARTTAG' as tagtype, '_0000' as postfix, '<?>' as xml_tag from dual union all   
  select 'CLOSETAG' as tagtype, '_9999' as postfix, '</?>' as xml_tag from dual   
 ),   
 schemas as (  
  select   
   lpad(user_id, 4, '0') as hh_s_id,   
   username  
  from all_users   
  where username = user  
 ),   
 tab_cols_cons as (  
   select   
     tt.owner,   
     tt.table_name,   
     cl.owner pk_owner,   
     cl.constraint_name pk_name,   
     cl.csv pk_column_name,   
     lpad(ss.user_id, 4, '0') as hh_s_id,   
     lpad(t_id, 4, '0') as hh_t_id  
   from (  
    select ROW_NUMBER( ) OVER (PARTITION BY owner ORDER BY table_name NULLS LAST) t_id, t.*  
    from all_tables t  
   )tt join all_users ss   
              on (tt.owner = ss.username)  
             left outer join csv_constraint cl  
              on (cl.owner = tt.owner and tt.table_name = cl.table_name)  
   /* to be changed */ where tt.owner = 'MARCOA' and tt.table_name like 'MY_TEST%'  
 ),   
 tab_cols as (  
   select   
     atc.owner,   
     atc.table_name,   
     atc.column_name,   
     atc.nullable,   
     case   
      when atc.data_type = 'NUMBER' then atc.data_type || '(' || atc.data_length || ', ' || nvl(atc.data_scale, 0) || ')'  
      when atc.data_type = 'VARCHAR2' then atc.data_type || '(' || atc.data_length || ')'  
      else atc.data_type  
     end as col_type,  
     atc.num_distinct,  
     atc.num_nulls,   
     tcc.hh_s_id,   
     tcc.hh_t_id,   
     lpad(atc.column_id + 1, 4, '0') as hh_c_id  
   from tab_cols_cons tcc join all_tab_columns atc on (tcc.owner = atc.owner and atc.table_name = tcc.table_name)  
 ),   
 refs as (  
     select distinct all_constraints.owner fk_owner, -- all_constraints.constraint_name, all_constraints.constraint_type,  
         all_constraints.constraint_name fk_name,  
         all_cons_columns.table_name fk_table_name,  
         all_cons_columns.column_name fk_column_name,  
         all_constraints.r_owner fk_ref_owner,  
         -- all_constraints.r_constraint_name fk_ref_pk_name,  
         fk_ref_table_name,  
         fk_ref_column_name  
      from ALL_CONSTRAINTS  
      join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name  
      join (  
         select all_constraints.owner fk_owner,   
             all_constraints.constraint_name fk_name,   
             -- all_constraints.constraint_type,  
             all_cons_columns.table_name fk_ref_table_name,  
             all_cons_columns.column_name fk_ref_column_name  
          from ALL_CONSTRAINTS  
          join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name  
          where constraint_type in ('P')  
         ) on all_constraints.r_owner = fk_owner and all_constraints.r_constraint_name = fk_name  
      where ALL_CONSTRAINTS.owner = user  
       and constraint_type in ('R')  
 ),   
 referenced_list as (  
  select fk_owner, fk_table_name rk_ref_table_name, fk_column_name rk_ref_column_name,   
   hh_s_id,   
   hh_t_id,   
   hh_c_id,   
   ROW_NUMBER( ) OVER (PARTITION BY fk_owner, fk_table_name , fk_column_name ORDER BY fk_ref_owner, fk_ref_table_name, fk_ref_column_name NULLS LAST) rd_id  
  from tab_cols tc join refs r on (tc.owner = r.fk_ref_owner and tc.table_name = r.fk_ref_table_name and tc.column_name = r.fk_ref_column_name )  
 ),   
 referencing_list as (  
  select fk_ref_owner, fk_ref_table_name, fk_ref_column_name,  
   hh_s_id,   
   hh_t_id,   
   hh_c_id  
  from tab_cols tc join refs r on (tc.owner = r.fk_owner and tc.table_name = r.fk_table_name and tc.column_name = r.fk_column_name )  
 )  
 select xml   
 from (  
   select res_query, final_id, xml   
   from (  
     select   
      'q1' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then replace(xml_builder.xml_tag, '?', 'schema name="' || username || '"')   
         else replace(xml_builder.xml_tag, '?', 'schema')   
      end as xml,   
      hh_s_id || xml_builder.postfix as final_id  
     from schemas join xml_builder on (1=1)  
     union all   
     select   
      'q2' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then   
           case when pk_column_name is null   
            then replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '"')  
            else replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '" pk_owner = "' || pk_owner || '" pk_name = "' || pk_name || '" pk_column_name = "' || pk_column_name || '"')  
           end  
         else replace(xml_builder.xml_tag, '?', 'table')   
      end as xml,   
       hh_s_id || '_' || hh_t_id || xml_builder.postfix as final_id  
     from tab_cols_cons join xml_builder on (1=1)  
     union all   
     select   
       'q3' as res_query,   
       '<comment>' || comments || '</comment>' as xml,   
       hh_s_id || '_' || hh_t_id || '_0001' as final_id  
     from tab_cols_cons t join all_tab_comments atc on (t.owner = atc.owner and t.table_name = atc.table_name)  
     union all   
     select   
      'q4' as res_query,   
      case   
       when tagtype = 'STARTTAG'   
         then replace(xml_builder.xml_tag, '?', 'column name="' || column_name || '" nullable="' || nullable || '" type="' || col_type || '" num_distinct = "' || num_distinct || '" num_nulls ="' || num_nulls || '"' )   
         else replace(xml_builder.xml_tag, '?', 'column')   
      end as xml,   
      hh_s_id || '_' || hh_t_id || '_' || hh_c_id || xml_builder.postfix as final_id  
     from  tab_cols join xml_builder on (1=1)  
     union all   
     select   
       'q5' as res_query,   
       '<comment>' || comments || '</comment>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0001' as final_id  
     from tab_cols tc join all_col_comments acc on (tc.owner = acc.owner and tc.table_name = acc.table_name and tc.column_name = acc.column_name)  
     union all   
     select   
       'q6' as res_query,   
       '<referencing fk_ref_table_name="' || fk_ref_table_name || '" fk_ref_column_name="' || fk_ref_column_name || '"/>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0002' as final_id  
     from referencing_list  
     union all   
     select   
       'q7' as res_query,   
       '<referenced_by rk_ref_table_name="' || rk_ref_table_name || '" rk_ref_column_name="' || rk_ref_column_name || '"/>' as xml,   
       hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_' || lpad(rd_id+2, 4, '0') as final_id  
     from referenced_list  
   )  
   order by 2  
 );  

How to convert a select query into a CSV

Source:

http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle

Goal:

 create table countries ( country_name varchar2 (100));  
 insert into countries values ('Albania');  
 insert into countries values ('Andorra');  
 insert into countries values ('Antigua');  


 SELECT * from countries;  
 COUNTRY_NAME       
 ----------------------  
 Albania         
 Andorra         
 Antigua    
 Goal:   


 select * from ();   
 CSV  
 ----------------------  
 Albania, Andorra, Antigua  

Solution: 

 SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv  
    FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,  
           COUNT (*) OVER () cnt  
        FROM countries)  
    WHERE rn = cnt  
 START WITH rn = 1  
 CONNECT BY rn = PRIOR rn + 1;  
 CSV                                         
 --------------------------  
 Albania,Andorra,Antigua                               
 1 row selected.  

ORACLE: Analytical Functions

The concept of analytical query is something that can highly speed up the development and the execution of your queries.
In particular because they are automatically optimized by oracle itself.

Here there are reported in a veeeeery small nutshell:


Count (member of elements in the same group)

SELECT empno, deptno, 
COUNT(*) OVER (PARTITION BY 
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);

     EMPNO     DEPTNO DEPT_COUNT
---------- ---------- ----------
      7369         20          5
      7566         20          5
      7788         20          5
      7902         20          5
      7876         20          5
      7499         30          6
      7900         30          6
      7844         30          6
      7698         30          6
      7654         30          6
      7521         30          6

11 rows selected.

Row Number (id of the entry within the group)

SELECT empno, deptno, hiredate,
ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY hiredate NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO
------ ------- --------- ----------
  7782      10 09-JUN-81          1
  7839      10 17-NOV-81          2
  7934      10 23-JAN-82          3
  7369      20 17-DEC-80          1
  7566      20 02-APR-81          2
  7902      20 03-DEC-81          3
  7788      20 09-DEC-82          4
  7876      20 12-JAN-83          5

8 rows selected.

Rank & Dense Rank (member of elements in the same group)

SELECT empno, deptno, sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) RANK,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) DENSE_RANK
FROM emp
WHERE deptno IN (10, 20)
ORDER BY 2, RANK;

EMPNO  DEPTNO   SAL  RANK DENSE_RANK
------ ------- ----- ----- ----------
  7839      10  5000     1          1
  7782      10  2450     2          2
  7934      10  1300     3          3
  7788      20  3000     1          1
  7902      20  3000     1          1
  7566      20  2975     3          2
  7876      20  1100     4          3
  7369      20   800     5          4

8 rows selected.

Lead & Lag (next / previous member of the group respect the current element)

SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;

 DEPTNO  EMPNO   SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
     10   7839  5000           2450               0
     10   7782  2450           1300            5000
     10   7934  1300              0            2450
     20   7788  3000           3000               0
     20   7902  3000           2975            3000
     20   7566  2975           1100            3000
     20   7876  1100            800            2975
     20   7369   800              0            1100

8 rows selected.

First Value & Last Value

-- How many days after the first hire of each department were the next
-- employees hired?

SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;

     EMPNO     DEPTNO    DAY_GAP
---------- ---------- ----------
      7369         20          0
      7566         20        106
      7902         20        351
      7788         20        722
      7876         20        756
      7499         30          0
      7521         30          2
      7698         30         70
      7844         30        200
      7654         30        220
      7900         30        286

11 rows selected.

Source:
http://www.orafaq.com/node/55

File system access on Oracle

It may sound easy, but accessing the file system from oracle can be painful.
I am not talking about read / write a file. I am talking about making a ls or dir command, crete folders, move files, etc.
In this post I would like to recall an easy system about making ls.

Actually the solution is already very well explained in this web page:
http://plsqlexecoscomm.sourceforge.net/


The solution is mainly based on a java package installed in the Oracle DB, which is accessing the file system and arranging the data in a proper way.

First of all it is needed to install the package (available on the link above) and then perform a simple query like the one below:

select * 
from table(
    file_pkg.get_file_list(file_pkg.get_file('/'))
)

And here you are: you get the result of a ls command executed on the root accessible as a simple select.

Oracle: log DML errors

When you perform a massive insert or update generating errors, it is very hard to recognize which are the records which are generating the errors.

The topic in this post is about performing a DML statement and storing the wrong error in an additional table rather than crashing the statement itself.


Suppose you run:

INSERT INTO dest
SELECT *
FROM   source;

SELECT *
       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("TEST"."DEST"."CODE")


SQL>

But you would like:

INSERT INTO dest
SELECT *
FROM   source
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

99998 rows created.

SQL>

How to do this?
First build the "Error table", which is a clone of your destination table, having no format for columns (every column is varchar).

LOG ERRORS [INTO [schema.]table] [('simple_expression')] [REJECT LIMIT integer|UNLIMITED]

Example:

-- Create the error logging table.
BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
END;
/

PL/SQL procedure successfully completed.

SQL>

Which generate:

SQL> DESC err$_dest
 Name                              Null?    Type
 --------------------------------- -------- --------------
 ORA_ERR_NUMBER$                            NUMBER
 ORA_ERR_MESG$                              VARCHAR2(2000)
 ORA_ERR_ROWID$                             ROWID
 ORA_ERR_OPTYP$                             VARCHAR2(2)
 ORA_ERR_TAG$                               VARCHAR2(2000)
 ID                                         VARCHAR2(4000)
 CODE                                       VARCHAR2(4000)
 DESCRIPTION                                VARCHAR2(4000)

SQL>

At this point you can invoke the insert in the way above.
Update and merge and code for a complete example are in the link below (source of this post).


Source:
http://www.oracle-base.com/articles/10g/dml-error-logging-10gr2.php

Kill session in Sap Hana

In Oracle an easy trick to kill a session is to run the following query:

 SELECT 'exec sys.kill_session(' || a.sid || ',' || a.serial# || ');'  
    ,osuser  
    ,terminal  
    ,machine  
    ,program  
    ,a.*  
  FROM v$session a  
 WHERE status = 'ACTIVE' AND username = USER;  

Of course you have to be careful on the way you use it... You should be sure about what is running in the correspondent session.
You can use for example the tool offered by SQL Developer (you need to have the rights on the table v$session).

What about SAP HANA? The basic command you need is:

   ALTER SYSTEM CANCEL SESSION 'conn_id';  

Where conn_id is in the table M_CONNECTIONS, column CONNECTION_ID.
You can retrieve the complete command with the following query:

 SELECT   
    'alter system cancel session ''' || C.CONNECTION_ID || ''';' command,   
    C.CONNECTION_ID,   
    PS.STATEMENT_STRING  
 FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS  
     ON C.CONNECTION_ID = PS.CONNECTION_ID   
     AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID  
 WHERE C.CONNECTION_STATUS = 'RUNNING'   
  AND C.CONNECTION_TYPE = 'Remote';  

    

Oracle: easy access the file system from PLSQL

How to easily access the file system from PLSQL ?
The goal is to perform an simple command like:
- DOS: dir
- LINUX: ls

and get the result as select.

Very good tool is offered by:
http://plsqlexecoscomm.sourceforge.net/


You need to download the PLSQL package from the web site above, install the PLSQL script and then perform the simple select:

 select *   
 from table(file_pkg.get_file_list(file_pkg.get_file('/')))  

Oracle: Measure disk space used

You need to know how much space your schema is using:

 -- estimated bytes per table used:  
 select   
   table_name,   
   avg_row_len*num_rows/1024/1024 size_mbytes   
 from user_tables   
 order by 2 desc; 

 --top table-segments for bytes used:  
 select   
      owner,   
      segment_name,   
      partition_name,   
      segment_type,   
      round(bytes/1024/1024) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 order by bytes desc;  

 --top table-segments for bytes used: (per Segment_name)  
 select   
      owner,   
      segment_name,   
      sum(size_MB) size_MB   
 from (  
  select   
       owner,   
       segment_name,   
       partition_name,   
       segment_type,   
       round(bytes/1024/1024) size_MB   
  from   
       dba_segments   
 )  
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner, segment_name  
 order by size_MB desc;

 -- top owner for bytes used:  
 select   
      owner,   
      round(sum(bytes/1024/1024)) size_MB   
 from dba_segments   
 where owner like '%MY_FAVOURITE_NAME%'  
 group by owner   
 order by 2 desc;   

-- disk free  
 select   
      tablespace_name,   
      round(sum(bytes)/(1024*1024*1024),0) GB_free   
 from dba_free_space   
 group by tablespace_name   
 order by 2 desc;  

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.

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;  

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;  

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;  
 /  

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

Oracle PLSQL Dynamic Loop

Dynamic Loop

 

 

 

 Declare  
   v_sql      VARCHAR2(32000);  
   TYPE t_refcrs IS REF CURSOR;  
   c_exttable t_refcrs;  
   v_my_field varchar2(320000 char);  
  BEGIN  
      v_sql := 'select my_field from my_table';  
      v_count := 0;  
      OPEN c_exttable FOR v_sql;  
       LOOP  
        FETCH c_exttable INTO v_v_my_field;  
        EXIT WHEN C_EXTTABLE%NOTFOUND;  
           --My operation  
       END LOOP;  
 End;  

Kill sessions in Oracle

How to kill a running session in Oracle?

 SELECT 'exec sys.kill_session(' || sesion.sid || ',' || sesion.serial# || ');', sql_text  
 from v$sqltext sqltext, v$session sesion  
 where sesion.sql_hash_value = sqltext.hash_value  
 and sesion.sql_address = sqltext.address  
 and sesion.username is not null  
 order by sqltext.piece;  

Oracle: Multiple insert conditiona​l

The goal is to insert multiple lines into several lines and track the possible errors as well...

 insert all   
 when 1=1  
 then into zzz_tab_output_1 (OF1, OF2, OFK) values (IF1, IF2, IF3)  
 LOG ERRORS INTO err$_zzz_tab_output_1 (1) REJECT LIMIT UNLIMITED  
 when exists (select 1 from ZZZ_Tab_INPUT_1 where ifk in (select ofk from zzz_tab_output_1))  
 then into zzz_tab_output_2 (OF1, OF3, OFK) values (IF1, IF3, IFK)  
 LOG ERRORS INTO err$_zzz_tab_output_2 (2) REJECT LIMIT UNLIMITED  
 Select i.IF1, j.IF2, j.IF3, IFK from ZZZ_Tab_INPUT_1 i join ZZZ_Tab_INPUT_2 j using (IFK);