Re: easy sql question

Hi,

okay, thanks for that I will give it a try !

Iain

"Billy Verreynne" <vslabs_at_onwe.co.za> wrote in message news:b0ooip$msd$1_at_ctb-nnrp2.saix.net...
> IKerr. wrote:
>
> > 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.
>
> I think we're both a bit confused. :-)
>
> SQL*Plus treats the & symbol as a variable specifier. So if you type &NAME
> in SQL*Plus, it will treat &NAME as a variable and will prompt you to
enter
> a value for it.
>
> Doing a SET DEFINE OFF, tells SQL*Plus to treat &NAME as the string
'&NAME'.
>
> However, I think what you are after is how to supress the *resulting
> display* (e.g. STDOUT) of the prompt (which appears in the spool file).
>
> My posting was on how to suppress the prompt *itself* (i.e. prevent
variable
> substitution).
>
> I can not offhand recall an option that supresses the stdout of a SQL*Plus
> prompt from appearing in the spool file. I usually prefer not to use
> SQL*Plus prompts/variables myself. Try something like this:
> --
> #!/bin/sh
>
> # usage : grant-access <to-user> <rights>
>
> # good idea to check the $* or $1 and $2 params first
> # e.g. if $1 = '-h' then display usage, if $2 not given
> # then display error message
>
> # spool file name
> SQLFILE=/tmp/~$$.sql
>
> # we get the connect string
> echo "Oracle username/password[@tns] : "
> read CONNECT
>
> # launch SQL-Plus
> sqlplus -s << EOF
> $CONNECT
>
> set heading off
> set echo off
> set termout off
> set feedback off
> set page 0
>
> spool $SQLFILE
>
> SELECT
> 'grant $2 on ' || table_name || ' to $1;'
> FROM user_tables;
>
> spool off
>
> @${SPOOLFILE}
>
> exit;
>
> EOF
>
> # delete the spool file
> rm $SQLFILE
> ---
>
> The above of course ignores stored procs and so on. You could modify the
> above to make it work execute privs also.
>
>
>
> --
> Billy
>
Received on Thu Jan 23 2003 - 07:17:56 CST