Strange "Truncate table" behavior in PL/SQL

Oracle 8.1.7 on windows.

Look at this:

spool truncate_test.log

conn system/****@conn

drop role user02_role;

drop user user00 cascade;
drop user user01 cascade;
drop user user02 cascade;

create role user02_role;

grant
create session,
create procedure,
create public synonym,
drop public synonym,
create table
to user00 identified by a;

grant
create session,
create procedure,
create public synonym,
drop public synonym
to user01 identified by a;

grant
create session
to user02 identified by a;

grant
user02_role
to user02;

alter user user00
default tablespace users
quota 1m on users;

conn user00/a_at_conn

create table t (c int);

create package p00 as
procedure tt (tn in char);
end p00;
/

create package body p00 as
procedure tt (tn in char)
is
begin
execute immediate ('truncate table ' || tn); end tt;
end p00;
/

drop public synonym t;
drop public synonym p00;

create public synonym t for user00.t;
create public synonym p00 for user00.p00;

grant execute on p00 to user01, user02_role; grant insert, select on t to user01, user02_role;

conn user01/a_at_conn

create package p01
authid current_user
as
procedure main;
end p01;
/

create package body p01
as
procedure main
is
begin
p00.tt('t');
insert into t values (0);
end main;
end p01;
/

drop public synonym p01;

create public synonym p01 for user01.p01;

grant execute on p01 to user02_role;

conn user02/a_at_conn

exec p01.main
exec p01.main
exec p01.main
exec p01.main

select * from t;

conn user02/a_at_conn

exec p01.main

select * from t;

spool off

exit

truncate_test.log is:

Connected.

Role dropped.

User dropped.

User dropped.

User dropped.

Role created.

Grant succeeded.

Grant succeeded.

Grant succeeded.

Grant succeeded.

User altered.

Connected.

Table created.

Package created.

Package body created.

Synonym dropped.

Synonym dropped.

Synonym created.

Synonym created.

Grant succeeded.

Grant succeeded.

Connected.

Package created.

Package body created.

Synonym dropped.

Synonym created.

Grant succeeded.

Connected.

PL/SQL procedure successfully completed.

BEGIN p01.main; END;

*
ERROR at line 1:

ORA-00942: table or view does not exist
ORA-06512: at "USER01.P01", line 7
ORA-06512: at line 1

BEGIN p01.main; END;

*
ERROR at line 1:

ORA-01003: no statement parsed
ORA-06512: at "USER01.P01", line 7
ORA-06512: at line 1

BEGIN p01.main; END;

*
ERROR at line 1:

ORA-01003: no statement parsed
ORA-06512: at "USER01.P01", line 7
ORA-06512: at line 1

no rows selected

Connected.

PL/SQL procedure successfully completed.

         C

         0

What do you think of it?
If user02 has direct grants - not trough the role - it works well. But why the first execution is ok?

Thank you.

Kamal Received on Thu Oct 21 2004 - 08:33:33 CDT