Regarding sql script

Question

I have the line below in a .sql script. Not sure what it does. Does it wait for input from user for dbuserid and password or does it take the values from environment? What does && denote? In test.sql file I saee the line below before creating tables:

CONNECT &&usr/&&pass@&&dbname

Answer 1

The '&' is used to refer to substitution variables in SQL*Plus. These variables can be set using the DEFINE keyword. Alternatively, if not specified using this keyword, SQL*Plus will prompt you for the value of the variable and continue prompting you every time it sees &variable

The double '&' tells SQL*Plus to reuse the first defined value of the variable. For example, if you were prompted for the value of usr based on your connect code snippet, all subsequent occurrences of &&usr would be replaced with this value. In this case SQL*Plus will prompt you only once to enter in the value of usr.

EDIT:
Yes you can pass in parameters from a shell script. Example:

$ cat a.sh
#!/bin/bash

# Ideally, you would retrieve the password from a secure
# location like a password safe/vault etc. Never hardcode
# the connection credentials
USER=scott
PASS=tiger
INST=orcl

sqlplus -s /nolog << EOF >> some_log_file.log
connect ${USER}/${PASS}@${INST}
set serveroutput on
select user from dual;
EOF

$ ./a.sh

$ cat some_log_file.log

USER
------------------------------
SCOTT

$ 

Answer 2

In this sql script you are declaring usr, pass and dbname as subsitution variables. From OTN:

Both single ampersand (&) and double ampersand (&&) can prefix a substitution variable name in a statement. SQL*Plus pre-processes the statement and substitutes the variable's value. The statement is then executed. If the variable was not previously defined then SQL*Plus prompts you for a value before doing the substitution.

If a single ampersand prefix is used with an undefined variable, the value you enter at the prompt is not stored. Immediately after the value is substituted in the statement the value is discarded and the variable remains undefined. If the variable is referenced twice, even in the same command, then you are prompted twice. Different values can be entered at each prompt.

If a double ampersand reference causes SQL*Plus to prompt you for a value, then SQL*Plus defines the variable as that value. Any subsequent reference to the variable (even in the same command) using either "&" or "&&" substitutes the newly defined value. SQL*Plus will not prompt you again.

And here is more about SQL*Plus Substitution Variables.