Re: easy sql question

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