Re: Dynamic sql problem on 9iR2

kamal80_at_virgilio.it (Kamal) wrote in message news:<4e766a02.0410270614.4156295b_at_posting.google.com>...
> 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

I ran your script on 9.2.0.5; here is my truncate_test.log:

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.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed

C

        0

Connected.

PL/SQL procedure successfully completed.

         C

No bugs here.

David Fitzjarrell Received on Wed Oct 27 2004 - 17:20:37 CDT