0
select table_name,count(column_name) from all_tab_columns where owner ='HR'

This query retrieves the results, but I want the same calculation of the number of columns in a table using a PL/SQL procedure and that output should go into a CSV file.

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
user1620464
  • 39
  • 1
  • 2
  • 11

1 Answers1

0

Create CSV version 1 (not using PL/SQL but still creates a CSV file) with help from here

set colsep ,     
set pagesize 0   
set trimspool on 
set headsep off  
set linesize 100   
set numw 10      

spool myfile.csv

select table_name,count(column_name) as cnt 
from all_tab_columns where owner ='HR' 
group by table_name);

Version 2 with a bit PL/SQL

set serveroutput on
begin
  for  c in (select table_name,count(column_name) as cnt 
             from all_tab_columns where owner ='HR' group by table_name)  
  loop
    dbms_output.put_line(c.table_name||';'||c.cnt);
  end loop;
end;
/

Version 3 would be using the utl_file package if you want the file server side but I was now assuming you run it in SQLplus or SQLdeveloper.

Community
  • 1
  • 1
hol
  • 8,255
  • 5
  • 33
  • 59
  • HEY THANK U..ITS VERY SIMPLE.... BUT YET GREAT MIND TO THINK..!! GOOD AND I HAVE RETREIVED THE OUTPUT USING UTL_FILE.TQ!! HOL – user1620464 Aug 23 '12 at 17:51
  • but willl i have any problem if columns are added to a table and are not analyzed??? – user1620464 Aug 23 '12 at 17:55
  • What do you mean with not analyzed? – hol Aug 23 '12 at 18:04
  • when table stats are gathered then only the updated or added column will be recorded in the all_tab_columns ryt?? – user1620464 Aug 23 '12 at 18:10
  • Oh that is a different questions of what is in the all_tab_columns view and has nothing to do with the original question. – hol Aug 23 '12 at 19:04
  • But you know what. You misread that. When you google that table there is a entry from oracle where they talk about stats. That has nothing to do with the content of the view. ALL_TAB_COLUMNS is updated whenever a table changes. By the way: http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – hol Aug 23 '12 at 19:14
  • @hol....okk so no need of worrying on stats analyzing..okkk Hey The code i am using it with utl_file.. its working fine.. but when i am combining with another proc...its getting errors??? – user1620464 Aug 24 '12 at 17:23
  • in brief... select owner,table_name,sum(column_name) from all_tab_columns where data_type in number; ----- first proc (here i used curosr) and .............. next "ur proc" --- but its giving error saying: " table_name must be defined" ... how to resolve it?? – user1620464 Aug 24 '12 at 17:30
  • can u give the proc by uing a cursor?? am unable to get that – user1620464 Aug 25 '12 at 09:30
  • can u give me ur email id to post it correctly..here i cant post it..not enough space or unable to post it for some reason.?? – user1620464 Aug 25 '12 at 14:08