PL/SQL function statement ignored error

Question

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!

You get an error on execution of the function, or when you compile it? — Alex Poole
– Alex Poole, Commented Apr 7, 2016 at 13:25

Vishal5364 · Accepted Answer · 2016-04-07 12:58:56Z

1

Your user does not have the priviliges to access DBA_OBJECTS

answered Apr 7, 2016 at 12:58

Vishal5364

2931 gold badge6 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erich Kitzmueller · Accepted Answer · 2016-04-07 13:15:14Z

1

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

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

answered Apr 7, 2016 at 13:15

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

1 Comment

Alex Poole Over a year ago

There's also an extra space, but the error line numbers indicate it's a compile time error - failing on the cursor select - not at runtime on execution of the dynamic SQL. Including the quotes is a good idea anyway, of course.

Collectives™ on Stack Overflow

PL/SQL function statement ignored error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related