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.  

Overload

My classic approach!
You get stuck in your dozens daily problems... You do not know how to get out of it...
STOP!
This is the exact meaning of "Thinking outside the box": try to see your problem from a different angle. How can you? do not hold your problems strict in your hands... try to now look at them and come back later... The angle will be for sure different!

What is EGO?

Ajax call for Java Spring

I found a great tutorial on how to invoke an ajax call on java spring.
Source: http://crunchify.com/how-to-use-ajax-jquery-in-spring-web-mvc-jsp-example/

It follows the code (of the website reported).
This code is going to invoke an ajax call every x seconds.

Code for the controller:

 package com.crunchify.controller;  
 import java.util.Date;  
 import org.springframework.stereotype.Controller;  
 import org.springframework.web.bind.annotation.RequestMapping;  
 import org.springframework.web.bind.annotation.RequestMethod;  
 import org.springframework.web.bind.annotation.ResponseBody;  
 import org.springframework.web.servlet.ModelAndView;  
 import java.util.Random;  
 /**  
  * @author Crunchify.com  
  *   
  */  
 @Controller  
 public class CrunchifySpringAjaxJQuery {  
   @RequestMapping("/ajax")  
   public ModelAndView helloAjaxTest() {  
     return new ModelAndView("ajax", "message", "Crunchify Spring MVC with Ajax and JQuery Demo..");  
   }  
   @RequestMapping(value = "/ajaxtest", method = RequestMethod.GET)  
   public @ResponseBody  
   String getTime() {  
     Random rand = new Random();  
     float r = rand.nextFloat() * 100;  
     String result = "<br>Next Random # is <b>" + r + "</b>. Generated on <b>" + new Date().toString() + "</b>";  
     System.out.println("Debug Message from CrunchifySpringAjaxJQuery Controller.." + new Date().toString());  
     return result;  
   }  
 }  

and here the code for the View:

 <html>  
 <head>  
 <TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>  
 <style type="text/css">  
 body {  
   background-image:  
     url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');  
 }  
 </style>  
 <script type="text/javascript"  
   src="http://code.jquery.com/jquery-1.10.1.min.js"></script>  
 <script type="text/javascript">  
   function crunchifyAjax() {  
     $.ajax({  
       url : 'ajaxtest.html',  
       success : function(data) {  
         $('#result').html(data);  
       }  
     });  
   }  
 </script>  
 <script type="text/javascript">  
   var intervalId = 0;  
   intervalId = setInterval(crunchifyAjax, 3000);  
 </script>  
 </head>  
 <body>  
   <div align="center">  
     <br> <br> ${message} <br> <br>  
     <div id="result"></div>  
     <br>  
     <p>  
       by <a href="http://crunchify.com">Crunchify.com</a>  
     </p>  
   </div>  
 </body>  
 </html>  

I had to use a slight variation.
I wanted to have the ajax call only on click of a button.
The controller remains the same. What needs to be changed is the view:

 <html>  
 <head>  
 <TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>  
 <style type="text/css">  
 body {  
   background-image:  
     url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');  
 }  
 </style>  
 <script type="text/javascript"  
   src="http://code.jquery.com/jquery-1.10.1.min.js"></script>  
 <script type="text/javascript">  
   function crunchifyAjax() {  
     $.ajax({  
       url : 'ajaxtest.html',  
       success : function(data) {  
         $('#result').html(data);  
       }  
     });  
   }  
      $( "#button_refresh" ).click(function() {  
           crunchifyAjax();  
      });  
 </script>  
 </head>  
 <body>  
   <div align="center">  
     <br> <br> ${message} <br> <br>  
     <button id="button_refresh">Refresh</button>  
     <div id="result"></div>  
     <br>  
     <p>  
       by <a href="http://crunchify.com">Crunchify.com</a>  
     </p>  
   </div>  
 </body>  
 </html>  

Top 10 Stupid code

