create table grants and Dynamic SQL question

Hi,

given 2 users a and b, I would like to do the following:

connect a/password_at_instance
create table b.test (x number);
drop table b.test;

Is this possible in Oracle 9.2, and if yes, which grants will I have to set?

Is it furthermore possible to put it into dynamic SQL?

DECLARE
Cmd VARCHAR2(100);
BEGIN
    Cmd := 'CREATE TABLE data.test (x number)';     EXECUTE IMMEDIATE Cmd;

• do something with test Cmd := 'DROP TABLE data.test'; EXECUTE IMMEDIATE Cmd; END; /

I'm NOT looking for a temporary PL/SQL table, I need a persistent regular table, just under another user. Another idea would be to let the dynamic Cmd run as user b - but how could I invoke that from a PL/SQL package running as user a?

Stephan Received on Sat May 22 2004 - 16:31:46 CDT