CODEWITHSUNDEEP
oracle-databasefunction
sameh
sameh

Reputation: 127

PL/SQL function statement ignored error

i want to create funtion that truncate all user objects, this is my script

CREATE OR REPLACE Function Truncate_user ( name_in IN varchar2 )
return number 
is
cnumber number;
v_str1 varchar2(200) := null;
cursor get_sql is
select
'drop '||object_type||' '||owner||'. '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS PURGE') v_str1
from DBA_objects
where object_type in ('TABLE','VIEW','PACKAGE','TYPE','PROCEDURE','FUNCTION','TRIGGER','SEQUENCE','SYNONYM') 
AND owner=name_in
order by object_type,object_name;
begin
open get_sql;
loop
fetch get_sql into v_str1;
if get_sql%notfound
then cnumber :=0; 
end if;

execute immediate v_str1;
end loop;
RETURN 1;
close get_sql;
end;
/

after the execution i got these errors

Erreur(7,1): PL/SQL: SQL Statement ignored
Erreur(9,6): PL/SQL: ORA-00942: Table ou vue inexistante

but when i execute this code without make a function ,the operation is done!

Upvotes: 0

Views: 361

Answers (2)

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

Maybe there are objects that requires quoted identifiers (e.g. using lowercase letters), try

select 'drop '||object_type||' '||owner||'. "'|| object_name||'"'|| ...

Upvotes: 1

Vishal5364
Vishal5364

Reputation: 293

Your user does not have the priviliges to access DBA_OBJECTS

Upvotes: 1

Related Questions