In my job, I have the opportunity of watching how people think.
I believe that a piece of code is a great demonstration about the cognitive skills of a person.

The question of the picture above says a lot... BeastUK "problem solving" skills are quite limited.

During my profession, I have to instruct developers about what is needed to do and review also their code.
Here is a short collection about the most stupid piece of code I have ever seen (believe me, it is real!).

Of course, this code has been re-adapted and made anonymous.
Anyway, I wanted to add some information about the origin of the genius, therefore you'll find below the flag of the creator's homecountry.

Some of these sniplets come from my former colleagues, freelancers, certified experts, colleagues from my customers, developers with several years of experience.



Top. 10

Ok, let's say that was a too fast copy and paste...

 […]  
     person.setFirstName(salutation);  
     person.setLastName(firstName);  
     person.setSalutation(lastName);  
 […]  

Top. 9

What about one loop and inside 5 if ?

 for i in my_array loop  
      if (my_array(i) == 1) then  
           -- do something  
      end if;  
 end loop;  
 for i in my_array loop  
      if (my_array(i) == 2) then  
           -- do something  
      end if;  
 end loop;  
 for i in my_array loop  
      if (my_array(i) == 3) then  
           -- do something  
      end if;  
 end loop;  
 for i in my_array loop  
      if (my_array(i) == 4) then  
           -- do something  
      end if;  
 end loop;  
 for i in my_array loop  
      if (my_array(i) == 5) then  
           -- do something  
      end if;  
 end loop;  

Top.  8

Unfortunately I did not have the honor to work with this genious.

I had to adapt the code in order to obfuscate confidential content of course, but the result was the same...

Given the following table:

 create table myTable (
 a numeric, 
 b numeric, 
 c numeric 
); 

We found in the code the following select:

select a 
from mytable
where b = (
 select b 
 from mytable 
 where c = param
)




Difficult to understand? Why do you need a sub query? why not to put it directly in the where condition?
Since it is exactly the same result, try to read it in this form:

select a
from mytable 
where c = param

Top.  7

Some confusion with the group by...

Some genious of previous masterpiece. I also in this case I have obfuscated the code.

Given the same table of before: 

create table myTable (
 a numeric, 
 b numeric, 
 c numeric 
); 

We found in the code the following select:

select a, b, sum(c) 
from   (
 select a, b, sum(c) as c 
 from mytable 
 group by a, b
)
group by a, b

The second group by (the external, the surrounding one) is totally useless. It makes the sum of a single record... Let's see a simplified version...

select a, b, sum(c) as c 
from mytable 
group by a, b

Top.  6

This one left me totally attonished!!!

Found in the code:

select max(id) 
into lmaxid 
from log_messages;

delete from log_messages 
where id <= lmaxid; 




Why should I select from the max and delete everything lower than max… therefore I delete everything?!?!

there is nothing behind max… 

delete from log_messages;

Top.  5

HOW THE HELL A SELECT COUNT CAN RETURN NO RECORD!!!!

In any case it returns one line with a number, mostly it will be 0!!!!

Found in the code: 

DECLARE
  v_cnt numeric;
  param numeric;
BEGIN
  BEGIN
    SELECT COUNT(1)
    INTO v_cnt
    FROM my_table
    WHERE field_1 = param;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
     //do something else
  END;
END;

Top.  4

What does it do the NVL function in Oracle? 

Let's say:  

NVL(a,b)

- If a is not null then it returns the value of a. 

- If the a is a NULL value then it returns the value of b. 

Tipically used in select statement in order to overwrite possible NULL values coming from the table. 

Given the same table of before: 

create table myTable (
 a numeric, 
 b numeric, 
 c numeric 
); 







Found in the code:

select
   nvl('a', a), 
   nvl('b', b), 
   nvl('c', c)
from dual;

How can it be possible that a constant value (like 'a') can be NULL?
Is it now more clear?

