Dynamic sql problem on 9iR2

Hi all.

I have a problem in executing native dynamic sql with the truncate table ddl clause.
I posted a similar problem before but now the situation changed, I think nobody replied to the previous message because it was about 8.1.7.
But now I tried the same script on 9iR2 and the problem is the same!

Just try this script on a 9iR2 instance (to launch it multiple times uncomment the drop commands at the beginning), beware if you already have users named "user00" etc... and public synonyms named "t", "p00" etc...:

spool truncate_test.log

conn system/xxx_at_conn_str

--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_str

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_str

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_str

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

select * from t;

conn user02/a_at_conn_str

exec p01.main

select * from t;

spool off

exit

My log is this (truncate_test.log):

[snip] (The ddl commands run smoothly)

But when I come at the last two connects:

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

I think this is a bug or something like that, what is your opinion?

Thank you

Kamal Received on Wed Oct 27 2004 - 09:14:14 CDT