okay, thanks Andy
"Andy" <andy.spaven_at_eps-hq.co.uk> wrote in message
news:%L7Y9.3675$9R.12348607_at_newsr2.u-net.net...
> You could also use accept to get the values prior to turning spool on.
>
> ACCEPT permissions CHAR PROMPT 'Permissions : '
> ACCEPT username CHAR PROMPT 'User : '
>
> SET VERIFY OFF
> SET FEEDBACK OFF
> SET HEADING OFF
> SET PAGESIZE 0
> SET TRIMSPOOL ON
> SET TERMOUT OFF
>
> SPOOL intermediate.sql
>
> select 'grant ' || '&permissions' || ' ON ' || table_name || ' TO ' ||
> '&username' || ';' from user_tables
> /
>
> SPOOL OFF
>
> START intermediate.sql
>
>
> This will do the business in SQL*Plus.
> The verify off removes the before and after replacement of &vars lines
> The feedback off removes the 256 rows selected.. line.
> The heading off and pagesize 0 remove the heading and and pagebreaks
> (pagesize 0 does this all in one but it just seems tidier to explicitly do
> both)
> The trimspool on prevents you getting a large file (otherwise each
line
> in the spool file is "linesize" characters wide space padded).
> The termout off prevents unnecessary display to screen whilst spooling
>
> You might want to switch some of these options back before running the
START
> command if you want to see the results of the grants.
>
>
> Andy
>
>
> "IKerr." <i.b.kerr_at_leeds.ac.uk> wrote in message
> news:FpQX9.104$1o.100_at_newsfep3-gui.server.ntli.net...
> > Hi Billy,
> >
> > I tried your suggestion, and I get
> >
> > grant &permissions ON table123 TO &user;
> > and further down the line
> > SP2-0552: Bind variable "USER" not declared.
> >
> > here is what I am trying (in an sql file):
> >
> > SPOOL intermediate.sql
> >
> > Select 'grant ' || '&permissions' || ' ON ' || table_name || ' TO ' ||
> > '&user' || ';'
> > From user_tables;
> >
> > /* set echo on*/
> >
> > SPOOL OFF
> >
> > start intermediate.sql
> >
> > thanks
> >
> > Iain
> >
> > "Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message
> > news:b0ohe0$joa$1_at_ctb-nnrp2.saix.net...
> > > IKerr. wrote:
> > >
> > > <snipped>
> > > > "SP2-0734: unknown command beginning "Enter valu..." - rest of line
> > > > ignored."
> > > >
> > > > How can I make the lines disappear from the spooled script before it
i
> s
> > > > run.
> > >
> > > SET define off
> > >
> > >
> > > This will disable all SQL*Plus prompts caused by the & char, e.g.
> > >
> > > SQL> Prompt &Hello there
> > > Enter value for &Hello:
> > >
> > > vs.
> > >
> > > SQL> set define off;
> > > SQL> Prompt &Hello there
> > > &Hello there
> > >
> > >
> > > --
> > > Billy
> >
> >
>
>
Received on Sun Jan 26 2003 - 06:39:58 CST
