Re: easy sql question
Date: Thu, 23 Jan 2003 14:52:36 +0000
Message-ID: <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.
[Quoted] Doing a SET DEFINE OFF, tells SQL*Plus to treat &NAME as the string '&NAME'.
[Quoted] 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).
[Quoted] 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[_at_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 _at_${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. -- BillyReceived on Thu Jan 23 2003 - 15:52:36 CET