SQL>
SQL>
SQL> select text from user_source s, user_objects o
2 where s.name = o.object_name and o.object_type in ('PROCEDURE' , 'FUNCTION')
3 and rownum < 50
4 ORDER BY name, line
5 /
TEXT
--------------------------------------------------------------------------------
procedure delete_cust
(p_Cust_no in number)
as
l_count number;
begin
select count(*) into l_count
from ord
where cust_no = p_cust_no;
if l_count != 0 then
TEXT
--------------------------------------------------------------------------------
raise_application_error(-20000, 'cannot delete active cust');
end if;
end;
PROCEDURE drop_if_exists(aiv_object_type in varchar2,aiv_object_name in varchar2
) is
cursor c_constraint(aiv_table_name in varchar2) is
select f.table_name,
f.constraint_name
from SYS.USER_CONSTRAINTS f,
SYS.USER_CONSTRAINTS p
TEXT
--------------------------------------------------------------------------------
where f.constraint_type = 'R'
and f.r_owner = p.owner
and f.r_constraint_name = p.constraint_name
and p.table_name = aiv_table_name;
n_count number;
v_sql varchar2(100);
begin
select count(1)
into n_count
from SYS.USER_OBJECTS
where object_type = upper(aiv_object_type)
TEXT
--------------------------------------------------------------------------------
and object_name = upper(aiv_object_name);
if n_count > 0 then
if upper(aiv_object_type) = 'TABLE' then
for r_constraint in c_constraint(upper(aiv_object_name)) loop
v_sql := 'alter table '||
r_constraint.table_name||
' drop constraint '||
r_constraint.constraint_name;
begin
execute immediate v_sql;
exception
TEXT
--------------------------------------------------------------------------------
when OTHERS then
dbms_output.put_line(SQLERRM||': '||v_sql);
end;
end loop;
end if;
v_sql := 'drop '||aiv_object_type||' '||aiv_object_name;
begin
49 rows selected.
SQL>