Re: Is this possible to do with SQL Plus?

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 12 Aug 1999 08:09:24 GMT
Message-ID: <7otvfk$9lo$2@news.seed.net.tw>

PMG <peteg_at_garlic.com> wrote in message news:37B0FA60.A91CD24C_at_garlic.com...
> I tried the following in a script to be executed in SQL Plus, but it
> doesn't work:
>
> select decode(user, 'pete', 'grant select on table1 to joe', null) from
> dual;
>
> Is it possible to execute a DDL-type statement based upon a who is the
> owner thru SQL Plus, or do I need to do this thru a procedure?

You can use a user variable to do this. If the user is SCOTT, the "grant" statement will be processed. If not, nothing happens.

column stmt new_value stmt;
select decode(user, 'SCOTT', 'select on emp to public', null) as stmt from dual; grant &&stmt;

SQL> column stmt new_value stmt;
SQL> select decode(user, 'SCOTT', 'select on emp to public', null) as stmt from dual;

SQL> grant &&stmt;
old 1: grant &&stmt
new 1: grant select on emp to public

Grant succeeded. Received on Thu Aug 12 1999 - 03:09:24 CDT