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
