Re: easy sql question

From: Andy <andy.spaven_at_eps-hq.co.uk>
Date: Fri, 24 Jan 2003 09:38:50 -0000
Message-ID: <%L7Y9.3675$9R.12348607_at_newsr2.u-net.net>


[Quoted] 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 [Quoted] 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 [Quoted] 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 Fri Jan 24 2003 - 10:38:50 CET

Original text of this message