Columns to rows (print_record)

In my blog (Compile blocked package/procedure/function) I used procedure tools.print_record which I use a lot of cases when I need nice formatted output of record with more columns without any manual formatting. Idea is to transform columns to rows and keep easiness of use.

The problem

If you need to see (some restore point) state of v$database data, then you probably might use query like:

select * from v$database;

Problem is bad readability of such an output:

And here is where print_record comes at it's best.

This is v1.1 thanks to Marko Sutic and his comments with url to original postings. In fact this code come originally from Tom's Kyte page - print_table procedure. Mine part is small adding at the begginig to store session parameters.

I was not aware of that before so now I do appologize for this to all authors ...

Implementation

Implementation is more then easy-install the procedure. In my case it is part of tools package but it can be standalone as well:

procedure print_record ( p_query in varchar2 )
is
/* 
   these two constants manage output formatting for strings.
   adjust them as you wish.
*/
  c_text_seg_len       CONSTANT NUMBER        := 80;   -- do not exceed 224
  c_cont_char          CONSTANT VARCHAR2(30)  := '~';  -- for continuation

  cur_da_cursor                 INTEGER DEFAULT dbms_sql.open_cursor;
  l_column_value                VARCHAR2(4000);
  l_column_title                VARCHAR(30);
  l_status                      INTEGER;
  l_table_desc                  dbms_sql.desc_tab;
  l_column_count                NUMBER;
  l_nls_date                    VARCHAR2(32);
begin
  select value into l_nls_date
    from nls_session_parameters
   where parameter = 'nls_date_format';

  execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

  dbms_sql.parse(cur_da_cursor,p_query,dbms_sql.native);

  dbms_sql.describe_columns (cur_da_cursor,l_column_count,l_table_desc);

  for i in 1 .. l_column_count loop
    dbms_sql.define_column (cur_da_cursor,i,l_column_value,4000);
  end loop;

  l_status := dbms_sql.execute(cur_da_cursor);

  while (dbms_sql.fetch_rows(cur_da_cursor) > 0) loop
    for i in 1 .. l_column_count loop
      dbms_sql.column_value (cur_da_cursor,i,l_column_value);
      if l_table_desc(i).col_type = 1 then
        if length(l_column_value) > 0 then
          l_column_title    := rpad(l_table_desc(i).col_name, 30);
          for j in 1 .. ceil(length(l_column_value)/c_text_seg_len) loop
            dbms_output.put_line(l_column_title || ': ' || 
                                 substr(l_column_value, (((j-1)*c_text_seg_len)+1), c_text_seg_len)
                                );
            l_column_title := rpad(c_cont_char, 30);
          end loop;
        else
          dbms_output.put_line (rpad( l_table_desc(i).col_name, 30) || ': ' || l_column_value);
        end if;
      elsif l_table_desc(i).col_type in (8, 23, 24, 112, 113, 114, 115) then
        dbms_output.put_line (rpad(l_table_desc(i).col_name, 30 ) || ': ** UNPRINTABLE **');
      else
        dbms_output.put_line (rpad( l_table_desc(i).col_name, 30) || ': ' || l_column_value );
      end if;
    end loop;
  dbms_output.put_line( '--------------------------------------------------------------------' );
  end loop;
  execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
exception
  when others then
    begin
      dbms_output.put_line(sqlerrm);
      dbms_output.put_line (chr(9));
      dbms_output.put_line (p_query);
    exception
      when others then
        null;
    end;    
    execute immediate 'alter session set nls_date_format='''||l_nls_date||'''';
end print_record;

"print_record" in action

If you run the same query as before "through" print_records:

exec tools.print_record('select * from v$database');

Result looks like:

And here is another enchantment for columns that are longer then 255 chars. Output is devided into several columns where length is defined with c_text_seg_len constant.

For the end

As you can see the main idea to keep easiness is full filled-you can use the same query in both cases!

Remember that every record will produce one set of rows....regardless how many they are in result.

Cheers!