Re: create table grants and Dynamic SQL question

I'm not exactly sure what you're trying to do. Looks to me that you need a "work table" which you create, process, and drop all in one user session. If this is the case, do check out Oracle Temporary Tables. Not the same thing as PL/SQL tables you're referring to?

But if you insist on this dynamic table create approach, CREATE ANY TABLE system privilege will allow you to create a table in any schema.

Cheers,

Romeo

FlameDance <FlameDance_at_gmx.de> wrote in message news:<c8qfd3$nkl$07$1_at_news.t-online.com>...
> 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 Sun May 23 2004 - 23:01:30 CDT