select 
   'a', 'b', 'c'
from dual;

Top.  3

How to make your life more complicated...
Look at this piece of code and try to understand what is doing.

 select   
      case when count(*) > 0   
           then count(*)   
           else 0   
      end   
 from table   

I am kind of sure you still do not get it... Try to see the version below, it is absolutely equivalent:

 select   
      count(*)  
 from table   

Top. 2 (Log,  part 1)

Topic: Storing queries in the log
Instructions about task:

Me: Hi Dev, I saw that you are using static code. Actually for this project has been decided to use dynamic code. In particular we need to store the EXACT query that has been executed! Including the parameters used in queries.
Therefore we make the query dynamic into a variable, save the variable in the log table and then execute the variable. 
Dev: Ok, all clear, now make sense! So store the query in the log!

Expectation:

 procedure myProcedure() begin  
      [...]  
      v_sql := 'insert into myTable   
                     select * from mySource  
                     where parameter = ' || var;  
      insert_in_log(v_sql);  
      execute v_sql;  
      [...]  
 end;  

Here is what I got:

 procedure myProcedure() begin  
      [...]  
      insert into myTable   
      select * from mySource  
      where parameter = var;  
      
      v_sql := 'insert into myTable   
                     select * from mySource  
                     where parameter = <<parameter>>';  
      
      insert_in_log(v_sql);  
      [...]  
 end;  

Me: Dev.... you are still using static code
Dev: Yes, but the query is in the log!
Me: But not EXACTLY what has been executed... where is the parameter? 
Dev: here look, where it is written <<parameter>>

Top. 1 (Log,  part 2)

Topic: Severity in the log
Instructions about task:

Me: Hi Dev, as you know we are using the severity of the log entries. We noticed that your code is writing too many entries at the same level. It does not matter if we use a log level as "debug" or as "production", it writes anyway too many entries. Can you reduce the number of entries in the log? Just assign properlyy the log severity.
Dev: Ok, all clear, reduce the number of entries in the log

Expectation (watch out the severity):

 procedure myProcedure() begin  
      [...]  
      query 1
      insert_in_log(query, high_severity);

      query 2
      insert_in_log(query, medium_severity);

      query 3
      insert_in_log(query, high_severity);

      query 4
      insert_in_log(query, low_severity);

 end;  

Here is what I got (watch out the severity and the final delete):

 procedure myProcedure() begin  
      [...]  
      query 1
      insert_in_log(query, high_severity);

      query 2
      insert_in_log(query, high_severity);

      query 3
      insert_in_log(query, high_severity);

      query 4
      insert_in_log(query, high_severity);

      delete * from log;
      [...]  
 end;  

Me: Dev.... ehm... why do you delete the entries from the log?
Dev: Com'on... you told me you wanted less entries in the log!

Theory of Stupidy

The pleasure of working with smart colleagues is wonderful.
Nothing pays more (professionally speaking) than the moment in which you manage to perform a good job, feeling a great synchrony with a smart colleague.
The moment in which you notice that it is enough to exchange two sentences in order to comunicate a very complicated concept and the security of being understood.

The possibility of meeting a smart colleague who enrich you can be very hard.

On the contrary, the possibility of meeting a dumb colleague may be very high! The colleague making his own life harder without any (logical) reason, the colleague who generates that piece of code that you will admire for ever.

We always have to deal with stupidity, in our every day life.
Is there any tool which can help us? Well, some years ago I read an inspiring book!

"Allegro ma non troppo" by Carlo M. Cipolla.

The book is about a scientific analysis of the human stupidity. It is proposed an illuminating mathematical model and 5 theorems.

If anybody out there is reading this blog, you know that this blog is meant to be short! I am therefore proposing here just the laws and the model. If you will be touched by such illumination, then I suggest you to read the book.



For the pictures I thank this website:
http://nicholasbordas.com/archives_posts/what-if-we-didnt-underestimate-stupidity