Hi Oracle Gurus

This might be very silly problem for you gyus, but I
am really geting headache. I suppose today my mind is
not working in sync.

I am attatching the script which i wrote. When I try
to execute on sql promp, I get the result like this :


SQL> @c:\general\ksg\ProcDisp.sql
SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.
|==============================================================================|
|Proc Name                     Proc Type              
                        |
|   Arguments Name                 Pos Data Type      
               IN-OUT   |
|==============================================================================|
|ADDCHARGETYPE                 PROCEDURE              
                        |
|                                                     
                        |
|   TVCCHARGETYPE                  1    VARCHAR2      
                IN      |
|   TVCGLNUMBER                    2    VARCHAR2      
                IN      |



I would appreciate If you help me getting rid of
following messages :

SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.
SP2-0103: Nothing in SQL buffer to run.

I want comments to be /*   */ like only
TIA
Krishan Swarup Gupta


__________________________________________________
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com
/********************************************************************************/
/*Auther        : Krishan S Gupta                                               */
/*Date          : 02-05-2002                                                    */
/*Des           : Procedure Descriptions                                        */
/*Oracle Ver    : Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production    */     
 
/********************************************************************************/

set serveroutput on size 100000

spool c:\general\ksg\ProcDisp.txt

declare

print_line      Varchar2(100);
param_count     number:=0;
calls_count     number:=0;  
called_count    number:=0;

cursor obj_cur (powner in all_arguments.owner%type,
                pobject_name in all_arguments.object_name%type) 
        is
        select distinct owner, object_name, 
                package_name, argument_name, 
                position, sequence, data_type, in_out
        from all_arguments 
        where owner = powner
        and   object_name = pobject_name
        order by owner, object_name, position;


begin
     
dbms_output.put_line('|==============================================================================|');
     dbms_output.put_line('|Proc Name                     Proc Type                    
                   |');
     dbms_output.put_line('|   Arguments Name                 Pos Data Type            
          IN-OUT   |');
     
dbms_output.put_line('|==============================================================================|');

     for r_cur_obj in (select distinct owner,name,type 
                       from all_source 
                       where owner = 'TC' and type in ('PROCEDURE','FUNCTION'))
     loop

             print_line := '|' || rpad(r_cur_obj.name,30,' ') || 
rpad(r_cur_obj.type,12,' ') || '                                    |';

             dbms_output.put_line(print_line);

             dbms_output.put_line('|                                                   
                           |');

             for r_cur_param in obj_cur(r_cur_obj.owner, r_cur_obj.name)
             loop

                       print_line := '|   ';

                       if (r_cur_obj.type = 'FUNCTION' and r_cur_param.POSITION = 0) 
THEN

                       print_line := print_line || rpad('Return Type',35,' ')          
       || ' ' ||  
                                                   rpad(r_cur_param.DATA_TYPE,30,' ')  
       || ' ' ||  
                                                   rpad(r_cur_param.IN_OUT,8,' ')      
       || '|' ;

                       else

                       print_line := print_line || rpad(r_cur_param.ARGUMENT_NAME,30,' 
')     || ' ' || 
                                                   
rpad(to_char(r_cur_param.POSITION),4,' ')  || ' ' ||  
                                                   rpad(r_cur_param.DATA_TYPE,30,' ')  
       || ' ' ||  
                                                   rpad(r_cur_param.IN_OUT,8,' ')      
       || '|' ;

                        end if;

                        dbms_output.put_line(print_line);

             end loop;

             dbms_output.put_line('|                                                   
                           |');

             /* procedure or functions it calls */

             calls_count := 0;

             for r_dep in (select * from all_dependencies 
                           where owner = r_cur_obj.owner and name = r_cur_obj.name
                           AND   REFERENCED_OWNER = r_cur_obj.owner 
                           AND REFERENCED_TYPE IN ('PROCEDURE','FUNCTION'))

              loop

                    calls_count := calls_count + 1;

                    if calls_count = 1 then
                            print_line := '|     Calls :--';
                    else
                            print_line := '|            --';
                    end if;

                    print_line := print_line || rpad(r_dep.referenced_name,30,' ') || 
rpad(r_dep.referenced_type,10,' ');

                    dbms_output.put_line(print_line || '                        |');   
                    
              end loop;

              dbms_output.put_line('|                                                  
                            |');

              called_count:=0;

              for r_called in (select * from all_dependencies 
                           where owner = r_cur_obj.owner 
                           and   referenced_owner = r_cur_obj.owner 
                           and   referenced_name  = r_cur_obj.name
                           and   type in ('PROCEDURE','FUNCTION'))

              loop

                    called_count := called_count + 1;

                    if called_count = 1 then
                            print_line := '| Called by :--';
                    else
                            print_line := '|            --';
                    end if;

                    print_line := print_line || rpad(r_called.name,30,' ') || 
rpad(r_called.type,10,' ');

                    dbms_output.put_line(print_line || '                        |');   
                    
              end loop;

              dbms_output.put_line('|                                                  
                            |');
        
     end loop;
              
dbms_output.put_line('|==============================================================================|');

end;
/

spool off

clear buffer

Reply via email to