Re: create table grants and Dynamic SQL question

From: FlameDance <flamedance_at_gmx.de>
Date: Mon, 24 May 2004 11:28:23 +0200
Message-ID: <c8sf88$8ev$05$1@news.t-online.com>

Ok, I'll give some background. :)

Let's rename the users in my example. a is called "meta", b is called "data". Tables in "meta" hold data that (among other things) describe tables in "data" and describe how to manipulate the data in the tables in "data". "Meta" ist the driving user where most of the application resides. Everything in "data" is generated in "meta": Tables and some table specific packages of code. I want to keep the meta-data and the acutal data-data seperate, as everything in "data" is derived from "meta"-information and program logic (and some user input gained through a web interface, but that's another story).

Later another person than me will want to do something with these tables, so they need to persist. That later part is out of my control, I just create the database and provide the program to fill it, driven by meta-data that I find in CSV files.

It works well, nice and fast, in principle - but so far I used to write the generated code into text files (e.g. "create_this_table.sql" and call them via a batch that had to be called manually. I found no way to call that batch automatically, except to fumble around with Java. So I am trying to make it work without user interference using native dynamic SQL (which appears to have it's own caveats, more questions incoming soon).

Romeo Olympia wrote:

> 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 Mon May 24 2004 - 04:28:23 CDT