Re: create table grants and Dynamic SQL question

From: FlameDance <flamedance_at_gmx.de>
Date: Mon, 24 May 2004 11:07:53 +0200
Message-ID: <c8se1q$fre$01$1@news.t-online.com>

Jim Kennedy wrote:

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

>
> Don't do this. You probably don't need to do this in the first place.

Well ... no, I don't need it absolutely - but it would represent the design of my application, to seperate things that should be seperated.

 > This is not MS SQL Server or Sybase.

I haven't worked with MS SQL nor with Sybase, so I have no idea what you're trying to tell me.

> But if you really, reallly think you need
> to do something like this then use global temporary tables (see
> documentation or asktom.oracle.com and do a search).  Then you don't have to
> do the whole create table dance.

Nope, as I wrote I need persistent tables that last way longer than just one session. The drop table statement above was just shown for completeness.

Stephan Received on Mon May 24 2004 - 04:07:53 CDT